September 18, 2013 at 10:03 am
Hello all.
I am having an ongoing issue in a production vendor application, and I don’t know what to look at next.
I have 2 copies of the exact same database.
1 copy has indexes that have been freshly rebuilt with next to zero fragmentation.
1 copy has indexes that were rebuilt 7 days prior.
When running the exact same query against these 2 databases, in the freshly reindexed DB, the query runs in 10 seconds. In the week old indexed DB, it just under 4 mins.
Initially to get around this issue, we began rebuilding our indexes every 2 weeks. However, it has gotten to the point that after a Sunday morning rebuild, by Monday afternoon, the queries are back to running long within the app, sometimes not returning at all.
The highest fragmentation level on the slow running database, with the tables involved in the query, is 30%, most other indexes are less than 1% fragmented.
I have moved both databases to the same server instance, to use same SQL version (SQL 2005 ENT SP4 CU3) with the same hardware.
Looking at the execution plans of each, it appears that in the slow running database, the most expensive part of the query is a Hash Match (76%), while at the same point in the Indexed database, it is a nested loop at 0%.
I am not a programmer by any means, but I don’t understand how indexes that are not overly fragmented can cause such an issue. And also don’t understand how it can occur so fast after a complete Index rebuild.
I’ve attached the execution plans to this post. Any assistance would be great appreciated.
Thank you
September 18, 2013 at 10:19 am
It's not the fragmentation.
Your statistics are getting out of date. I'd guess either auto_update stats is off or these are large tables. The stats aren't getting updated, the estimated row count is far off the actual in the slow query and hence the plan the optimiser comes up with is a pile of garbage.
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
September 18, 2013 at 12:53 pm
Gail, Thank you so much for your quick reply. I was hoping to hear from you on this one.
In the past we went down the stats path, and now we are updating stats on the 2 larger tables in the query every 6 hours. That seemingly worked for a while, but seems to have fallen off.
Auto_update stats is on, and as you can see from the row counts in the tables involved in the query, the tables are not terrible large either, though some are very wide.
ROW_COUNTS
1,314,740
1,019,205
457,865
315,543
315,383
107,365
95
0
0
0
0
0
0
In our bi-weekly Index rebuild job, I am immediately running sp_updatestats following the Index rebuild. I figured it was more efficiant this way.
After reading your reply to this post, I did a manual update statistics to each table in the query...execution time actually increased (granted a shared test server with shared disk).
After these results, I ran a full sp_updatestats against the database. That ran for 5 + minutes, and now the query is returning results in 2 seconds.
September 18, 2013 at 1:15 pm
mike.hockman (9/18/2013)
In our bi-weekly Index rebuild job, I am immediately running sp_updatestats following the Index rebuild. I figured it was more efficiant this way.
That could actually be harmful. Index rebuilds update statistics. Running sp_updatestats afterwards can then update the stats with a sample and make the stats less accurate. I would recommend you remove that and replace it with an UPDATE STATISTICS <stats name> WITH FULLSCAN every day that you don't have an index rebuild running. If you need it more frequently on some tables, well I've heard of some cases where stats are updated on some tables every hour.
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
September 18, 2013 at 1:53 pm
Thanks for the advice Gail.
My mindset was that running sp_updatestats after the index rebuild would only update the stats that were not taken care of during the rebuild. However, I see your point about the sampling being incomplete vs the rebuild.
I will create a script to run UPDATE STATISTICS <TABLE> with FULL SCAN against every table in the database, and see how that improves things.
Any idea why with AUTO_UPDATE STATS ON would not be taking care of this and I would need to manually account for it?
Thanks again for your help.
September 18, 2013 at 2:03 pm
Auto update only kicks in after 20% of the table has changed. On larger tables that's no where near sufficient. There have been traceflags added to SQL 2008 to adjust the threshold but they're not back-ported into SQL 2005.
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
September 19, 2013 at 6:43 am
Gail, initial results this morning after the job ran last night look very promising.
Just wanted to drop back and say thank you again for your help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply