Register for Upcoming Free Webcasts on Indexes, Stats Maintenance, Query Tuning, and More

I’ve got a whole bunch of free, live webcasts scheduled on SQL Server training! I’d love for you to join me for these sessions.

Continue reading

Batch Mode Hacks for Rowstore Queries in SQL Server

What’s batch mode?

Batch mode was introduced as a way to help SQL Server process data from columnstore indexes faster. The whole idea with columnstore is that you pull big compressed sets of rows out for aggregation or other operations in big chunks.

Batch mode is a way that operators can work on a “batch” of up to 900 values at a time, instead of working on individual rows. Batch mode can reduce the overhead of metadata and make more efficient use of your CPUs.

Continue reading

SQLServer:Databases(_Total)\Transactions/sec vs SQL Statistics\Batch Requests/sec (video)

Perfmon counters are great for measuring workload, but choosing which counter to baseline can be confusing.

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

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