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, DATA_COMPRESSION = PAGE);
GO 

CREATE OR ALTER VIEW 
    dbo.PushyPaul
WITH SCHEMABINDING
AS
    SELECT 
        p.OwnerUserId,
        p.Score,
        p.CreationDate,
        p.LastActivityDate,
        PostRank = 
            DENSE_RANK() OVER
            ( 
               PARTITION BY 
                  p.OwnerUserId 
               ORDER BY     
                  p.Score DESC 
            )
    FROM dbo.Posts AS p;
GO 

SELECT 
    p.* 
FROM dbo.PushyPaul AS p
WHERE p.OwnerUserId = 22656;
GO 

CREATE OR ALTER PROCEDURE 
    dbo.StinkyPete 
(
    @UserId int
)
AS 
SET NOCOUNT, XACT_ABORT ON;
BEGIN
    SELECT 
        p.* 
    FROM dbo.PushyPaul AS p
    WHERE p.OwnerUserId = @UserId;
END;
GO 

EXEC dbo.StinkyPete 
    @UserId = 22656;



/*Start Here*/

ALTER DATABASE 
    StackOverflow2013 
SET PARAMETERIZATION SIMPLE;

DBCC TRACEOFF
(
    4199, 
    -1
);

ALTER DATABASE SCOPED CONFIGURATION 
    SET QUERY_OPTIMIZER_HOTFIXES = OFF;


SELECT 
    p.* 
FROM dbo.PushyPaul AS p
WHERE p.OwnerUserId = 22656
AND   1 = (SELECT 1); /*Avoid trivial plan/simple parameterization*/


/*Let's cause a problem!*/
ALTER DATABASE 
    StackOverflow2013 
SET PARAMETERIZATION FORCED;

SELECT 
    p.* 
FROM dbo.PushyPaul AS p
WHERE p.OwnerUserId = 22656
AND   1 = (SELECT 1); /*Avoid trivial plan/simple parameterization*/


/*Can we fix the problem?*/
DBCC TRACEON
(
    4199, 
    -1
);


SELECT 
    p.* 
FROM dbo.PushyPaul AS p
WHERE p.OwnerUserId = 22656
AND   1 = (SELECT 1); /*Avoid trivial plan/simple parameterization*/


/*That's kinda weird...*/
DBCC FREEPROCCACHE;


SELECT 
    p.* 
FROM dbo.PushyPaul AS p
WHERE p.OwnerUserId = 22656
AND   1 = (SELECT 1); /*Avoid trivial plan/simple parameterization*/


/*Turn Down Service*/
DBCC TRACEOFF
(
    4199, 
    -1
);

SELECT 
    p.* 
FROM dbo.PushyPaul AS p
WHERE p.OwnerUserId = 22656
AND   1 = (SELECT 1); /*Avoid trivial plan/simple parameterization*/


/*Okay then.*/


/*I'm different.*/
ALTER DATABASE SCOPED CONFIGURATION 
    SET QUERY_OPTIMIZER_HOTFIXES = ON;


SELECT 
    p.* 
FROM dbo.PushyPaul AS p
WHERE p.OwnerUserId = 22656
AND   1 = (SELECT 1); /*Avoid trivial plan/simple parameterization*/



/*Cleanup*/
ALTER DATABASE 
    StackOverflow2013 
SET PARAMETERIZATION SIMPLE;

ALTER DATABASE SCOPED CONFIGURATION 
    SET QUERY_OPTIMIZER_HOTFIXES = OFF;

DBCC TRACEOFF
(
    4199, 
    -1
);

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Local Variables vs Forced Parameterization In SQL Server Queries

Questions, Arise!


I think it was sometime in the last century that I mentioned I often recommend folks turn on Forced Parameterization in order to deal with poorly formed application queries that send literal rather than parameterized values to SQL Server.

And then just like a magickal that, I recommended it to someone who also has a lot of problems with Local Variables in their stored procedures.

They were curious about if Forced Parameterization would fix that, and the answer is no.

But here’s proofs. We love the proofs.

Especially when they’re over 40.

A Poorly Written Stored Procedure


Here’s this thing. Don’t do this thing. Even the index is pretty dumb, because it’s on a single column.

CREATE INDEX
    i
ON dbo.Users
    (Reputation)
WITH
    (SORT_IN_TEMPDB= ON, DATA_COMPRESSION = PAGE);
GO 

CREATE PROCEDURE
    dbo.humpback
(
    @Reputation int
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;

    /*i mean don't really do this c'mon*/
    DECLARE 
        @ReputationCopy int = ISNULL(@Reputation, 0);
    
    SELECT
        u.DisplayName,
        u.Reputation,
        u.CreationDate,
        u.LastAccessDate
    FROM dbo.Users AS u
    WHERE u.Reputation = @ReputationCopy;

END;

If we look at the statement parameterization type, available with Query Store enabled, it returns 0. That’s true whether or not we enable simple or forced parameterization:

ALTER DATABASE StackOverflow2013 SET PARAMETERIZATION FORCED;
GO 
EXEC dbo.humpback 
    @Reputation = 11;
GO 

ALTER DATABASE StackOverflow2013 SET PARAMETERIZATION SIMPLE;
GO 

EXEC dbo.humpback 
    @Reputation = 11;
GO 

For now, you’ll have to do a little more work to fix local variable problems.

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Forced Parameterization vs Optimize For Ad Hoc Workloads

Shredded Cheese


I often speak with people who are confused about what these settings do, and which one they should be using to fix certain problems.

The first myth is that Optimize For Ad Hoc Workloads has some special effect on queries run outside of stored procedures: it does not. It’s very poorly named in that regard. There are no special optimizations applied because of that setting.

If you hit up the documentation, there’s no mention of it, but because it’s named how it is, people think YEAH IT’LL MAKE MY AD HOC QUERIES FASTER!

But no. It does not.

Add Chalk


Optimize For Ad Hoc Workloads mainly helps servers where the plan cache is unstable because it gets full of plans that don’t ever get reused. You end up with a lot of churn.

Why don’t they get reused? There are a lot of reasons, but often it’s because queries either aren’t parameterized, or because parameters aren’t explicitly defined in the application.

When you turn Optimize For Ad Hoc Workloads on, first-time plans are stored as stubs. That’s all. If they get used a second time, the full plan is stored.

This is great, unless all your plans have a low rate of reuse anyway, like < 10 or so.

Paramesan Cheese


First, the documentation for Forced Parameterization is hard to find.

Second, it’s usually a *wonderful* setting for queries that are fully unparameterized. One of the limitations is that if a query is only partially parameterized, it won’t parameterize the unparameterized bits.

That kinda sucks, but I understand why it doesn’t: Microsoft thinks you’re smart and you know what you’re doing, and there must be a *very good reason* for you to only have partially parameterized a query.

For instance, to get a filtered index used, or to avoid some parameter sniffing issue with skewed data.

Which One Do You Need?


Focus on the problem you’re trying to solve.

  • If you have a lot of single use plans clogging up your plan cache and forcing a lot of churn, then Optimize For Ad Hoc Workloads can be great
  • If you have a lot of unparameterized queries creating loads of duplicate plans (maybe even single use), you want Forced Parameterization

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Forced Parameterization Doesn’t Fix SQL Injection Problems In SQL Server

Short and Sweaty


If you have stored procedures that do things like this:

IF @OwnerUserId IS NOT NULL
   SET @Filter = @Filter + N' AND p.OwnerUserId = ' + RTRIM(@OwnerUserId) + @nl;
IF @CreationDate IS NOT NULL
   SET @Filter = @Filter + N' AND p.CreationDate >= ''' + RTRIM(@CreationDate) + '''' + @nl;
IF @LastActivityDate IS NOT NULL
   SET @Filter = @Filter + N' AND p.LastActivityDate < ''' + RTRIM(@LastActivityDate) + '''' + @nl;
IF @Title IS NOT NULL 
   SET @Filter = @Filter + N' AND p.Title LIKE ''' + N'%' + @Title + N'%''' + @nl;
IF @Body IS NOT NULL
   SET @Filter = @Filter + N' AND p.Body LIKE ''' + N'%' + @Body + N'%'';';    
IF @Filter IS NOT NULL
   SET @SQLString += @Filter;

PRINT @SQLString
EXEC (@SQLString);

Or even application code that builds unparameterized strings, you’ve probably already had someone steal all your company data.

Way to go.

But Seriously


I was asked recently if the forced parameterization setting could prevent SQL injection attacks.

Let’s see what happens! I’m using code from my example here.

EXEC dbo.AwesomeSearchProcedure @OwnerUserId = 35004, 
                                @Title = NULL, 
                                @CreationDate = NULL, 
                                @LastActivityDate = NULL, 
                                @Body = N''' UNION ALL SELECT t.object_id, t.name, NULL, NULL, SCHEMA_NAME(t.schema_id) FROM sys.tables AS t; --';

If we look at the printed output from the procedure, we can see all of the literal values.

SELECT TOP (1000) p.OwnerUserId, p.Title, p.CreationDate, p.LastActivityDate, p.Body
FROM dbo.Posts AS p
WHERE 1 = 1 
AND p.OwnerUserId = 35004
AND p.Body LIKE '%' UNION ALL SELECT t.object_id, t.name, NULL, NULL, SCHEMA_NAME(t.schema_id) FROM sys.tables AS t; --%';

But if we look at the query plan, we can see partial parameterization (formatted a little bit for readability)

dang
where @0 = @1 and p . OwnerUserId = @2 
and p . Body like '%' union all select t . object_id , t . name , null , null , SCHEMA_NAME ( t . schema_id ) from sys . tables as t

Slightly More Interesting


If we change the LIKE predicate on Body to an equality…

IF @Body IS NOT NULL
   SET @Filter = @Filter + N' AND p.Body = ''' + @Body + ';';

The parameterization will change a little bit, but still not fix the SQL injection attempts.

Instead of the ‘%’ literal value after the like, we get @3 — meaning this is the third literal that got parameterized.

dang
where @0 = @1 and p . OwnerUserId = @2 
and p . Body = @3 union all select t . object_id , t . name , null , null , SCHEMA_NAME ( t . schema_id ) from sys . tables as t

But the injecty part of the string is still there, and we get the full list of tables in the database back.

Double Down


If you’d like to learn how to fix tough problems like this, and make your queries stay fast, check out my advanced SQL Server training.

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.