I am troubleshooting a slow query...it runs in less than 100 ms 99% of the time, but once in an hr, goes bad and does 6 million reads and takes 11 seconds! I saw the query plan, it does do a clustered index scan, sys.dm_exec_cached_plans.usecounts keeps increasing every time the query executes, so i am thinking its the same plan , just wondering why at one point it goes out-of-whack! any pointers will be helpful.
Without seeing the query it is impossilbe to know. However, I would guess you are a victim of "parameter sniffing".
Have to ask, is there one value in some major table that occurs a lot more often than others, such that when it gets hit there SHOULD be a lot more work done? And if the plan was built using any of the other, much less numerous values, then the plan reuse for the big value is the problem.
In that case, it is highly unlikely that the query plan stayed the same. It is more likely that (due to stale statistics) every once in a while the optimizer chooses a poor query plan. With the same query plan, you shouldn't be seeing executions that differ 3 orders of magnitude in the number of reads.
SELECT cp.*,st.*, qp.*
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where left(Text,22) = 'select top 1 T.seq_num'
I ran the above query to see how many plans were there for the query, there were four, each were different queries. I setup a trace with a filter on the textdata and watched for a while, each time the query ran( ofcourse in few hundred milliseconds), the sys.dm_exec_cached_plans.usecount increased by one. So, I am pretty sure its the same plan and behaves badly occassionally!
How many tables are you using in this query, try to rebuild the indexes and update the statistics of tables.
DBCC DBREINDEX ('<your table name>', ' ', 80) go update statistics <tablename> go
For doing the procedure for all the tables in the database at once
EXEC sp_MSforeachtable @command1="DBCC DBREINDEX ('?', ' ', 80)" GO EXEC sp_updatestats GO
Amar Deep Singh