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

Index Maintenance and Performance (Dear SQL DBA Episode 38)

They made their index maintenance job smarter, and their queries got slower in production afterward. Could the index maintenance have harmed performance?

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

Data Type Mismatches Do Not Always Cause a Bad Implicit Conversion and Index Scan

Here’s a great recent question that I got about query tuning and index use:

Assuming that the documented levels of data type precedence in SQL Server are true as of SQL 2016, why does a bigint value not force an index scan when compared against an int column?

Continue reading

New online course: Troubleshooting Blocking & Deadlocks for Beginners (Free!)

I made y’all a SQL Server style Valentine’s day present: a new FREE online training course.

Continue reading

Should I Learn Fulltext Indexing? (Dear SQL DBA Episode 29)

This week’s question is about a longstanding feature in SQL Server that sounds really cool: full-text search. If you’re learning performance tuning, how much time should you invest in researching and learning about full-text indexes?

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