Award Winning
Imagine you have a rather complicated query that you want to abstract into a simple query for your less-than-with-it end users.
A view is probably a pretty good way of doing that, since you can shrink your preposterously-constructed …
Category: Indexing
Why MAX Data Types Are Usually A Bad Choice For SQL Server Columns
Easy Rider
When you’re trying to figure out how to store string data, it often seems easiest to just choose an extra long — even MAX — data type to avoid future truncation errors.
Even if you’re storing strings with …
SQL Server 2022 CTP 2.1 Improvements To Parameter Sensitive Plan Optimization
Hop On Your Good Foot
Several weeks back, I blogged about a missed opportunity with the new parameter sensitive plan feature. At the time, I thought that there was indeed sufficient skewness available to trigger the additional plan variants, …
SQL Server 2017 CU 30: The Real Story With SelOnSeqPrj Fixes
I am a heading
Thanks for watching! Demo scripts below.
Demo Scripts
USE StackOverflow2013;
EXEC dbo.DropIndexes;
SET NOCOUNT ON;
DBCC FREEPROCCACHE;
GO
CREATE INDEX
chunk
ON dbo.Posts
(OwnerUserId, Score DESC)
INCLUDE
(CreationDate, LastActivityDate)
WITH
(MAXDOP = 8, SORT_IN_TEMPDB = ON, … SQL Server 2017 CU 30 Doesn’t Actually Fix The Problem With Views And Parameters
I am a heading
In the release notes for SQL Server 2017 CU30, there’s a note that it fixes a problem where parameters can’t be pushed passed Sequence Project operators:
“In Microsoft SQL Server 2017, running parameterized queries skips …
Fixing Ordered Column Store Sorting In SQL Server 2022
Groove Is In The Heart
When Brent posted about the availability of, and disappointment with creating ordered column store indexes in SQL Server 2022, I got to work.
I can’t have my dear friend Brent being all distraught with …
SQL Server 2022 Parameter Sensitive Plan Optimization: Does Not Care To Fix Your Local Variable Problems
–To Fix Parameter Sniffing
There are some code comments you see that really set the stage for how tuning a query is going to go.
Usually one misgiving about how SQL Server works gives way to a whole levee-breaking bevy …
SQL Server 2022 Parameter Sensitive Plan Optimization: How PSP Can Help Some Queries With IF Branches
Time Served
I’ve spent a bit of time talking about how IF branches can break query performance really badly in SQL Server.
While the Parameter Sensitive Plan (PSP) optimization won’t fix every problem with this lazy coding habit, it …
SQL Server 2022 Parameter Sensitive Plan Optimization: Sometimes There’s Nothing To Fix
Best Intentions
After seeing places where the Parameter Sensitive Plan (PSP) optimization quite stubbornly refuses to kick in, it’s somewhat amusing to see it kick in where it can’t possibly have any positive impact.
Even though some parameters are responsible …