SQL Server Performance Forum – Threads Archive
Long time to add record
Hi all,this is my first post here and I appreciate any help you can give me. I wrote an app using a VB.NET/ADO.NET front end and SQL Server DB. About two weeks ago a user reported a timeout error. I capture all errors to a log file and that was what it was, literally the error read ‘Timeout expired’. I opened Enterprise Manager and opened the table. I attempted to add the record directly in the grid. When I had filled in the required fields and tabbed to the next row to insert the record, there was a long pause, about 60-90 seconds if I remember correcly. The record was added, but it should have been immediate. I’m fairly new to .NET and SS, but I assume the timeout was a function of the command object rather than the DB and that’s why I got the error from the app. Anyway I didn’t know what to do so I backed up the DB and restored it to another DB with a different name to see if the new DB exibited the same behavior. The new one did not, but strangely the behavior disappeared on the original DB too. All was well until I get a call an hour or so ago that it’s happening again. This time on a different table. I tried the backup/restore thing again and it didn’t work. Any ideas why this is happening? I’m no DBA, my knowledge of SQL Server is just basic. Any help would be appreciated. Darrell
there could be several causes for this. Table indices are heavily fragmented, or the clustered index has been chosen badly, requiring a lot of reorganising of data on disk to make room for the new row. This would depend on the data being inserted, which is why it doesnt happen everytime.
There could be more than one table with this problem, which might explain why it appears though the problem doesnt depend on the table Another possibility is that the database is having to grow because it has reached its capacity. If the database is set to grow by a percentage (10% is the defalt), and the database is large, then to grow by 10% may take some time. After it has grown the problem would disappear for a while, until it next reaches capacity. Finally if there is lots of activity the insert may be blocking because of locks. Firstly check the execution plan of the insert statement. This might highlight a major problem. Next run SQL Profiler and see the activity during the insert. This will give you a definite answer for what causes the problem
quote:Originally posted by DWinTX
Hi all,
this is my first post here and I appreciate any help you can give me. I wrote an app using a VB.NET/ADO.NET front end and SQL Server DB. About two weeks ago a user reported a timeout error. I capture all errors to a log file and that was what it was, literally the error read ‘Timeout expired’. I opened Enterprise Manager and opened the table. I attempted to add the record directly in the grid. When I had filled in the required fields and tabbed to the next row to insert the record, there was a long pause, about 60-90 seconds if I remember correcly. The record was added, but it should have been immediate. I’m fairly new to .NET and SS, but I assume the timeout was a function of the command object rather than the DB and that’s why I got the error from the app. Anyway I didn’t know what to do so I backed up the DB and restored it to another DB with a different name to see if the new DB exibited the same behavior. The new one did not, but strangely the behavior disappeared on the original DB too. All was well until I get a call an hour or so ago that it’s happening again. This time on a different table. I tried the backup/restore thing again and it didn’t work. Any ideas why this is happening? I’m no DBA, my knowledge of SQL Server is just basic. Any help would be appreciated. Darrell
Do not add directly records through EM and follow the suggestions of Chappy.
this is my first post here and I appreciate any help you can give me. I wrote an app using a VB.NET/ADO.NET front end and SQL Server DB. About two weeks ago a user reported a timeout error. I capture all errors to a log file and that was what it was, literally the error read ‘Timeout expired’. I opened Enterprise Manager and opened the table. I attempted to add the record directly in the grid. When I had filled in the required fields and tabbed to the next row to insert the record, there was a long pause, about 60-90 seconds if I remember correcly. The record was added, but it should have been immediate. I’m fairly new to .NET and SS, but I assume the timeout was a function of the command object rather than the DB and that’s why I got the error from the app. Anyway I didn’t know what to do so I backed up the DB and restored it to another DB with a different name to see if the new DB exibited the same behavior. The new one did not, but strangely the behavior disappeared on the original DB too. All was well until I get a call an hour or so ago that it’s happening again. This time on a different table. I tried the backup/restore thing again and it didn’t work. Any ideas why this is happening? I’m no DBA, my knowledge of SQL Server is just basic. Any help would be appreciated. Darrell
Surendra Kalekar
quote:Another possibility is that the database is having to grow because it has reached its capacity. If the database is set to grow by a percentage (10% is the defalt), and the database is large, then to grow by 10% may take some time. After it has grown the problem would disappear for a while, until it next reaches capacity.
Also, check if you have autoshrink enabled on the db. If you have, better disable it.
Hi, You should also take the help of network administrator to check the connectivity of the client with the server. Usually I used to get timeout error when my network response is poor. Please checkout Thanks and Regards Ravi K
Thanks for the responses. The problem went away after about 2 hours. I checked out each of your suggestions and here’s what I found: DB is set to grow at 15%. Current size is a about 10GB so I guess it grew by a little more than 1GB this last time. The server has a 3.06 mhz Xeon processor with 2GB of RAM. It doesn’t do much else besides run this DB and my app. Any idea how long it should take to grow 1.5GB? Would it take 2 hours? I don’t think activity levels are high. It’s a small company with about 15 users. They add about 100-200 records a day. Also run a few reports but that’s it. I also use optimistic concurrency in my code, so I don’t thinks locks are much of a problem. Autoshrink was enabled, I disabled it. I don’t think network connectivity is the issue. This server is a Terminal Server and I can log in to the server directly with the TS Client, run the app from the server, and I still get the behavior. I don’t know if the indices are fragmented, how can I check this? Chappy, not sure what you mean by ‘Execution Plan’ of the Insert statement. I use standard SQL INSERT command assigned to the DataAdapter.InsertCommand object. Wasn’t familiar with SQL Profiler. I’m reading up on it now so I can use it when this happens again. Surrendra, is it really bad to add a record via EM if just for testing purposes? I don’t do this with legitimate records, I just did it to see if I might get a more verbose error from SQL Server than ‘Timeout expired’. I deleted it afterwards. How is this different than adding a record with a SQL statement? Thanks all for the help, I’ve got a lot to learn about SQL Server.
Even though SQL Server is very good at more or less automatically maintaining most statistics, physical data structuring and configurations etc, there are still some situations where you need to perform a little manual work to help it. One of those areas is fragmentation. You can check the fragmentation level using DBCC SHOWCONTIG. When reviewing the output from DBCC SHOWCONTIG you should pay special attention to the following metrics: Avg. Page Density (full): Shows the average level of how filled the pages are. A percentage means the pages are almost full, and a low indicates much free space. This value should be compared to the fill factor setting specified when the index was created to decide whether or not the index is internally fragmented. Scan Density: Shows the ratio between the Best Count of extents that should be necessary to read when scanning all the pages of the index, and the Actual Count of extents that was read. This percentage should be as close to 100% as possible. Defining an acceptable level is difficult, but anything under 75% definitely indicates external fragmentation. Logical Scan Fragmentation: Shows the ratio of pages that are out of logical order. The value should be as close to 0% as possible and anything over 10% indicates external fragmentation. Note: The value for Extent Scan Fragmentation is not really important. This is because the algorithm that calculates it does not work for indexes that span over multiple files. According to some Microsoft sources, in the next version of SQL Server, DBCC SHOWCONTIG will not even include this metric.
I hope this helps in resolving fragmentation issues if any. Regards, Chetan.
Hi,<br /><br />Sometimes it takes time for a while when developer use "requery" ,and if their is a trigger fires on INSERT STATEMENT , and it might be taking time while inserting records due to "Auto Shrink" enable , index defrag is not done…<br />and the best thing to optimize and sort out this is use SQL PROFILER AND IF YOU DONT KNOW MUCH ABOUT IT USE THIRD PARTY TOOL COEFFICIENT.<br /><br />HTH.<br /><br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br />Regards.<br /><br />hsGoswami<br />[email protected]
Do you see improvement after disabling autoshrink? You need also to defragment indexes, because database shrinking increases fragmentation. You can use dbcc command mentioned to see what is level of fragmentation to decide which indexes to defragment.
]]>