slow query....runs in less than 100 ms 99% of the time except once in an hr or two..


  • I am troubleshooting a slow 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.


    Friday, June 28, 2013 5:28 PM

All replies

  • Without seeing the query it is impossilbe to know.  However, I would guess you are a victim of "parameter sniffing".

    Please see:

    Friday, June 28, 2013 5:32 PM
  • Thanks for the is a inline query not a stored proc, there are no parameters, all values are hard coded.


    Friday, June 28, 2013 5:43 PM
  • In that case, it must be blocked by another query.
    Friday, June 28, 2013 8:29 PM
  • will blocking result in huge reads ? usually the is about 3-4 thousand reads, but when it goes bad, the reads jump to about 6 million! and the duration is about 11 seconds!


    Monday, July 01, 2013 2:00 PM
  • 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.


    Monday, July 01, 2013 5:09 PM
  • 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.


    • Proposed as answer by Naomi NModerator Monday, July 01, 2013 11:40 PM
    • Unproposed as answer by Ranga1 Tuesday, July 02, 2013 2:20 PM
    Monday, July 01, 2013 5:59 PM
  • 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!


    Monday, July 01, 2013 6:11 PM
  • 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)
    update statistics <tablename>

    For doing the procedure for all the tables in the database at once

    EXEC sp_MSforeachtable @command1="DBCC DBREINDEX ('?', ' ', 80)"
    EXEC sp_updatestats

    Amar Deep Singh

    Monday, July 01, 2013 11:01 PM