Software Vendor Mistakes With SQL Server: Not Parameterizing Queries

Posted on by Erik Darling

You Won’t Stop

Continuing a bit on yesterday’s theme of parameterization, another type of mistake I see software vendors make quite a bit is not parameterizing queries at all, or only partially parameterizing them. When you do this, you harm SQL Server’s ability to cache and reuse execution plans. There are very few situations where this is advisable outside of data warehouses.

There are all sorts of things that can cause this that aren’t just confined to places where you’d traditionally consider parameterization, like TOP, OFFSET/FETCH, and even static values in a SELECT list.

If you’re reading this with some knowledge of SQL Server, the reason I say it harms the ability to cache plans is because there are limits to plan cache size, and the more pollution you cause the more churn there is.

Partially parameterizing queries has the additional misfortune of not being a problem that the forced parameterization setting can solve.

Hold Up

To simulate what happens when you don’t parameterize queries, we can use unsafe dynamic SQL. In this case, it’s probably not the worst sin against dynamic SQL since we’re using an integer limited to a two byte string, but you know, I’d be a bad blogger if I didn’t point that out.

DECLARE
    @i int = 1,
    @sql nvarchar(MAX) = N'';


WHILE @i <= 10
BEGIN
    
    SELECT 
        @sql = N'    
        SELECT
            c = COUNT_BIG(*),
            s = SUM(p.Score * 1.),
            m = MAX(u.DisplayName),
            r = MIN(u.Reputation)
        FROM dbo.Users AS u
        JOIN dbo.Posts AS p
            ON p.OwnerUserId = u.Id
        WHERE u.Reputation = ' + CONVERT(nvarchar(2), @i) + ';
    ';
    
    RAISERROR
    ( 
        @sql,
        0,
        1
    )
    WITH
        NOWAIT;

    SET STATISTICS XML ON;
        EXEC sys.sp_executesql
            @sql;
    SET STATISTICS XML OFF;
    
    SELECT 
        @i += 1;
    
END;

After that, we can look at the plan cache and see what happened.

Large Amounts

These 10 executions will generate 3-4 different query plans, but even when the same plan is chosen, it has different identifiers.

beleefs

For high frequency execution queries, or more complicated queries (think lots of left joins, etc.) that may spend a lot of time in the optimization process trying out different join orders and plan shapes, you probably want to avoid not parameterizing queries, or only partially parameterizing them.

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 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.

Related Posts