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 …
Category: Temporary Tables
Of Temporary Tables And Plan Cache Pollution
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
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 Spills Cause tempdb Contention
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 Queries
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 In Cursors
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
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 …
Things SQL Server vNext Should Address: Table Variable Modifications
Canard
People still tell me things like “I only put 100 rows in table variables”, and think that’s the only consideration for their use.
There are definitely times when table variables can be better, but 100 rows is meaningless.…
Things SQL Server vNext Should Address: Common Table Expressions
Repetition Is Everything
I know what you’re thinking: this is another post that asks for a hint to materialize CTEs.
You’re wrong. I don’t want another hint that I can’t add to queries to solve a problem because the code …
Things SQL Server vNext Should Address: Local Variable Estimates
And Then The World
I’ve written at length about what local variables do to queries, so I’m not going to go into it again here.
What I do want to talk about are better alternatives to what you currently have …