Query Store And Dropped Objects

Grant Fritchey gives us a potential nightmare scenario with Query Store:

Along comes our aggressive junior DBA who decides that there are “too many” indexes on the server. No, I don’t know what that means either, but they evidently read it on the internet or something so they drop the index we created before:

1
DROP INDEX TransactionOccurredWhenNCI ON Warehouse.StockItemTransactions;

What now happens to our lovely execution plan and the plan forcing? We’ll take a look at two events in Extended Events, sql_statement_recompile and query_store_plan_forcing_failed. Nothing happens immediately on dropping the index. The plans associated with that object, if any, are marked as invalid in the cache. The next time we call the query it’s going to recompile and we can see the event:

The result is a bit happier than I would have expected; I was looking forward to a “and the world came crashing down” conclusion.

Related Posts

Units Of Measure In The ShowPlan Schema

Grant Fritchey shows off the ShowPlan Schema: Because the showplan schema contains notes throughout stating what the units of measure are, what each of the values means. For instance, I can explain why SerialDesiredMemory, DesiredMemory, RequestedMemory are identical: …Provide memory grant estimate as well as actual runtime memory grant information. Serial required/desired memory attributes are […]

Read More

Good Query Store Default Settings

Erin Stellato gives us a starting point for good values for Query Store settings: QUERY_CAPTURE_MODE The default value for SQL Server 2016 and SQL Server 2017 is ALL.  For Azure SQL Database, the default value is AUTO. With AUTO, queries that are insignificant from a resource utilization perspective, or executed infrequently, are not captured.  If […]

Read More

2 Comments

  • Grant Fritchey on 2016-08-16

    I’d say that overall it was a very happy conclusion. It shows good architecture and approach by Microsoft. I really thought we might see a recompile for every execution. That would have been horrifying.

    • Kevin Feasel on 2016-08-16

      Recompilation for each execution would have been terrible, I agree. My first thought when reading your post, though, was that dropping the index would have resulted in every execution of the query failing, sort of like when you have a procedure with an index hint specified.

Comments are closed

Categories

August 2016
MTWTFSS
« Jul Sep »
1234567
891011121314
15161718192021
22232425262728
293031