Use temporary stored procedures for performance tuning

I recently found out recently that when tuning stored procedures (e.g. for parameter sniffing), you shouldn’t try to tune the queries separately using local variables, e.g.

DECLARE @InputParameter INT = 1;

/* Query taken from stored procedure. */
SELECT [ColumeName]
FROM [TableName]
WHERE [Id] = @InputParameter;

This is because using local variables will result in SQL Server optimising the query for the density vector rather than using statistics for “real” values.

Instead, you should use temporary stored procedures, e.g.

CREATE PROCEDURE #StoredProcedureName
    @InputParameter INT
AS
    SELECT [ColumeName]
    FROM [TableName]
    WHERE [Id] = @InputParameter;
GO

EXEC #StoredProcedureName @InputParameter = 1;

Kimberly Tripp has a (much) more detailed blog post about variables & the density vector at https://www.sqlskills.com/blogs/kimberly/stored-procedure-execution-with-parameters-variables-and-literals/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Kevin Kline

Career and Technical Advice for the IT Professional

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Powershellshocked

A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!

%d bloggers like this: