I got a completely independent log table, which I mean
there is no relationship with other tables. its columns
look like
nvarchar(16), nvarchar(16), nvarchar(16), int, tinyint,
tinyint, varchar(3),smalldatetime.
it works fine before the amount of its records reaches 10
millions around. afterwards, the insertion of one record
in ASP may return an ODBC Timeout Error. when this
happens, the only way to insert a record which I can
imagine is to run the same stored procedure in Query
Analyzer, Guess how long it took? 70 seconds!
the strangest thing is, after the 70 seconds insertion,
the next insertion only takes 1 second or less, at that
time, ASP page works again.
My SQL server is SQL2000+sp3
I noticed the same problem was reported in this newsgroup
two years ago, somebody suggested using aba_lockinfo to
figure it out, I will try it when the problem happens
again. In the meantime, I wonder if anybody got a better
solution, or knew what was the main reason of this problem.
Thanks,
Mingfa MaSounds like you may have page splitting going on and a really slow disk
subsystem. What is the actual DDL including indexes for the table and a
sample of the inserted row? You could also have blocking issues which can
be found with sp_who2.
--
Andrew J. Kelly
SQL Server MVP
"Mingfa Ma" <mingfa@.commonname.com> wrote in message
news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
> I got a completely independent log table, which I mean
> there is no relationship with other tables. its columns
> look like
> nvarchar(16), nvarchar(16), nvarchar(16), int, tinyint,
> tinyint, varchar(3),smalldatetime.
> it works fine before the amount of its records reaches 10
> millions around. afterwards, the insertion of one record
> in ASP may return an ODBC Timeout Error. when this
> happens, the only way to insert a record which I can
> imagine is to run the same stored procedure in Query
> Analyzer, Guess how long it took? 70 seconds!
> the strangest thing is, after the 70 seconds insertion,
> the next insertion only takes 1 second or less, at that
> time, ASP page works again.
> My SQL server is SQL2000+sp3
> I noticed the same problem was reported in this newsgroup
> two years ago, somebody suggested using aba_lockinfo to
> figure it out, I will try it when the problem happens
> again. In the meantime, I wonder if anybody got a better
> solution, or knew what was the main reason of this problem.
> Thanks,
> Mingfa Ma
>|||Thanks, Andrew,
but what do you mean "the actual DDL"? how to get it?
here is the sample of the inserted row,
626995265,1052399332,Dering Harbor,0,20,100,US,2003-07-15
02:23:00
Mingfa Ma
>--Original Message--
>Sounds like you may have page splitting going on and a
really slow disk
>subsystem. What is the actual DDL including indexes for
the table and a
>sample of the inserted row? You could also have
blocking issues which can
>be found with sp_who2.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
>> I got a completely independent log table, which I mean
>> there is no relationship with other tables. its columns
>> look like
>> nvarchar(16), nvarchar(16), nvarchar(16), int, tinyint,
>> tinyint, varchar(3),smalldatetime.
>> it works fine before the amount of its records reaches
10
>> millions around. afterwards, the insertion of one record
>> in ASP may return an ODBC Timeout Error. when this
>> happens, the only way to insert a record which I can
>> imagine is to run the same stored procedure in Query
>> Analyzer, Guess how long it took? 70 seconds!
>> the strangest thing is, after the 70 seconds insertion,
>> the next insertion only takes 1 second or less, at that
>> time, ASP page works again.
>> My SQL server is SQL2000+sp3
>> I noticed the same problem was reported in this
newsgroup
>> two years ago, somebody suggested using aba_lockinfo to
>> figure it out, I will try it when the problem happens
>> again. In the meantime, I wonder if anybody got a better
>> solution, or knew what was the main reason of this
problem.
>> Thanks,
>> Mingfa Ma
>>
>
>.
>|||That is the definition of the table, indexes, etc. You can get this by
right clicking on the table in EM and choosing "All Tasks - Generate Sql
Script" and make sure you include the indexes, triggers etc.
--
Andrew J. Kelly
SQL Server MVP
"Mingfa Ma" <mingfa@.commonname.com> wrote in message
news:04ff01c34b9e$9ea475c0$a001280a@.phx.gbl...
> Thanks, Andrew,
> but what do you mean "the actual DDL"? how to get it?
> here is the sample of the inserted row,
> 626995265,1052399332,Dering Harbor,0,20,100,US,2003-07-15
> 02:23:00
>
> Mingfa Ma
>
> >--Original Message--
> >Sounds like you may have page splitting going on and a
> really slow disk
> >subsystem. What is the actual DDL including indexes for
> the table and a
> >sample of the inserted row? You could also have
> blocking issues which can
> >be found with sp_who2.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
> >> I got a completely independent log table, which I mean
> >> there is no relationship with other tables. its columns
> >> look like
> >>
> >> nvarchar(16), nvarchar(16), nvarchar(16), int, tinyint,
> >> tinyint, varchar(3),smalldatetime.
> >>
> >> it works fine before the amount of its records reaches
> 10
> >> millions around. afterwards, the insertion of one record
> >> in ASP may return an ODBC Timeout Error. when this
> >> happens, the only way to insert a record which I can
> >> imagine is to run the same stored procedure in Query
> >> Analyzer, Guess how long it took? 70 seconds!
> >>
> >> the strangest thing is, after the 70 seconds insertion,
> >> the next insertion only takes 1 second or less, at that
> >> time, ASP page works again.
> >>
> >> My SQL server is SQL2000+sp3
> >>
> >> I noticed the same problem was reported in this
> newsgroup
> >> two years ago, somebody suggested using aba_lockinfo to
> >> figure it out, I will try it when the problem happens
> >> again. In the meantime, I wonder if anybody got a better
> >> solution, or knew what was the main reason of this
> problem.
> >>
> >> Thanks,
> >>
> >> Mingfa Ma
> >>
> >>
> >
> >
> >.
> >|||Sorry, I mistook DDL as DLL.
the table is very simple, no index, no trigger.
Here is the definition of the table
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[n2_tblLogUserQuery]') and OBJECTPROPERTY
(id, N'IsUserTable') = 1)
drop table [dbo].[n2_tblLogUserQuery]
GO
CREATE TABLE [dbo].[n2_tblLogUserQuery] (
[UID] [nvarchar] (16) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[SID] [nvarchar] (16) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Query] [nvarchar] (128) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CommonNameID] [int] NULL ,
[Type] [tinyint] NULL ,
[App] [tinyint] NULL ,
[CountryCode] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LogDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
>--Original Message--
>That is the definition of the table, indexes, etc. You
can get this by
>right clicking on the table in EM and choosing "All
Tasks - Generate Sql
>Script" and make sure you include the indexes, triggers
etc.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>news:04ff01c34b9e$9ea475c0$a001280a@.phx.gbl...
>> Thanks, Andrew,
>> but what do you mean "the actual DDL"? how to get it?
>> here is the sample of the inserted row,
>> 626995265,1052399332,Dering Harbor,0,20,100,US,2003-07-
15
>> 02:23:00
>>
>> Mingfa Ma
>>
>> >--Original Message--
>> >Sounds like you may have page splitting going on and a
>> really slow disk
>> >subsystem. What is the actual DDL including indexes
for
>> the table and a
>> >sample of the inserted row? You could also have
>> blocking issues which can
>> >be found with sp_who2.
>> >
>> >--
>> >
>> >Andrew J. Kelly
>> >SQL Server MVP
>> >
>> >
>> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>> >news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
>> >> I got a completely independent log table, which I
mean
>> >> there is no relationship with other tables. its
columns
>> >> look like
>> >>
>> >> nvarchar(16), nvarchar(16), nvarchar(16), int,
tinyint,
>> >> tinyint, varchar(3),smalldatetime.
>> >>
>> >> it works fine before the amount of its records
reaches
>> 10
>> >> millions around. afterwards, the insertion of one
record
>> >> in ASP may return an ODBC Timeout Error. when this
>> >> happens, the only way to insert a record which I can
>> >> imagine is to run the same stored procedure in Query
>> >> Analyzer, Guess how long it took? 70 seconds!
>> >>
>> >> the strangest thing is, after the 70 seconds
insertion,
>> >> the next insertion only takes 1 second or less, at
that
>> >> time, ASP page works again.
>> >>
>> >> My SQL server is SQL2000+sp3
>> >>
>> >> I noticed the same problem was reported in this
>> newsgroup
>> >> two years ago, somebody suggested using aba_lockinfo
to
>> >> figure it out, I will try it when the problem happens
>> >> again. In the meantime, I wonder if anybody got a
better
>> >> solution, or knew what was the main reason of this
>> problem.
>> >>
>> >> Thanks,
>> >>
>> >> Mingfa Ma
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||You have no indexes at all? Is there anyone running selects on this table?
If so they will most likely take out a table lock and block the insert until
done. Just curious if the UID and SID are just numbers why ar eyou using a
NVARCHAR vs a regular VARCHAR or even an INT? You might consider placing a
clustered index on the table but you need to decide how you access it to
determine the proper column to use.
--
Andrew J. Kelly
SQL Server MVP
"Mingfa Ma" <mingfa@.commonname.com> wrote in message
news:0c0401c34bb0$61ec76c0$a501280a@.phx.gbl...
> Sorry, I mistook DDL as DLL.
> the table is very simple, no index, no trigger.
> Here is the definition of the table
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[n2_tblLogUserQuery]') and OBJECTPROPERTY
> (id, N'IsUserTable') = 1)
> drop table [dbo].[n2_tblLogUserQuery]
> GO
> CREATE TABLE [dbo].[n2_tblLogUserQuery] (
> [UID] [nvarchar] (16) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [SID] [nvarchar] (16) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Query] [nvarchar] (128) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [CommonNameID] [int] NULL ,
> [Type] [tinyint] NULL ,
> [App] [tinyint] NULL ,
> [CountryCode] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [LogDate] [smalldatetime] NULL
> ) ON [PRIMARY]
> GO
>
>
> >--Original Message--
> >That is the definition of the table, indexes, etc. You
> can get this by
> >right clicking on the table in EM and choosing "All
> Tasks - Generate Sql
> >Script" and make sure you include the indexes, triggers
> etc.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >news:04ff01c34b9e$9ea475c0$a001280a@.phx.gbl...
> >> Thanks, Andrew,
> >>
> >> but what do you mean "the actual DDL"? how to get it?
> >>
> >> here is the sample of the inserted row,
> >>
> >> 626995265,1052399332,Dering Harbor,0,20,100,US,2003-07-
> 15
> >> 02:23:00
> >>
> >>
> >> Mingfa Ma
> >>
> >>
> >> >--Original Message--
> >> >Sounds like you may have page splitting going on and a
> >> really slow disk
> >> >subsystem. What is the actual DDL including indexes
> for
> >> the table and a
> >> >sample of the inserted row? You could also have
> >> blocking issues which can
> >> >be found with sp_who2.
> >> >
> >> >--
> >> >
> >> >Andrew J. Kelly
> >> >SQL Server MVP
> >> >
> >> >
> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >> >news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
> >> >> I got a completely independent log table, which I
> mean
> >> >> there is no relationship with other tables. its
> columns
> >> >> look like
> >> >>
> >> >> nvarchar(16), nvarchar(16), nvarchar(16), int,
> tinyint,
> >> >> tinyint, varchar(3),smalldatetime.
> >> >>
> >> >> it works fine before the amount of its records
> reaches
> >> 10
> >> >> millions around. afterwards, the insertion of one
> record
> >> >> in ASP may return an ODBC Timeout Error. when this
> >> >> happens, the only way to insert a record which I can
> >> >> imagine is to run the same stored procedure in Query
> >> >> Analyzer, Guess how long it took? 70 seconds!
> >> >>
> >> >> the strangest thing is, after the 70 seconds
> insertion,
> >> >> the next insertion only takes 1 second or less, at
> that
> >> >> time, ASP page works again.
> >> >>
> >> >> My SQL server is SQL2000+sp3
> >> >>
> >> >> I noticed the same problem was reported in this
> >> newsgroup
> >> >> two years ago, somebody suggested using aba_lockinfo
> to
> >> >> figure it out, I will try it when the problem happens
> >> >> again. In the meantime, I wonder if anybody got a
> better
> >> >> solution, or knew what was the main reason of this
> >> problem.
> >> >>
> >> >> Thanks,
> >> >>
> >> >> Mingfa Ma
> >> >>
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Correct, there is no indexes on the table as there is
nobody running selects on this table.
UID and SID are not numbers, but they can be changed to
varchar if that would make any difference.
It is whorthwhile pointing out that this table is not
being accessed frequently. 99.99% of time it is only used
to log incoming data and there are about 15 to 30 incoming
data to be recorded within a single second. I am pretty
sure nobody is runnng 'select' when the problem happens.
If creating an index on this table, would it not further
slow down this table? Also, this table can grow like 1
million records in a day, the index of the table would
also be very huge?
Mingfa Ma
>--Original Message--
>You have no indexes at all? Is there anyone running
selects on this table?
>If so they will most likely take out a table lock and
block the insert until
>done. Just curious if the UID and SID are just numbers
why ar eyou using a
>NVARCHAR vs a regular VARCHAR or even an INT? You might
consider placing a
>clustered index on the table but you need to decide how
you access it to
>determine the proper column to use.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>news:0c0401c34bb0$61ec76c0$a501280a@.phx.gbl...
>> Sorry, I mistook DDL as DLL.
>> the table is very simple, no index, no trigger.
>> Here is the definition of the table
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[n2_tblLogUserQuery]') and
OBJECTPROPERTY
>> (id, N'IsUserTable') = 1)
>> drop table [dbo].[n2_tblLogUserQuery]
>> GO
>> CREATE TABLE [dbo].[n2_tblLogUserQuery] (
>> [UID] [nvarchar] (16) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [SID] [nvarchar] (16) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [Query] [nvarchar] (128) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [CommonNameID] [int] NULL ,
>> [Type] [tinyint] NULL ,
>> [App] [tinyint] NULL ,
>> [CountryCode] [varchar] (3) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [LogDate] [smalldatetime] NULL
>> ) ON [PRIMARY]
>> GO
>>
>>
>> >--Original Message--
>> >That is the definition of the table, indexes, etc. You
>> can get this by
>> >right clicking on the table in EM and choosing "All
>> Tasks - Generate Sql
>> >Script" and make sure you include the indexes, triggers
>> etc.
>> >
>> >--
>> >
>> >Andrew J. Kelly
>> >SQL Server MVP
>> >
>> >
>> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>> >news:04ff01c34b9e$9ea475c0$a001280a@.phx.gbl...
>> >> Thanks, Andrew,
>> >>
>> >> but what do you mean "the actual DDL"? how to get it?
>> >>
>> >> here is the sample of the inserted row,
>> >>
>> >> 626995265,1052399332,Dering Harbor,0,20,100,US,2003-
07-
>> 15
>> >> 02:23:00
>> >>
>> >>
>> >> Mingfa Ma
>> >>
>> >>
>> >> >--Original Message--
>> >> >Sounds like you may have page splitting going on
and a
>> >> really slow disk
>> >> >subsystem. What is the actual DDL including indexes
>> for
>> >> the table and a
>> >> >sample of the inserted row? You could also have
>> >> blocking issues which can
>> >> >be found with sp_who2.
>> >> >
>> >> >--
>> >> >
>> >> >Andrew J. Kelly
>> >> >SQL Server MVP
>> >> >
>> >> >
>> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>> >> >news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
>> >> >> I got a completely independent log table, which I
>> mean
>> >> >> there is no relationship with other tables. its
>> columns
>> >> >> look like
>> >> >>
>> >> >> nvarchar(16), nvarchar(16), nvarchar(16), int,
>> tinyint,
>> >> >> tinyint, varchar(3),smalldatetime.
>> >> >>
>> >> >> it works fine before the amount of its records
>> reaches
>> >> 10
>> >> >> millions around. afterwards, the insertion of one
>> record
>> >> >> in ASP may return an ODBC Timeout Error. when this
>> >> >> happens, the only way to insert a record which I
can
>> >> >> imagine is to run the same stored procedure in
Query
>> >> >> Analyzer, Guess how long it took? 70 seconds!
>> >> >>
>> >> >> the strangest thing is, after the 70 seconds
>> insertion,
>> >> >> the next insertion only takes 1 second or less, at
>> that
>> >> >> time, ASP page works again.
>> >> >>
>> >> >> My SQL server is SQL2000+sp3
>> >> >>
>> >> >> I noticed the same problem was reported in this
>> >> newsgroup
>> >> >> two years ago, somebody suggested using
aba_lockinfo
>> to
>> >> >> figure it out, I will try it when the problem
happens
>> >> >> again. In the meantime, I wonder if anybody got a
>> better
>> >> >> solution, or knew what was the main reason of this
>> >> problem.
>> >> >>
>> >> >> Thanks,
>> >> >>
>> >> >> Mingfa Ma
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||The main reason I mentioned changing the datatype was to reduce space and
keep the index smaller if you had one on those columns. If this does not
get queried then I suppose you don't need an index for that purpose but
sometimes a clustered index can help with insertions. In a heap sql server
needs to lookup a fair amount of information to determine where to place the
next row and with a large number of rows this may take a while, although
usually not that long<g>. With a clustered index there is no choice but to
place it where it belongs according to the index expression. If you use a
column such a an Identity or a datetime that is always in chronological
order for your index expression the new rows are always just added to the
end. It's pretty clean and efficient. First I would run profiler and
perfmon to see what is happening in the system at the time of the slowdown.
Make sure there are no high disk or cpu queues etc. Run sp_who2 to ensure
that nothing is blocking the inserts. And I would try the clustered index
on the LogDate column and see how that works out.
--
Andrew J. Kelly
SQL Server MVP
"Mingfa Ma" <mingfa@.commonname.com> wrote in message
news:091f01c34c62$86066d60$a501280a@.phx.gbl...
> Correct, there is no indexes on the table as there is
> nobody running selects on this table.
> UID and SID are not numbers, but they can be changed to
> varchar if that would make any difference.
> It is whorthwhile pointing out that this table is not
> being accessed frequently. 99.99% of time it is only used
> to log incoming data and there are about 15 to 30 incoming
> data to be recorded within a single second. I am pretty
> sure nobody is runnng 'select' when the problem happens.
> If creating an index on this table, would it not further
> slow down this table? Also, this table can grow like 1
> million records in a day, the index of the table would
> also be very huge?
> Mingfa Ma
>
> >--Original Message--
> >You have no indexes at all? Is there anyone running
> selects on this table?
> >If so they will most likely take out a table lock and
> block the insert until
> >done. Just curious if the UID and SID are just numbers
> why ar eyou using a
> >NVARCHAR vs a regular VARCHAR or even an INT? You might
> consider placing a
> >clustered index on the table but you need to decide how
> you access it to
> >determine the proper column to use.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >news:0c0401c34bb0$61ec76c0$a501280a@.phx.gbl...
> >> Sorry, I mistook DDL as DLL.
> >>
> >> the table is very simple, no index, no trigger.
> >>
> >> Here is the definition of the table
> >>
> >> if exists (select * from dbo.sysobjects where id => >> object_id(N'[dbo].[n2_tblLogUserQuery]') and
> OBJECTPROPERTY
> >> (id, N'IsUserTable') = 1)
> >> drop table [dbo].[n2_tblLogUserQuery]
> >> GO
> >>
> >> CREATE TABLE [dbo].[n2_tblLogUserQuery] (
> >> [UID] [nvarchar] (16) COLLATE
> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> [SID] [nvarchar] (16) COLLATE
> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> [Query] [nvarchar] (128) COLLATE
> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> [CommonNameID] [int] NULL ,
> >> [Type] [tinyint] NULL ,
> >> [App] [tinyint] NULL ,
> >> [CountryCode] [varchar] (3) COLLATE
> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> [LogDate] [smalldatetime] NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >That is the definition of the table, indexes, etc. You
> >> can get this by
> >> >right clicking on the table in EM and choosing "All
> >> Tasks - Generate Sql
> >> >Script" and make sure you include the indexes, triggers
> >> etc.
> >> >
> >> >--
> >> >
> >> >Andrew J. Kelly
> >> >SQL Server MVP
> >> >
> >> >
> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >> >news:04ff01c34b9e$9ea475c0$a001280a@.phx.gbl...
> >> >> Thanks, Andrew,
> >> >>
> >> >> but what do you mean "the actual DDL"? how to get it?
> >> >>
> >> >> here is the sample of the inserted row,
> >> >>
> >> >> 626995265,1052399332,Dering Harbor,0,20,100,US,2003-
> 07-
> >> 15
> >> >> 02:23:00
> >> >>
> >> >>
> >> >> Mingfa Ma
> >> >>
> >> >>
> >> >> >--Original Message--
> >> >> >Sounds like you may have page splitting going on
> and a
> >> >> really slow disk
> >> >> >subsystem. What is the actual DDL including indexes
> >> for
> >> >> the table and a
> >> >> >sample of the inserted row? You could also have
> >> >> blocking issues which can
> >> >> >be found with sp_who2.
> >> >> >
> >> >> >--
> >> >> >
> >> >> >Andrew J. Kelly
> >> >> >SQL Server MVP
> >> >> >
> >> >> >
> >> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >> >> >news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
> >> >> >> I got a completely independent log table, which I
> >> mean
> >> >> >> there is no relationship with other tables. its
> >> columns
> >> >> >> look like
> >> >> >>
> >> >> >> nvarchar(16), nvarchar(16), nvarchar(16), int,
> >> tinyint,
> >> >> >> tinyint, varchar(3),smalldatetime.
> >> >> >>
> >> >> >> it works fine before the amount of its records
> >> reaches
> >> >> 10
> >> >> >> millions around. afterwards, the insertion of one
> >> record
> >> >> >> in ASP may return an ODBC Timeout Error. when this
> >> >> >> happens, the only way to insert a record which I
> can
> >> >> >> imagine is to run the same stored procedure in
> Query
> >> >> >> Analyzer, Guess how long it took? 70 seconds!
> >> >> >>
> >> >> >> the strangest thing is, after the 70 seconds
> >> insertion,
> >> >> >> the next insertion only takes 1 second or less, at
> >> that
> >> >> >> time, ASP page works again.
> >> >> >>
> >> >> >> My SQL server is SQL2000+sp3
> >> >> >>
> >> >> >> I noticed the same problem was reported in this
> >> >> newsgroup
> >> >> >> two years ago, somebody suggested using
> aba_lockinfo
> >> to
> >> >> >> figure it out, I will try it when the problem
> happens
> >> >> >> again. In the meantime, I wonder if anybody got a
> >> better
> >> >> >> solution, or knew what was the main reason of this
> >> >> problem.
> >> >> >>
> >> >> >> Thanks,
> >> >> >>
> >> >> >> Mingfa Ma
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Thanks very much indeed.
I will try your suggestions when the problem happens again.
and I'll inform you the result. Is your email
sqlmvpnooospam@.shadhawk.com reachable? just in case this
topic would be too old in the group at that time, and you
would not notice it.
Kind regards,
Mingfa Ma
>--Original Message--
>The main reason I mentioned changing the datatype was to
reduce space and
>keep the index smaller if you had one on those columns.
If this does not
>get queried then I suppose you don't need an index for
that purpose but
>sometimes a clustered index can help with insertions. In
a heap sql server
>needs to lookup a fair amount of information to determine
where to place the
>next row and with a large number of rows this may take a
while, although
>usually not that long<g>. With a clustered index there
is no choice but to
>place it where it belongs according to the index
expression. If you use a
>column such a an Identity or a datetime that is always in
chronological
>order for your index expression the new rows are always
just added to the
>end. It's pretty clean and efficient. First I would
run profiler and
>perfmon to see what is happening in the system at the
time of the slowdown.
>Make sure there are no high disk or cpu queues etc. Run
sp_who2 to ensure
>that nothing is blocking the inserts. And I would try
the clustered index
>on the LogDate column and see how that works out.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>news:091f01c34c62$86066d60$a501280a@.phx.gbl...
>> Correct, there is no indexes on the table as there is
>> nobody running selects on this table.
>> UID and SID are not numbers, but they can be changed to
>> varchar if that would make any difference.
>> It is whorthwhile pointing out that this table is not
>> being accessed frequently. 99.99% of time it is only
used
>> to log incoming data and there are about 15 to 30
incoming
>> data to be recorded within a single second. I am pretty
>> sure nobody is runnng 'select' when the problem happens.
>> If creating an index on this table, would it not further
>> slow down this table? Also, this table can grow like 1
>> million records in a day, the index of the table would
>> also be very huge?
>> Mingfa Ma
>>
>> >--Original Message--
>> >You have no indexes at all? Is there anyone running
>> selects on this table?
>> >If so they will most likely take out a table lock and
>> block the insert until
>> >done. Just curious if the UID and SID are just numbers
>> why ar eyou using a
>> >NVARCHAR vs a regular VARCHAR or even an INT? You
might
>> consider placing a
>> >clustered index on the table but you need to decide how
>> you access it to
>> >determine the proper column to use.
>> >
>> >--
>> >
>> >Andrew J. Kelly
>> >SQL Server MVP
>> >
>> >
>> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>> >news:0c0401c34bb0$61ec76c0$a501280a@.phx.gbl...
>> >> Sorry, I mistook DDL as DLL.
>> >>
>> >> the table is very simple, no index, no trigger.
>> >>
>> >> Here is the definition of the table
>> >>
>> >> if exists (select * from dbo.sysobjects where id =>> >> object_id(N'[dbo].[n2_tblLogUserQuery]') and
>> OBJECTPROPERTY
>> >> (id, N'IsUserTable') = 1)
>> >> drop table [dbo].[n2_tblLogUserQuery]
>> >> GO
>> >>
>> >> CREATE TABLE [dbo].[n2_tblLogUserQuery] (
>> >> [UID] [nvarchar] (16) COLLATE
>> >> SQL_Latin1_General_CP1_CI_AS NULL ,
>> >> [SID] [nvarchar] (16) COLLATE
>> >> SQL_Latin1_General_CP1_CI_AS NULL ,
>> >> [Query] [nvarchar] (128) COLLATE
>> >> SQL_Latin1_General_CP1_CI_AS NULL ,
>> >> [CommonNameID] [int] NULL ,
>> >> [Type] [tinyint] NULL ,
>> >> [App] [tinyint] NULL ,
>> >> [CountryCode] [varchar] (3) COLLATE
>> >> SQL_Latin1_General_CP1_CI_AS NULL ,
>> >> [LogDate] [smalldatetime] NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >>
>> >>
>> >>
>> >> >--Original Message--
>> >> >That is the definition of the table, indexes, etc.
You
>> >> can get this by
>> >> >right clicking on the table in EM and choosing "All
>> >> Tasks - Generate Sql
>> >> >Script" and make sure you include the indexes,
triggers
>> >> etc.
>> >> >
>> >> >--
>> >> >
>> >> >Andrew J. Kelly
>> >> >SQL Server MVP
>> >> >
>> >> >
>> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>> >> >news:04ff01c34b9e$9ea475c0$a001280a@.phx.gbl...
>> >> >> Thanks, Andrew,
>> >> >>
>> >> >> but what do you mean "the actual DDL"? how to get
it?
>> >> >>
>> >> >> here is the sample of the inserted row,
>> >> >>
>> >> >> 626995265,1052399332,Dering
Harbor,0,20,100,US,2003-
>> 07-
>> >> 15
>> >> >> 02:23:00
>> >> >>
>> >> >>
>> >> >> Mingfa Ma
>> >> >>
>> >> >>
>> >> >> >--Original Message--
>> >> >> >Sounds like you may have page splitting going on
>> and a
>> >> >> really slow disk
>> >> >> >subsystem. What is the actual DDL including
indexes
>> >> for
>> >> >> the table and a
>> >> >> >sample of the inserted row? You could also have
>> >> >> blocking issues which can
>> >> >> >be found with sp_who2.
>> >> >> >
>> >> >> >--
>> >> >> >
>> >> >> >Andrew J. Kelly
>> >> >> >SQL Server MVP
>> >> >> >
>> >> >> >
>> >> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in
message
>> >> >> >news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
>> >> >> >> I got a completely independent log table,
which I
>> >> mean
>> >> >> >> there is no relationship with other tables. its
>> >> columns
>> >> >> >> look like
>> >> >> >>
>> >> >> >> nvarchar(16), nvarchar(16), nvarchar(16), int,
>> >> tinyint,
>> >> >> >> tinyint, varchar(3),smalldatetime.
>> >> >> >>
>> >> >> >> it works fine before the amount of its records
>> >> reaches
>> >> >> 10
>> >> >> >> millions around. afterwards, the insertion of
one
>> >> record
>> >> >> >> in ASP may return an ODBC Timeout Error. when
this
>> >> >> >> happens, the only way to insert a record which
I
>> can
>> >> >> >> imagine is to run the same stored procedure in
>> Query
>> >> >> >> Analyzer, Guess how long it took? 70 seconds!
>> >> >> >>
>> >> >> >> the strangest thing is, after the 70 seconds
>> >> insertion,
>> >> >> >> the next insertion only takes 1 second or
less, at
>> >> that
>> >> >> >> time, ASP page works again.
>> >> >> >>
>> >> >> >> My SQL server is SQL2000+sp3
>> >> >> >>
>> >> >> >> I noticed the same problem was reported in this
>> >> >> newsgroup
>> >> >> >> two years ago, somebody suggested using
>> aba_lockinfo
>> >> to
>> >> >> >> figure it out, I will try it when the problem
>> happens
>> >> >> >> again. In the meantime, I wonder if anybody
got a
>> >> better
>> >> >> >> solution, or knew what was the main reason of
this
>> >> >> problem.
>> >> >> >>
>> >> >> >> Thanks,
>> >> >> >>
>> >> >> >> Mingfa Ma
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >> >.
>> >> >> >
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||You have to remove the nooospam first. GoodLuck.
--
Andrew J. Kelly
SQL Server MVP
"Mingfa Ma" <mingfa@.commonname.com> wrote in message
news:0c3001c34c75$48451f40$a401280a@.phx.gbl...
> Thanks very much indeed.
> I will try your suggestions when the problem happens again.
> and I'll inform you the result. Is your email
> sqlmvpnooospam@.shadhawk.com reachable? just in case this
> topic would be too old in the group at that time, and you
> would not notice it.
> Kind regards,
> Mingfa Ma
>
> >--Original Message--
> >The main reason I mentioned changing the datatype was to
> reduce space and
> >keep the index smaller if you had one on those columns.
> If this does not
> >get queried then I suppose you don't need an index for
> that purpose but
> >sometimes a clustered index can help with insertions. In
> a heap sql server
> >needs to lookup a fair amount of information to determine
> where to place the
> >next row and with a large number of rows this may take a
> while, although
> >usually not that long<g>. With a clustered index there
> is no choice but to
> >place it where it belongs according to the index
> expression. If you use a
> >column such a an Identity or a datetime that is always in
> chronological
> >order for your index expression the new rows are always
> just added to the
> >end. It's pretty clean and efficient. First I would
> run profiler and
> >perfmon to see what is happening in the system at the
> time of the slowdown.
> >Make sure there are no high disk or cpu queues etc. Run
> sp_who2 to ensure
> >that nothing is blocking the inserts. And I would try
> the clustered index
> >on the LogDate column and see how that works out.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >news:091f01c34c62$86066d60$a501280a@.phx.gbl...
> >> Correct, there is no indexes on the table as there is
> >> nobody running selects on this table.
> >>
> >> UID and SID are not numbers, but they can be changed to
> >> varchar if that would make any difference.
> >>
> >> It is whorthwhile pointing out that this table is not
> >> being accessed frequently. 99.99% of time it is only
> used
> >> to log incoming data and there are about 15 to 30
> incoming
> >> data to be recorded within a single second. I am pretty
> >> sure nobody is runnng 'select' when the problem happens.
> >>
> >> If creating an index on this table, would it not further
> >> slow down this table? Also, this table can grow like 1
> >> million records in a day, the index of the table would
> >> also be very huge?
> >>
> >> Mingfa Ma
> >>
> >>
> >> >--Original Message--
> >> >You have no indexes at all? Is there anyone running
> >> selects on this table?
> >> >If so they will most likely take out a table lock and
> >> block the insert until
> >> >done. Just curious if the UID and SID are just numbers
> >> why ar eyou using a
> >> >NVARCHAR vs a regular VARCHAR or even an INT? You
> might
> >> consider placing a
> >> >clustered index on the table but you need to decide how
> >> you access it to
> >> >determine the proper column to use.
> >> >
> >> >--
> >> >
> >> >Andrew J. Kelly
> >> >SQL Server MVP
> >> >
> >> >
> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >> >news:0c0401c34bb0$61ec76c0$a501280a@.phx.gbl...
> >> >> Sorry, I mistook DDL as DLL.
> >> >>
> >> >> the table is very simple, no index, no trigger.
> >> >>
> >> >> Here is the definition of the table
> >> >>
> >> >> if exists (select * from dbo.sysobjects where id => >> >> object_id(N'[dbo].[n2_tblLogUserQuery]') and
> >> OBJECTPROPERTY
> >> >> (id, N'IsUserTable') = 1)
> >> >> drop table [dbo].[n2_tblLogUserQuery]
> >> >> GO
> >> >>
> >> >> CREATE TABLE [dbo].[n2_tblLogUserQuery] (
> >> >> [UID] [nvarchar] (16) COLLATE
> >> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> >> [SID] [nvarchar] (16) COLLATE
> >> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> >> [Query] [nvarchar] (128) COLLATE
> >> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> >> [CommonNameID] [int] NULL ,
> >> >> [Type] [tinyint] NULL ,
> >> >> [App] [tinyint] NULL ,
> >> >> [CountryCode] [varchar] (3) COLLATE
> >> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> >> [LogDate] [smalldatetime] NULL
> >> >> ) ON [PRIMARY]
> >> >> GO
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> >--Original Message--
> >> >> >That is the definition of the table, indexes, etc.
> You
> >> >> can get this by
> >> >> >right clicking on the table in EM and choosing "All
> >> >> Tasks - Generate Sql
> >> >> >Script" and make sure you include the indexes,
> triggers
> >> >> etc.
> >> >> >
> >> >> >--
> >> >> >
> >> >> >Andrew J. Kelly
> >> >> >SQL Server MVP
> >> >> >
> >> >> >
> >> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >> >> >news:04ff01c34b9e$9ea475c0$a001280a@.phx.gbl...
> >> >> >> Thanks, Andrew,
> >> >> >>
> >> >> >> but what do you mean "the actual DDL"? how to get
> it?
> >> >> >>
> >> >> >> here is the sample of the inserted row,
> >> >> >>
> >> >> >> 626995265,1052399332,Dering
> Harbor,0,20,100,US,2003-
> >> 07-
> >> >> 15
> >> >> >> 02:23:00
> >> >> >>
> >> >> >>
> >> >> >> Mingfa Ma
> >> >> >>
> >> >> >>
> >> >> >> >--Original Message--
> >> >> >> >Sounds like you may have page splitting going on
> >> and a
> >> >> >> really slow disk
> >> >> >> >subsystem. What is the actual DDL including
> indexes
> >> >> for
> >> >> >> the table and a
> >> >> >> >sample of the inserted row? You could also have
> >> >> >> blocking issues which can
> >> >> >> >be found with sp_who2.
> >> >> >> >
> >> >> >> >--
> >> >> >> >
> >> >> >> >Andrew J. Kelly
> >> >> >> >SQL Server MVP
> >> >> >> >
> >> >> >> >
> >> >> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in
> message
> >> >> >> >news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
> >> >> >> >> I got a completely independent log table,
> which I
> >> >> mean
> >> >> >> >> there is no relationship with other tables. its
> >> >> columns
> >> >> >> >> look like
> >> >> >> >>
> >> >> >> >> nvarchar(16), nvarchar(16), nvarchar(16), int,
> >> >> tinyint,
> >> >> >> >> tinyint, varchar(3),smalldatetime.
> >> >> >> >>
> >> >> >> >> it works fine before the amount of its records
> >> >> reaches
> >> >> >> 10
> >> >> >> >> millions around. afterwards, the insertion of
> one
> >> >> record
> >> >> >> >> in ASP may return an ODBC Timeout Error. when
> this
> >> >> >> >> happens, the only way to insert a record which
> I
> >> can
> >> >> >> >> imagine is to run the same stored procedure in
> >> Query
> >> >> >> >> Analyzer, Guess how long it took? 70 seconds!
> >> >> >> >>
> >> >> >> >> the strangest thing is, after the 70 seconds
> >> >> insertion,
> >> >> >> >> the next insertion only takes 1 second or
> less, at
> >> >> that
> >> >> >> >> time, ASP page works again.
> >> >> >> >>
> >> >> >> >> My SQL server is SQL2000+sp3
> >> >> >> >>
> >> >> >> >> I noticed the same problem was reported in this
> >> >> >> newsgroup
> >> >> >> >> two years ago, somebody suggested using
> >> aba_lockinfo
> >> >> to
> >> >> >> >> figure it out, I will try it when the problem
> >> happens
> >> >> >> >> again. In the meantime, I wonder if anybody
> got a
> >> >> better
> >> >> >> >> solution, or knew what was the main reason of
> this
> >> >> >> problem.
> >> >> >> >>
> >> >> >> >> Thanks,
> >> >> >> >>
> >> >> >> >> Mingfa Ma
> >> >> >> >>
> >> >> >> >>
> >> >> >> >
> >> >> >> >
> >> >> >> >.
> >> >> >> >
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
