How to answer why suddenly we need indexes or query needs to be changed

How to answer what changed question by dev ?

This is a very common question not only with DEV, it applies to every team in IT and business.

What changed ? ==> can be answered by facts and figures.

Facts refer to for example

• increase the amount of users that are accessing the database ?
• Any change in server configuration parameter ?
• Database Maintenance - update stats, reorg/rebuild of indexes not being performed ? Due to this the plans are being incorrectly generated !
• Amount of data has increased ?
• Changes were made on the network side, OS was patched and/or a new service pack or CU for sql server was deployed - without doing a full regression testing of your application business cycle ?
• The underlying SAN has become suddenly slow ?

Figures can be derived if you have data to show. For example :

• Baselining your server is critical during this situation. This will alleviate the blame game since you can support the facts with solid figures.
• Start collecting data using DMVs or sp_whoisactive to a table, so that the data gets persisted after a sql server reboot.

(you have to workout based on your environment and needs, on how often to collect the data/ what data to collect and how much will be the retention period) or (you can invest in a third party software like sqlsentry or idera's diagnostic manager that will do the above work for you).

Well, you could be getting a different plan because:

• the plan could have been evicted out of cache, due to:
  • a service restart
  • manual clearing of the plan cache
  • a service restart or failover
  • an inadvertent change, e.g. certain sp_configure changes can flush the cache
  • some change to underlying objects, indexes, statistics, or other dependencies triggered a recompile
• you could just be getting a different plan than the other user(s) or previous invocations because:
  • the query text might not be identical (this includes case sensitivity and whitespace, never mind different columns, join criteria, filters, etc.)
  • the query could be run by different users with different set options (or different default schemas, if any object in the plan does not have a fully-qualified name, including schema)
• the query and plan could be the same, but you could be getting different performance because:
  • the plan was cached using different parameters, and that plan is not optimal for the current set of parameters (this is commonly called "parameter sniffing")
  • the amount of data based on the parameters or simply due to data change in the meantime is significantly different
  • the data has changed enough to alter the most efficient way to access the data, but not enough to trigger statistics updates or recompiles (search for ascending key problem as well as auto-stats algorithm)
  • the data has been evicted from the buffer pool, and now has to be read from disk
  • there is higher concurrency, blocking, or other strains on resources needed to satisfy the query

I go through a lot of these in more detail here:

• Multiple Plans for an "Identical" Query

If these are running on different environments, then I have a whole series of things to check here:

• Different Plans for "Identical" Servers

Also, it is important to keep in mind that creating an index or changing the query might not be the direct reason a query suddenly performs better - sometimes it's just because those changes really did generate a new plan and/or invalidated the one that already existed.

As usual Aaron Bertrand and Kin provided excellent answers. However both answers contain a common thread. If you analyze either answer you will see that the reason why XYZ isn't working like it worked yesterday isn't because of something you/they/person X did. The reason why things changed is because the database decided to do things differently due to XYZ reasons.

A database is a living, breathing entity. Databases will make decisions, and change its mind due to a combinations of assumptions, statistics, and other heuristic tools. This is dramatically different than most application layer programming (machine learning being a notable exception).

_{I'm going to use some military references because I can't think of something better right now. A more general metaphor would be appreciated (no pun intended).}

In most applications the programmer acts as a Drill Instructor. They tell the computer exactly what to do, in what order, and sometimes for how long. Programming a database is more like acting as a Commanding Officer. You tell it what you want it to do in at a high level, and offer some guidance where needed. The database takes on the job of figuring out the best manner to execute the plan based on current intelligence like the junior officers and non-commissioned officers.

By making this distinction clear in the other programmers minds they will hopefully begin to see that you don't have dictorial powers like they do over their environment. You are guiding the database to the solution and occasionally the database gets off track for good or bad reasons. Remind them that in the end it doesn't matter why* the database went off track, but what we can do to bring it back.

_{*I recognize "why" is very valuable for future prevention, learning, etc. but it seems like the OP is facing resistance from people who aren't trying to learn about or help the problem.}