MS SQL please speed up so i dont cry
October 20, 2009 9:07 AM Subscribe
MS SQL Pros, I need your help! What's causing MS SQL 2005 to import records so slowly? It's a persistent problem across all databases that just developed yesterday. Details inside...
So my 3rd party app that I use to import records into MS SQL 2005 Standard has gone from approximately 10K records per hour to 500 records per hour. I've tried creating new databases to test on, and they all give me the same result.
I'm no MS SQL expert, but I did a bit of googling and created a trace file. It looks like the delay (according to the Start and End Time in the trace record) is between an "IF @@TRANCOUNT > 0 COMMIT TRAN" and "SELECT ID FROM tlbFolders WHERE NAME="C:\PATHTODATA\". The delay between these 2 events is about 10 seconds, and I can't tell why it's acting so slow.
I'll follow up in the thread, let me know if you need more info or know of any additional resources that can help me answer this question, or any good troubleshooting tips. Thanks!
So my 3rd party app that I use to import records into MS SQL 2005 Standard has gone from approximately 10K records per hour to 500 records per hour. I've tried creating new databases to test on, and they all give me the same result.
I'm no MS SQL expert, but I did a bit of googling and created a trace file. It looks like the delay (according to the Start and End Time in the trace record) is between an "IF @@TRANCOUNT > 0 COMMIT TRAN" and "SELECT ID FROM tlbFolders WHERE NAME="C:\PATHTODATA\". The delay between these 2 events is about 10 seconds, and I can't tell why it's acting so slow.
I'll follow up in the thread, let me know if you need more info or know of any additional resources that can help me answer this question, or any good troubleshooting tips. Thanks!
Response by poster: Sorry, I mistyped a bit. I really should take a deep breath, as this problem is approaching pound-head-on-desk proportions! The second statement reads fully:
SELECT ID FROM tblFolders WHERE Name =N'C:\PATHTODATA\'
Sorry for the confusion - and this is the 3rd party app that's writing this query, so there's not much I can do about it. I think this statement is just going through my folder and picking out files one by one, correct? It's the delay between the COMMIT statement and this one that seems to be the culprit, but that's my amateur analysis. I'll make sure the tblFolders table is indexed, but I'll also point out that the delay happens even if I create a whole new DB, and it will be slow even between importing the first and second records. Thanks for your help!
posted by antonymous at 10:19 AM on October 20, 2009
SELECT ID FROM tblFolders WHERE Name =N'C:\PATHTODATA\'
Sorry for the confusion - and this is the 3rd party app that's writing this query, so there's not much I can do about it. I think this statement is just going through my folder and picking out files one by one, correct? It's the delay between the COMMIT statement and this one that seems to be the culprit, but that's my amateur analysis. I'll make sure the tblFolders table is indexed, but I'll also point out that the delay happens even if I create a whole new DB, and it will be slow even between importing the first and second records. Thanks for your help!
posted by antonymous at 10:19 AM on October 20, 2009
What is the recovery model for your DB? Is it set to FULL? Are you getting a lot of logging?
posted by capnsue at 12:05 PM on October 20, 2009
posted by capnsue at 12:05 PM on October 20, 2009
Response by poster: Yes, my recovery model is set to full on the databases. You think changing to simple would have an impact? This SQL Server is pretty much standard out-of-the-box, so if there are any settings that are enabled/disabled by default that shouldn't be normally, let me know. I assume that means I'm doing a lot of logging, but I'm not really sure what to check. I work in IT, but databases are not my forte :(
posted by antonymous at 12:10 PM on October 20, 2009
posted by antonymous at 12:10 PM on October 20, 2009
Often when inserting data on indexed tables, especially large amounts of data relative to the current size of the table, it's dramatically faster to disable any indexes on the table, insert your data, then rebuild the indexes after.
Also in your trace above, it looks like the 10 second gap you are seeing is not the SELECT statement, but the previous transaction getting executed before that SELECT gets run. Look at what the previous transaction does.
posted by spatula at 12:25 PM on October 20, 2009
Also in your trace above, it looks like the 10 second gap you are seeing is not the SELECT statement, but the previous transaction getting executed before that SELECT gets run. Look at what the previous transaction does.
posted by spatula at 12:25 PM on October 20, 2009
Response by poster: Right - it's the COMMIT that's seemingly taking forever...is there any way to optimize that? I'll try turning off the indexing and reimporting, but I think I've tried it already. I've also reindexed the databases in question, but to no avail. Wouldn't hurt to try importing into a non-indexed BD and trying again, I guess. Thanks for the help so far everyone!
posted by antonymous at 12:46 PM on October 20, 2009
posted by antonymous at 12:46 PM on October 20, 2009
The logging can make a huge difference in performance...for loading data I always use simple or bulk-logged.
Do you know if the third-party app is using INSERT or BULK INSERT?
posted by capnsue at 2:29 PM on October 20, 2009
Do you know if the third-party app is using INSERT or BULK INSERT?
posted by capnsue at 2:29 PM on October 20, 2009
What are your file size settings and auto-growth settings?
If the initial file size on your DB files is/are not big enough, then SQL Server will go out and try to grab more disk space. If your autogrowth number is set low, it has to do this A LOT of times for a big import. We just had a DBA mess up and set initial file size to 200MB and autogrowth to 13MB (!). I executed an insert of 20 million rows into a table, and by my calculation the DB "auto-grew" about 600 times. It was insanely slow.
If this is something that only started to happen recently, then it is possible that before now, your files hadn't filled their initial size setting, but now they have and your autogrow setting is too low. As a general rule of thumb, set autogrowth to 10%. (Again -- that is a rule of thumb -- your situation may call for higher or lower numbers.)
posted by peripatetic007 at 7:36 AM on October 21, 2009
If the initial file size on your DB files is/are not big enough, then SQL Server will go out and try to grab more disk space. If your autogrowth number is set low, it has to do this A LOT of times for a big import. We just had a DBA mess up and set initial file size to 200MB and autogrowth to 13MB (!). I executed an insert of 20 million rows into a table, and by my calculation the DB "auto-grew" about 600 times. It was insanely slow.
If this is something that only started to happen recently, then it is possible that before now, your files hadn't filled their initial size setting, but now they have and your autogrow setting is too low. As a general rule of thumb, set autogrowth to 10%. (Again -- that is a rule of thumb -- your situation may call for higher or lower numbers.)
posted by peripatetic007 at 7:36 AM on October 21, 2009
Not sure if you're still having this problem, but you might try running a SQL Profiler trace to see if you can pinpoint where the bottleneck really is. As peripatetic007 says, make sure you're not autogrowing -- that's a performance killer. When the import is running, you should also look at the waittypes; you may have better performance by fiddling with the max degree of parallelism (if so, you'll see CXPACKET waittypes) or you may find that your disk response is poor (you'll see PAGE_IO_LATCH waittypes). Also, if the file you're loading is on a different server than your database, you may have network performance issues (you'll see ASYNCH waittypes). Again, the waittypes will help determine where exactly the problem is. Make sure you're not getting hung up by blocking. Lastly, you may need to update the statistics on your database; I've found with a lot of SQL2K5 databases that we have to update the statistics much more frequently than we had to with SQL2K.
Good luck. Feel free to memail me.
posted by Maisie at 5:29 PM on November 13, 2009
Good luck. Feel free to memail me.
posted by Maisie at 5:29 PM on November 13, 2009
This thread is closed to new comments.
SELECT ID FROM tlbFolders WHERE NAME="C:\PATHTODATA\"
First of all, I hope [NAME] isn't the actual column name! Second, you're matching on a text field, which is extremely inefficient and if not indexed can get slow if that table gets large. Is tblFolders the table you're inserting into? If so, that would explain a lot.
posted by mkultra at 9:35 AM on October 20, 2009