Using Batch Mode for the “Matching Supply With Demand” Challenge

Itzik Ben-Gan posted an interesting T-SQL challenge on SQL performance dot com. I’m writing up my solution in my own blog post because I have a lot to say…

Read the post: Using Batch Mode for the “Matching Supply With Demand” Challenge

When Query Spills Cause tempdb Contention And Performance Issues In SQL Server

I was helping a client troubleshoot a scalability issue recently, where during one point in their workload tempdb went out of control.

Read the post: When Query Spills Cause tempdb Contention And Performance Issues In SQL Server

Local Variables Get Bad Cardinality Estimates In Cursors In SQL Server, Too

While helping a client out with weird performance issues, we isolated part of the code that was producing a whole bunch of bad plans.

Read the post: Local Variables Get Bad Cardinality Estimates In Cursors In SQL Server, Too

Why Expressions Are Better Than Local Variables For Performance In SQL Server Queries

I see people writing stored procedures that use local variables either because they read that all functions in a where clause are dangerous, or because…

Read the post: Why Expressions Are Better Than Local Variables For Performance In SQL Server Queries

When Should You Use Table Variables In SQL Server? When Queries Execute Thousands Of Times A Minute

Table variables beat temp tables when a workload runs tiny queries at very high frequency and very high concurrency, because temp tables hit tempdb…

Read the post: When Should You Use Table Variables In SQL Server? When Queries Execute Thousands Of Times A Minute

What’s Really Different About In-Memory Table Variables In SQL Server?

My dear friend Kendra asked… Okay, look, I might have dreamed this. But I maybe dreamed that she asked what people’s Cost Threshold For Blogging™ is.

Read the post: What’s Really Different About In-Memory Table Variables In SQL Server?

Making The Most Of Temp Tables Part 4: Batch Mode

When you have queries that need to process a lot of data, and probably do some aggregations over that lot-of-data, batch mode is usually the thing you…

Read the post: Making The Most Of Temp Tables Part 4: Batch Mode

Making The Most Of Temp Tables Part 3: More Opportune Indexes To Make Queries Go Faster

You have too many indexes on too many tables already, and the thought of adding more fills you with a dread that has a first, middle, last, and even a…

Read the post: Making The Most Of Temp Tables Part 3: More Opportune Indexes To Make Queries Go Faster

Making The Most Of SQL Server Temporary Tables Part 2: Materializing Expressions

A lot of the time when I see queries that are written with all sorts of gymnastics in the join or where clause and I ask some questions about it, people…

Read the post: Making The Most Of SQL Server Temporary Tables Part 2: Materializing Expressions

Making The Most Of Temp Tables In SQL Server Part 1: Fully Parallel Inserts

If you have a workload that uses #temp tables to stage intermediate results, and you probably do because you’re smart, it might be worth taking advantage…

Read the post: Making The Most Of Temp Tables In SQL Server Part 1: Fully Parallel Inserts

Starting SQL: I Don’t Care How Many Rows Are In Your Table Variable, They Can Still Hurt Performance

So you’re that odd soul who has been listening to rumors about table variables. Perhaps things about them only being in memory, or that they’re okay to use…

Read the post: Starting SQL: I Don’t Care How Many Rows Are In Your Table Variable, They Can Still Hurt Performance

Starting SQL: Why Your Query Can’t Go Parallel, Table Variables Edition

The important thing to understand about parallelism is it’s great when appropriate. Striking a balance between what should go parallel, the CPU usage it’s…

Read the post: Starting SQL: Why Your Query Can’t Go Parallel, Table Variables Edition

Experiment With Table Variable Deferred Compilation Without SQL Server 2019

If you haven’t heard by now, Table Variables have some ✌features✌ that can cause performance issues pretty generally in SQL Server.

Read the post: Experiment With Table Variable Deferred Compilation Without SQL Server 2019