I recently came across an intersting case with a new stored procedure performance. The SP appeared to be run fast for one parameter value but very slow for another. My initial thought was parameter sniffing. However, a quick WITH RECOMPILE didn’t help. After turn on Live Query Statistics and look at the live data movements, I noticed that the process is doing a index scan on a small table with only 8 rows and then doing nested loop against a large table with out of wack estimates. The query has a filter on the PK column on the small table, however, the index scan is not using the clustered PK index but a non-clustered index for a column unrelated to the query. I would expect a index seek/scan on the PK index. So I did a rebuild all 3 indexes on the small table and all the sudden the SP flys and execution time dropped from over 4 minutes down to 4 seconds.
So I went back to investigate the cause of this performance issue. Run DBCC SHOW_STATISTICS on the non-clustered index revaled that the statistics is way out. The statistics only indicates 1 row in the table (where PK index indicates 7 rows, and the table has 8 rows). Even though the non-clustered index does not explicitly index on the PK column, the PK column is implicitly included in the index, and that tricked the query optimiser thoguht this non-clustered index is more “efficient” than the clustered index as its smaller.
DBCC SHOW_STATISTICS ("TableName",<Non-clustered index>)
DBCC SHOW_STATISTICS ("TableName",<clustered index>)
Even though there are index maintenance plan in place, the indexes for the table is never got rebuild as it’s under the raida of fragmentation percent threshold for index rebuild and this table data is rarelly changed. So if you have infrequently update small table, it might worth to periodically rebuild index on it, either manually or set a separate SQL job.
When doing query performance tuning, Live Query Statistics can help with quickly narrowing down to the performance pain point.