Do Index Changes Remove Execution Plans from Cache?

• Impacted query plans are NOT removed from cache at the time you change an index. The next time the query is executed, it will be recompiled, and the new plan and related execution information will be placed in cache.
• This is true whether or not the index is used by the query.
• Even if the index is on columns in the table which are not referenced by the query, the query will recompile on the next run.

To see this in action, you can play along with this sample script.

Continue reading

Did that Query Eliminate Partitions? (videos)

Table partitioning makes execution plans a bit more confusing.

Free Course

I have a free online course which walks you through decoding execution plans, including whether or not partition elimination occurred.

Continue reading

Are Bad Statistics Making My Query Slow? (Dear SQL DBA Episode 39)

An important query is suddenly slow. Is it because statistics are out of date? This is tricky to figure out, and updating statistics right away can make troubleshooting even harder. Learn how to use query execution plans to get to the heart of the question and find out if stats are really your problem, or if it’s something else.

In this 35 minute episode:

• 00:39 SQL Server 2017 Announced
• 01:10 New video from Microsoft’s Joe Sack demonstrating Adaptive Query Processing
• 03:05 This week’s question: Are bad stats making my query slow?
• 05:26 Demo of finding plan in cache and analyzing stats begins
• 28:17 What to do when stats ARE the problem

Code samples are at the bottom of the page

Continue reading

Columnstore Index Returns Zero Rows... Which is One Row

I’ve never claimed to be great at math, but until recently I thought I knew how to count to one. Zero… one. That’s what we learned in kindergarten.

Apparently SQL Server didn’t go to kindergarten.

Continue reading

What is that Garbage in my Execution Plan? (Dear SQL DBA Episode 27)

Today I was working on some code samples for a user question, and I hit a weird roadblock.

There was a bunch of garbage in my execution plan that I couldn’t explain. And by ‘garbage’, I mean a nested loop to a whole branch of code that I hadn’t asked SQL Server to run – and a warning about an implicit conversion possibly causing problems with the quality of my execution plan.

Continue reading

Parallelism and tempdb data file usage in SQL Server

I’m sometimes asked if the number of CPU cores used by a query determines the number of tempdb files that the query can use.

Good news: even a single threaded query can use multiple tempdb data files.

Continue reading

Columnstore Indexes and Computed Columns in SQL Server 2016

You can’t do everything with a columnstore index – but SQL Server’s optimizer can get pretty creative so it can use a columnstore index in ways you might not expect.

Continue reading