|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
timeout after many inserts; possible resource exhaustion?I'm running a .NET 1.1 program on W2K/SQL Server 2K. I have a VB console program that hits a web site for news articles every minute and inserts betwen 50 and 200 articles (one row per article) into a table. The table has a full-text index on two columns with change tracking. The program runs from a .bat file in a loop; it runs, there is a GOTO, then it runs, etc. The program is running on the same system as SQL server. The program is using the ApplicationBlocks.Data library and using the ExecuteNonQuery() entry point to perform the insert for each record with text (not a stored procedure). After some number of hours the insert operation times out, with the error at the lowest entry point "The timeout period elapsed prior to completion of the operation or the server is not responding." If I stop and start SQL Server it works again for a while, then eventually gets the message again, until at some point it gets it every time, until a system reboot, when the cycle starts again. This certainly feels like a resource exhaustion problem. What could possibly be accruing to the point that it ran out? What tools are available to help find out whats leaking? There are no messages in the event logs of errors. Thanks, John Mott John
Have you tried to wrap it to the stored procedure?. It may also posible to BEGIN TRAN ....COMMIT the batch. Show quote "John Mott" <johnmot***@hotmail.com> wrote in message news:eUFqX4GtFHA.2064@TK2MSFTNGP09.phx.gbl... > Hello, > > I'm running a .NET 1.1 program on W2K/SQL Server 2K. I have a VB console > program that hits a web site for news articles every minute and inserts > betwen 50 and 200 articles (one row per article) into a table. The table > has > a full-text index on two columns with change tracking. The program runs > from > a .bat file in a loop; it runs, there is a GOTO, then it runs, etc. > > The program is running on the same system as SQL server. > > The program is using the ApplicationBlocks.Data library and using the > ExecuteNonQuery() entry point to perform the insert for each record with > text (not a stored procedure). After some number of hours the insert > operation times out, with the error at the lowest entry point "The timeout > period elapsed prior to completion of the operation or the server is not > responding." > > If I stop and start SQL Server it works again for a while, then eventually > gets the message again, until at some point it gets it every time, until a > system reboot, when the cycle starts again. > > This certainly feels like a resource exhaustion problem. What could > possibly > be accruing to the point that it ran out? What tools are available to help > find out whats leaking? There are no messages in the event logs of errors. > > Thanks, > > John Mott > > Thank you for responding.
I'm not using a stored procedure, i just have a text version of command. Would that matter? Not in an 'elegant programming' sense, in the 'something runs out' sense. Would the use of a transaction limit resources? I'm going to recode it to use the same connection for the whole run instead of using ExecuteNonQuery(), but I fear that will only stretch out the time between problems if there is something more endemic going on. john Show quote "Uri Dimant" <u***@iscar.co.il> wrote in message news:eDNM58GtFHA.2072@TK2MSFTNGP14.phx.gbl... > John > Have you tried to wrap it to the stored procedure?. It may also posible to > BEGIN TRAN ....COMMIT the batch. > > > > > "John Mott" <johnmot***@hotmail.com> wrote in message > news:eUFqX4GtFHA.2064@TK2MSFTNGP09.phx.gbl... > > Hello, > > > > I'm running a .NET 1.1 program on W2K/SQL Server 2K. I have a VB console > > program that hits a web site for news articles every minute and inserts > > betwen 50 and 200 articles (one row per article) into a table. The table > > has > > a full-text index on two columns with change tracking. The program runs > > from > > a .bat file in a loop; it runs, there is a GOTO, then it runs, etc. > > > > The program is running on the same system as SQL server. > > > > The program is using the ApplicationBlocks.Data library and using the > > ExecuteNonQuery() entry point to perform the insert for each record with > > text (not a stored procedure). After some number of hours the insert > > operation times out, with the error at the lowest entry point "The timeout > > period elapsed prior to completion of the operation or the server is not > > responding." > > > > If I stop and start SQL Server it works again for a while, then eventually > > gets the message again, until at some point it gets it every time, until a > > system reboot, when the cycle starts again. > > > > This certainly feels like a resource exhaustion problem. What could > > possibly > > be accruing to the point that it ran out? What tools are available to help > > find out whats leaking? There are no messages in the event logs of errors. > > > > Thanks, > > > > John Mott > > > > > > Some things to consider:
Check out max server memory for SQL Server on the SQL Server machine. Check fragmentation on your regular indexes. Do several inserts in each batch (each ExecuteNonQuery). Speeds it up due to less overhead and network traffic. Do several inserts in each transaction. Speeds it up due to less I/O. Consider bulk loading the data (more efficient than inserts). ADO.NET might have bulk load options. I know ADO.NET 2.0 will have bulk loading abilities from a DataReader and possibly DataTable. Post also to a full text indexing group. I wouldn't be surprised that the *slowdown* part is due to some leak in ft crawling. I'm far from an FT expert, though. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "John Mott" <johnmot***@hotmail.com> wrote in message news:eUFqX4GtFHA.2064@TK2MSFTNGP09.phx.gbl... > Hello, > > I'm running a .NET 1.1 program on W2K/SQL Server 2K. I have a VB console > program that hits a web site for news articles every minute and inserts > betwen 50 and 200 articles (one row per article) into a table. The table has > a full-text index on two columns with change tracking. The program runs from > a .bat file in a loop; it runs, there is a GOTO, then it runs, etc. > > The program is running on the same system as SQL server. > > The program is using the ApplicationBlocks.Data library and using the > ExecuteNonQuery() entry point to perform the insert for each record with > text (not a stored procedure). After some number of hours the insert > operation times out, with the error at the lowest entry point "The timeout > period elapsed prior to completion of the operation or the server is not > responding." > > If I stop and start SQL Server it works again for a while, then eventually > gets the message again, until at some point it gets it every time, until a > system reboot, when the cycle starts again. > > This certainly feels like a resource exhaustion problem. What could possibly > be accruing to the point that it ran out? What tools are available to help > find out whats leaking? There are no messages in the event logs of errors. > > Thanks, > > John Mott > > Thank you for responding.
I re-coded to use a command object on a single connection for each batch, so we'll see if that changes anything. Where would I look for max server memory and index fragmentation? I've never gotten into SQL server tuning (data center people usually handle that, this client is a one person shop and there isn't anyone I can ask). Are there good books or web sites? john "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in to less overhead andmessage news:Oyo9CGHtFHA.2756@TK2MSFTNGP09.phx.gbl... > Some things to consider: > > Check out max server memory for SQL Server on the SQL Server machine. > > Check fragmentation on your regular indexes. > > Do several inserts in each batch (each ExecuteNonQuery). Speeds it up due > network traffic. might have bulk load options.> > Do several inserts in each transaction. Speeds it up due to less I/O. > > Consider bulk loading the data (more efficient than inserts). ADO.NET > I know ADO.NET 2.0 will have bulk loading abilities from a DataReader and *slowdown* part is due topossibly DataTable. > > Post also to a full text indexing group. I wouldn't be surprised that the Show quote > some leak in ft crawling. I'm far from an FT expert, though. > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "John Mott" <johnmot***@hotmail.com> wrote in message news:eUFqX4GtFHA.2064@TK2MSFTNGP09.phx.gbl... > > Hello, > > > > I'm running a .NET 1.1 program on W2K/SQL Server 2K. I have a VB console > > program that hits a web site for news articles every minute and inserts > > betwen 50 and 200 articles (one row per article) into a table. The table has > > a full-text index on two columns with change tracking. The program runs from > > a .bat file in a loop; it runs, there is a GOTO, then it runs, etc. > > > > The program is running on the same system as SQL server. > > > > The program is using the ApplicationBlocks.Data library and using the > > ExecuteNonQuery() entry point to perform the insert for each record with > > text (not a stored procedure). After some number of hours the insert > > operation times out, with the error at the lowest entry point "The timeout > > period elapsed prior to completion of the operation or the server is not > > responding." > > > > If I stop and start SQL Server it works again for a while, then eventually > > gets the message again, until at some point it gets it every time, until a > > system reboot, when the cycle starts again. > > > > This certainly feels like a resource exhaustion problem. What could possibly > > be accruing to the point that it ran out? What tools are available to help > > find out whats leaking? There are no messages in the event logs of errors. > > > > Thanks, > > > > John Mott > > > > > Have you tested for the existence of any blocking processes? Such as updates
to the affected tables or any look-ups that might slow down your inserts. Using transactions and procedures as Uri suggests might put a little order into a possible mess (possible, not actual, since we haven't seen any code). ML Thank you for responding. In this case I'm certain that there is no other
activity to the tables (besides full-text indexing, of course). I'd like to understand the benefits of transactions and procedures from a resource utilization perspective. I can appreciate that stored procedures are already compiled and faster to execute and that transactions can help unwind a set of errors; in this case I'm blasting records into a table, and each record addition is a good thing since they stand alone, i would not want an error on the 10th record to delete the first record added. john Show quote "ML" <M*@discussions.microsoft.com> wrote in message news:04CB6020-EBDF-4725-A144-85AB9702529D@microsoft.com... > Have you tested for the existence of any blocking processes? Such as updates > to the affected tables or any look-ups that might slow down your inserts. > > Using transactions and procedures as Uri suggests might put a little order > into a possible mess (possible, not actual, since we haven't seen any code). > > > ML  |
|||||||||||||||||||||||
Other interesting topics