Mythology
I’ve heard many times incorrectly over the years that CTEs somehow materialize data.
But a new one to me was that CTEs execute procedurally, and you could use that to influence plan shapes by always doing certain things first.…
Category: Query Tuning
Reading, Locking, Sniffing
Make It Out
I’m going to show you something bizarre. I’m going to show it to you because I care for your well-being and long term mental health.
Someday you’ll run into this and be thoroughly baffled, and I want …
Query Tuning SQL Server 2019 Part 5: I’m Not Going Back
Butt Out Bag
There was one thing that I didn’t talk about earlier in the week.
You see, there’s a mystery plan.
It only shows up once in a while, like Planet X. And when it does, we get bombarded …
Query Tuning SQL Server 2019 Part 4: Long Live The Query Tuner
Rumors Of My Demise
Let’s talk about some common hints that people use to fix parameter sniffing:
- RECOMPILE: Won’t work here to get us a better plan for VoteTypeId 5, because it sucks when the optimizer knows what’s coming
- OPTIMIZE
Query Tuning SQL Server 2019 Part 3: Who Died And Made You The Optimizer?
Be Yourself
We’ve got a problem, Sam Houston. We’ve got a problem with a query that has some strange issues.
It’s not parameter sniffing, but it sure could feel like it.
- When the procedure compiles and runs with VoteTypeId 5,
Query Tuning SQL Server 2019 Part 2: Big Databases, Big Ideas
Are We Still Friends?
When I first wrote this demo, I called it dbo.ParameterSniffingMonstrosity.
Because , you know, it’s really terrible.
CREATE OR ALTER PROCEDURE dbo.VoteSniffing( @VoteTypeId INT )
AS
SET XACT_ABORT, NOCOUNT ON;
BEGIN
SELECT ISNULL(v.UserId, 0) AS … Query Tuning SQL Server 2019 Part 1: Changing Databases
Teeth To Grit
I’ve always had trouble standing still on SQL Server versions, but most companies don’t. Hardly anyone I talk to is on SQL Server 2017, though these days SQL Server 2016 seems more common than SQL Server 2012, …
Lock Waits Come From Blocking, Not Locks Being Taken
Waiting Game
In SQL Server, troubleshooting blocking problems is a pain. It’s one of those things you really have to be monitoring for pretty actively in order to catch the full extent of it.
You might catch glimpses of it …
SQL Server 2019: Index Choice and Batch Mode On Rowstore
A/B Testing
Let’s look at a pretty simple query against the Votes table:
SELECT v.VoteTypeId,
MIN(v.CreationDate) AS min_creation_date,
MAX(v.CreationDate) AS max_creation_date,
COUNT_BIG(*) AS records
FROM dbo.Votes AS v
GROUP BY v.VoteTypeId
ORDER BY v.VoteTypeId;
There are only two column involved:…
Using Exchange Demand Partitioning to Improve Parallel Query Scalability
One of our SQL Server workloads runs many concurrent parallel queries on a possibly already busy server. Other work occurring on the server can have dramatic effects on parallel query runtimes. One way to improve scalability of parallel query runtimes …