SQL Server query performance affected by rebuilding an un-fragmented index

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

Statistics is your keys to performance

Try run the following:

EXEC sp_updatestats 'RESAMPLE'

or

on selected table

UPDATE statistics YourTable WITH RESAMPLE