15 Reasons Your Query Was Fast Yesterday, But Slow Today

SQLSaturday Israel 2019 speaker shirt

In rough order of how frequently I see ’em:

  1. There are different workloads running on the server (like a backup is running right now.)
  2. You have a different query plan due to parameter sniffing.
  3. The query changed, like someone did a deployment or added a field to the select list.
  4. You have a different query plan due to a statistics change.
  5. You’re getting a different share of hardware (perhaps someone changed a hardware setting, introduced a noisy neighbor VM, or you have hardware competition in the cloud.)
  6. Someone added or removed an index.
  7. Someone added or removed a trace flag or other server-level config setting.
  8. Your application itself changed, and it’s not able to digest the results as quickly.
  9. Someone patched, and it had an unexpected side effect.
  10. You have the same plan, but different memory grants.
  11. Someone’s modifying more rows at a time, so you’re hitting lock escalation.
  12. Your query is being throttled by Resource Governor.
  13. It never really was that fast to begin with.
     
    And starting with SQL Server 2017 (if you have columnstore indexes) and SQL Server 2019 (for everyone):
     
  14. You’re getting a different memory grant due to adaptive memory grants.
  15. You’re getting a different join type due to the adaptive join threshold changing.

 

Previous Post
Which Database is Causing the Problem?
Next Post
Free SQL Server Load Testing Tools

14 Comments. Leave new

  • Someone Changed MAXDOP and CTOP. Just a thought

    Reply
  • #13 definitely resonates. You hear how fast their job was in the past but now seems slow…first thing I look at is history of the job. That’s when I find out people’s perceptions are from a different dimension in space/time.

    Reply
  • “Somebody changed isolation level from default read committed to repeatable read and your query is hitting blocking now and taking forever to finish” this also could be one addition on above list.

    Great article as always.

    Reply
  • You just didn’t need one more thing on your plate today and this is urgent

    Reply
  • Lauria Ruggiero
    April 19, 2019 3:54 am

    You have changed the compatibility level of the DB, or moved the DB to a newer SQL Version

    Reply
  • There is another (somewhat rare) case that I have seen that is related to #4. I’ve seen certain query performance regressions before where the statistics on a table need to be updated, but they have not yet hit the internal thresholds to trigger automatic stats updates. Because of this, the query plan that was previously optimized to only process a few records now has to process a few thousand records, and slowness ensues. It’s not really a parameter sensitivity issue, because the query is always run with the same parameter values, and all database/server configurations are the same.

    Reply
  • Rafael A Colon Garcia
    April 19, 2019 8:28 am

    Brilliant, your list also remind us that features keep changing and many times is depends on what version of SQL you are running to troubleshoot in the right place. Fortunately with new features also comes new tools to the rescue, like the query Store for example. I think this list is a great guide to keep ourselves aware that not all the performance issues start with looking at the query plan , although usually is a good place to start.

    Reply
  • Did someone run the SQL Server Query Tuning Advisor and deploy every single index suggested, then shrink the database directly after for good measure?

    Reply
  • The perennial the table/database has grown and the developers did not do volume testing

    Reply
  • 11.a: your table scan is getting blocked because oltp

    Reply
  • Did someone store a value in XML then CROSS APPLY in a subquery to get the individual values into the recordset?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}