Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query within a stored procedure that the same input will periodically time out ( > 30 seconds) when it normally runs in milliseconds.

The table has 2077748 rows, 59 columns, and 13 indexes. We have found that rebuilding one of the indexes takes the performance back to milliseconds.

Here is the fun part. The percentage fragmentation on this index every time it is rebuilt is < 1%, the last time it was .01%. I've been playing with the fill factors because I thought it may be an expansion issue, it was 100, then 80, now 60. This has not effected the frequency of this problem.

When I run the following

select b.name,* 
from sys.dm_db_index_physical_stats(5,1075691080,NULL,NULL,NULL) a
JOIN sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id

I get

index_id partition_number index_type_desc alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages page_count

66 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 0.01 1116 18.9229390681004 21118

Any suggestions on other places to look or sources of this problem?

share|improve this question

migrated from stackoverflow.com Feb 6 '12 at 15:41

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 3 down vote accepted

It sounds like the rebuild of the index is a red herring, especially given it was non-clustered. The rebuild is more likely to be invalidating a bad query plan in cache, and on re-run the good query plan is generated and used again.

Captue the query plan when the performance is bad and then after the rebuild and compare them, I suspect you will see a different plan.

That would be my first step - e.g. demonstrate what changes to the plan due to the index rebuild, or prove there is no change on the plan.

share|improve this answer
    
this will take a while for me to catch, thanks for the advice. –  Jeremy Gray Feb 3 '12 at 18:19

Index rebuild has the side effect of updating the statistics. This is what probably causes the change. Do you have AUTO_UPDATE_STATISTICS/ASYNC enabled?

Update

Perhaps I gave too much credit to the due diligence performed and I assumed the OP to read that only an index rebuild fixes the problem. On second though I must have read it properly, since OP never actually checked the plans, as an index rebuild solves the issue, which translates into 'any action that invalidates the cached plan results into a new plan that is correct'. In other words a bad plan (parameter sniffing, indeed) makes its way into the cache and it keeps being used until it becomes obsolete due to metadata version change (ie. index rebuild). Plan analysis should confirm this, as well as explicit bad plan eviction: DBCC FREEPROCCACHE(0x0....) passing in the bad plan handle retrieved from sys.dm_exec_query_stats/sys.dm_exec_requests. The topic is covered at length by Erland Sommarskog in Slow in the Application, Fast in SSMS? Understanding Performance Mysteries. The best solution is to deploy plan guides.

And, of course, any performance investigation should rely on a good methodology like Waits and Queues.

share|improve this answer
    
Auto Update Statistics is enabled. –  Jeremy Gray Feb 3 '12 at 18:18
2  
Index rebuild will update the stats with fullscan whereas auto update will use a sample so the stats may end up better but I agree with Andrew this might just be a parameter sniffing issue anyway. –  Martin Smith Feb 3 '12 at 18:31
    
I'd thought about it being stats, it's certainly a valid possibility, but gut feeling is query plan. –  Andrew Feb 3 '12 at 18:53

Statistics is your keys to performance

Try run the following:

EXEC sp_updatestats 'RESAMPLE'

or

on selected table

UPDATE statistics YourTable WITH RESAMPLE
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.