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

Checking for the Existence of Global Temp Table in TSQL

Every now and again, I need use a global temporary table for some testing or demo code.

Each time I do, I stumble a little bit when it comes to checking for the existence of the global temp table, in order to make my code re-runnable.

Continue reading

Error 1204: When SQL Server Runs Out of Locks

I recently did a Dear SQL DBA episode answering a question about lock timeouts and memory in SQL Server. I really enjoyed the episode, and thought it would be fun to follow up and show what it looks like if SQL Server doesn’t have enough memory to allocate locks.

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

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

DBCC CLONEDATABASE Does NOT Clone Index Usage Statistics

The word ‘statistics’ is awfully confusing in SQL Server

It can mean “statistics” themselves – little objects that describe the distribution of data in columns or indexes to help the optimizer.

Or it can mean “usage statistics” -  dynamic management views that let you see how many times an index has been used or requested, how many times a query has been run, that kind of thing.

Continue reading