At First I Was Like... This won't work at all, because parameters are a known enemy of filtered indexes. But it turns out that some filtered indexes can be used for parameterized queries, whether they're from stored procedures, dynamic SQL, or in databases with forced parameterization enabled. Unfortunately, it seems limited to filtering out NULLs…
Snorting The Future
SQL Server 2019 introduced batch mode over row store, which allows for batch mode processing to kick in on queries when the optimizer deems it cost effective to do so, and also to open up row store queries to the possibility of Adaptive Joins, and Memory Grant Feedback.
SQL Server 2019
Introduces something pretty cool: the ability for row store indexes to be processed in batch mode. Batch mode is, of course, the preferred processing realm of column store indexes.
What Is Skewed Parallelism?
When queries go parallel, some assumptions get made:
There's enough work to keep multiple threads busy
Each thread will get an equal amount of work to do
A Reminder
With a new baby in the house, I often find myself comparing her to various things.
Barnacles. Ham. Pugs.
Unquam Oblite
This post will not change your life, but it will help me remember something.
When you decide to partition a table to take advantage of data management features, because IT IS NOT A PERFORMANCE FEATURE, or you have an existing partitioned table that takes advantage of data management features, because IT IS NOT A PERFORMANCE FEATURE, you may have or end up with nonclustered indexes that aren't aligned with the partitioning scheme.
Way Back When I blogged about the new USE HINT that logs query plans that use it to a new XE session. The use hint and session both share the same name -- query_plan_profile. There are currently some rather unfortunate limitations to the event in the GUI. Right now, there's no query plan tab so you…
Recent Updates
To SQL Server 2016 and 2017 introduced a new USE HINT that lets you direct an actual execution plan to a new Extended Event, called query_plan_profile.
The hint by itself doesn't do anything, and the XE by itself doesn't do anything. You need to have both.
Get Yourself Together
This is one of the most common patterns that I see in stored procedures. I'm going to simplify things a bit, but hopefully you'll get enough to identify it when you're looking at your own code.
Here's the stored procedure:
[crayon-6aa5b9f249dd5099365006/]
There's a lot of perceived cleverness in here.
Raise Your Hand If
You've ever wanted to play a prank on your co-workers, but just didn't have a any ideas that didn't involve exploding Hot Pockets.
Now you have something even less safe than molten cheese squirts!
Worse Than Mobile Browsing
I have a love/hate relationship with Extended Events. Yes, they're powerful. Yes, you can track interesting things.
But they're just not intuitive, like, 10 years on.
App Like That
If you've got the kind of application that sends bare-assed strings to SQL Server, you may end up with a weird choice.
Brent will be sitting on one shoulder telling you to use Forced Parameterization.
TEASE
Who Let The Docs Out?
If you head over to Microsoft's GitHub repo, you can peruse around for stuff updated recently.
Base Table
When you choose to forgo putting a clustered index on your table, you may find your queries utilizing forwarded fetches -- SQL Server's little change of address form for rows that don't fit on the page anymore.
This typically isn't a good thing, though. All that jumping around means extra reads and CPU that can be really confusing to troubleshoot.
Frankenblog
This post has been nagging at me for a while, because I had seen it hinted about in several other places, but never written about beyond passing comments.
A long while back, Conor Cunningham wrote:
This same condition applies to indexed view maintenance, but I’ll save that for another day :).
AFAIK he hasn't written about it or typed an emoji since then.
Basics
If you update statistics when the underlying objects haven't been modified, plans won't be invalidated.
That makes total sense if your statistics update doesn't also change the statistics. But what about when they do?
This Isn't A Trick Question
Hopefully it'll get you thinking about your tables, and how they're designed. One of the most consistent problems I see with clients is around wide tables.
I don't mean data types, I mean the number of columns.
Secret Squirrel When you modify a table with multiple indexes, SQL Server may choose either a narrow plan, if it doesn't think all that many rows are going to change, or a wide plan if it thinks many will. In narrow plans, the work SQL Server has to do to modify many indexes is hidden…
What Did Dinosaurs Watch On TV?
These are some of my favorite books and blogs from Microsoft from the way-back machine.
I can't say every bit of information is still 100% true and should be followed to the letter, but hey, that's what happens.
Deus Redux
A while back I wrote about the Perils of VSS Snaps.
After working with several more clients having similar issues, I decided it was time to look at things again. This time, I wanted blood. I wanted to simulate a slow VSS Snap and see what kind of waits stats I'd have to look out for.
