Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Lightly fragmented indexes causing a huge difference? Expand / Collapse
Author
Message
Posted Wednesday, September 18, 2013 10:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:15 PM
Points: 40, Visits: 480
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


  Post Attachments 
SLOW.sqlplan (6 views, 1.71 MB)
FAST.sqlplan (1 view, 1.64 MB)
Post #1495998
Posted Wednesday, September 18, 2013 10:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 43,008, Visits: 36,164
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1496002
Posted Wednesday, September 18, 2013 12:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:15 PM
Points: 40, Visits: 480
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.

Post #1496084
Posted Wednesday, September 18, 2013 1:15 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 43,008, Visits: 36,164
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1496096
Posted Wednesday, September 18, 2013 1:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:15 PM
Points: 40, Visits: 480
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.
Post #1496113
Posted Wednesday, September 18, 2013 2:03 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 43,008, Visits: 36,164
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1496120
Posted Thursday, September 19, 2013 6:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:15 PM
Points: 40, Visits: 480
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!
Post #1496361
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse