Understanding
I’ve covered this sort of information in previous posts, like:
But some points should be made together, so I’m going to combine them …
Category: Temporary Tables
Software Vendor Mistakes With SQL Server: Not Getting Parallel Inserts Into #Temp Tables
Do That, But Faster
Serial zones in parallel plans can leave a lot of performance on the table. One of the best ways to see that is with insert queries that do a lot of work. A big contrast …
Software Vendor Mistakes With SQL Server: Not Using #Temp Tables
Wind Charms
After yesterday’s post about when to use or not use @table variables in SQL Server, you can probably make choices more confidently.
Most of the time, you want to be using #temp tables, when plan choices …
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 and getting code formatting right can be tricky in blog post …
How #Temporary Tables Can Cause Plan Cache Pollution In SQL Server
I Love #Temp Tables
I solve a lot of problems with #temp tables, indeed I do. And I hate people who are reflexively anti-#temp table.
If you’re into jokes (I’m definitely not into jokes; SQL is serious business), you …
LOB Data, Recompile, And Too Much tempdb Usage In SQL Server
Stumper
I got a very interesting issue about sp_HumanEvents recently, and I couldn’t quite figure out why things were going wonky.
The problem was that at some point when monitoring blocking long-term, something was taking up a whole lot of …
When Query Spills Cause tempdb Contention And Performance Issues In SQL Server
Temptables
I was helping a client troubleshoot a scalability issue recently, where during one point in their workload tempdb went out of control.
What was puzzling them was that nothing in the stored procedure did anything with temporary objects, cursors, …
SARGability Week: Using Temp Tables To Fix Non-SARGable Query Performance Problems In SQL Server
International
This post is especially interesting because it highlights the oddball performance situations you can run into when you write the type of All-In-One™ queries that the optimizer is typically not very good at handling, and also rely on expressions …
Local Variables Get Bad Cardinality Estimates In Cursors In SQL Server, Too
Work Off
While helping a client out with weird performance issues, we isolated part of the code that was producing a whole bunch of bad plans.
At the intersection of bad ideas, there was a cursor looping over a table …
Why Expressions Are Better Than Local Variables For Performance In SQL Server Queries
Often Enough
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 they need to perform a calculation on one of the parameters that get …