Farce
The index and proc creation script for this are a bit on the long side, so I’m going to stick them in a GitHub gist to keep the post concise, since we have some other ground to cover here.…
Category: Statistics
What’s The Best Statistics Sample Rate For SQL Server?
Big Think
Most people fire off their index and statistics maintenance jobs with the default options and do just fine. Sometimes, though, statistics need a little bit more consideration, and I get a lot of questions about them.
- Do I
Trace Flag 3608 Disables Automatic Statistics Creation And Ghost Record Cleanup In SQL Server
Short Bad
According to the docs:
…Prevents SQL Server from automatically starting and recovering any database except the master database. If activities that require TempDB are initiated, then model is recovered and TempDB is created. Other databases will be
Creating An Agent Job To Update Statistics Using Ola Hallengren’s Scripts
Hubba Hubba
Over in my GitHub repo, I’ve added a file that will create an Agent Job to update statistics using Ola Hallengren’s IndexOptimize script.
It’s something I hand out enough that I figured people might be interested in …
One Thing The “New” Cardinality Estimator Does Better In SQL Server
Or “Default”, If That’s Your Kink
Look, I’m not saying there’s only one thing that the “Default” cardinality estimator does better than the “Legacy” cardinality estimator. All I’m saying is that this is one thing that I think it does …
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, …