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

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

Index Types: Heaps, Primary Keys, Clustered and Nonclustered Indexes (Dear SQL DBA Episode 28)

I see HEAP tables are found even when I know those tables have a clustered index, and I see a lot of forwarded records. This happens to 5 tables in my database. I can see the clustered and in some ones the non-clustered indexes… why are some scripts reporting them as heaps?

Continue reading

Bug: Incorrect modification_counter for Column Stats on Tables with a Clustered Columnstore Index

I don’t find bugs in SQL Server all that often. I find bugs in my own code all the time.

In this case I double checked, and I think it’s a real SQL Server bug.

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

Should I Upgrade to SQL Server 2016? (Dear SQL DBA Episode 22)

It’s a big week for SQL Server! And it’s the perfect week to talk about this week’s question, which is about explaining to your management why it’s worth it to upgrade to SQL Server 2016, and which features you can use right away.

Continue reading