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 12»»

Poor query performance Expand / Collapse
Author
Message
Posted Thursday, November 14, 2013 7:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:17 AM
Points: 168, Visits: 695
I have a procedure making a call to a table that normally takes 5-10 seconds to return results. However, lately this has been taking 1-2 min. I have found a fix which is to rebuild an index (0% fragmentation) in the main table that the query uses, then it runs fine.

Reorganizing and/or updating statistics on that index does not help. By comparing the before and after exec plans, I’ve noticed that the Actual/estimated number of rows has changed and a key lookup & index seek changes from parallelism = False, to Parallelism = True - after the index rebuild. Other than that nothing major stands out at me when comparing the plans.

Also noticed that after restarting sql the query goes back to the old non-parallelized plan which is slower. This is occuring on both of my SQL 2005 test and production servers. Any ideas what could be causing this?
Post #1514265
Posted Thursday, November 14, 2013 7:30 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 @ 5:17 AM
Points: 40,450, Visits: 36,905
Are you sure that updating stats with fullscan doesn't help? Changes in estimated rows before/after is usually indicative of statistics-related problem (or bad parameter sniffing).


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 #1514280
Posted Thursday, November 14, 2013 7:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:17 AM
Points: 168, Visits: 695
I updated stats with full scan on the entire table. Should I try just updating them on that particular index?
Post #1514289
Posted Thursday, November 14, 2013 8:31 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 @ 5:17 AM
Points: 40,450, Visits: 36,905
No. Updating stats on the table updates all the stats on that table, index and column.


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 #1514313
Posted Thursday, November 14, 2013 8:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:17 AM
Points: 168, Visits: 695
Any other ideas? We basically have a table for each month with the same structure and this is even happening with the historical tables that have no inserts and very little read activity. There is no fragmentation.
Post #1514316
Posted Thursday, November 14, 2013 9:44 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 @ 5:17 AM
Points: 40,450, Visits: 36,905
Stale stats or bad plan in cache to be honest.
The optimiser has no knowledge of fragmentation, so that won't change plans, usually when rebuild fixes query problems it's actually the stats update that fixed things



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 #1514361
Posted Thursday, November 14, 2013 10:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:17 AM
Points: 168, Visits: 695
I use Ola Hallengren's maintenance solution, so stats are being updated nightly based on threshold.

The query uses a better plan after rebuilding the index, but could it possibly be bad stats on another table that the query uses, that's not related to the index?
Post #1514371
Posted Thursday, November 14, 2013 11:20 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 @ 5:17 AM
Points: 40,450, Visits: 36,905
Or stale stats on the same table, on a different column. Or on another table. Or a bad plan (from bad parameter sniffing) getting into cache maybe.


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 #1514407
Posted Thursday, November 14, 2013 11:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:17 AM
Points: 168, Visits: 695
I have a feeling that it's bad parameter sniffing - chasing that down now. If nothing comes up there, then I will update stats on all tables EXCEPT the one that I've already updated with no success.
Post #1514413
Posted Thursday, November 14, 2013 11:34 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 @ 5:17 AM
Points: 40,450, Visits: 36,905
Next time the query misbehaves, can you capture the actual execution plan (not estimated please) before and after the index rebuild? Without that, just guessing.


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 #1514414
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse