December 3, 2011 at 3:03 pm
Just a quick one (sorry for the lack of the detail but I'm hoping for general pointers rather than full on description as we have solved the speed issue now).
Massive query runs each monthend. This one joins on multiple tables including one that is 50 million rows +
Now this one Insert statement using those tables was taking 10 minutes last monthend. This monthend with no changes to server, no changes to specs, no changes to code, no changes to anything - went to 3 hours (at the point we killed it) with little or no progress.
No other jobs were running. The server was just running this code. Some tape backups were running that we stopped but no improvement. Another machine shares the virtual server so we shut that one off, no improvement.
Now it we got it back to running in 10 minutes by inserting a new index suggested by the execution plan in 2008 (against our 2005 server). So thats great.
BUT we do not know why this was no a problem before. Something I noted was that we tested the previous monthend around midmonth on our UAT box and it took the 10 minutes. Then we ran a test against the current position of the month and it behaved the same as this (3 hours)!
What could have caused this behaviour? Is there a limit on a tables size or number of records where it will suddenly need an extra index or start performing badly? Last month that table had 51904683 records, and the dataset that began running poorly had increased around 400,000 to 52354977.
Thanks for anything anyone can throw my way to consider!
December 3, 2011 at 3:13 pm
You may have reached a tipping point with other indexes and that is why adding the new index helped.
One thing you may also want to to look at is the query itself. You indicate that it is one massive query. Perhaps breaking it down into smaller steps and using temporary tables will allow you to improve the performance of this EOM routine.
December 4, 2011 at 4:37 am
It's hard to know for sure in these situations without a lot more detail. In general, I'd assume an issue with statistics and/or blocking. You say no other processes were running, but did you specifically look for blocked processes and resource contention? If the process is running long, you can use sys.dm_exec_requests to figure out which statement within the query is running slow. You can get the execution plan and compare that to when things are running fast.
That's all I've got based on the information provided.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 4, 2011 at 6:22 am
Check that you haven't run into this:
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply