why query might suddenly run slower in SQL, shifting from a few seconds to several minutes part-1
We are going to discuss in detail about all the possible below reasons for the sudden query performance degradation.
Today we discussed about Point 8 & 2 which is Blocking & schema changes
Here are several reasons that can contribute to this issue (The list might go on but I picked up the below)
1)Data volume growth: More rows in key tables can make previous join strategies, indexes, or row estimates suboptimal, increasing I/O and sorts/spills.
2)Schema changes like adding or dropping of indexes, Constraints or even code change to any SP/functions
3)If it is a stored procedure and it contains parameters means most likely you are prone to Parameter sniffing issue.
4)Auto-updated or manually updated statistics can change cardinality estimates, leading to a new and worse plan.
5)Plan cache eviction or recreation
6)Tempdb contention
7)Any server level changes like maxdop/Max memory/ any database scoped configuration changes (available from 2016)
8)Blockings or deadlocks
9)Concurrent workloads can create contention with respect to usage of CPU/Memory and I/O
10)Could be CPU or Memory or IO pressure
11)Changes with respect to Cardinality estimator or Compatibility level or version changes. For example all the IQP features introduced from 2017 usually benefit majority of the workload but may not work for few workloads. In that case we need to turn off that feature.
12)Introduction of skewed data to the existing table. Means one value becoming much more frequent can invalidate earlier assumptions.
13)Running of traces or unwanted Extended events which can put load on the server.
14) When we reach tipping point sometimes retrieving even one row causes SQL to go with different plan. For example top 100 and top 101 works quite differently.
15) Over running of maintenance jobs or application jobs at the time of the issue.
16) Regressed Plans