Stored procedures (SPs) have been a part of T-SQL from the beginning. SPs provide a means for creating server-side subroutines written in T-SQL. This chapter begins with a discussion of what SPs are and why you might want to use them, and it continues with a discussion of SP creation and usage, including examples.

Introducing Stored Procedures

SPs are saved collections of one or more T-SQL statements stored on the server as code units. They are analogous to procedures or subroutines in procedural languages like Visual Basic or C#. And just like procedures in procedural languages, SPs give you the ability to effectively extend the language of SQL Server by letting you add named custom subroutines to your databases.

An SP declaration begins with the CREATE PROCEDURE keywords followed by the name of the SP. Microsoft recommends against naming the SP with the prefix sp_. This prefix is used by SQL Server to name system stored procedures and is not recommended for user SPs in databases other than the master database. The name can specify a schema name and procedure name, or just a procedure name. If you don’t specify a schema name when creating an SP, SQL Server will create it in the default schema for your login. It’s a best practice to always specify the schema name so that your SPs are always created in the proper schema, rather than leaving it up to SQL Server. SQL Server allows you to drop groups of procedures with the same name with a single DROP PROCEDURE statement.

SPs, like the T-SQL user-defined functions (UDFs) discussed in Chapter 4, can accept and return parameter values from and to the caller. The parameters are specified in a comma-separated list following the procedure name in the CREATE PROCEDURE statement. Unlike UDFs, when you call an SP, you can specify the parameters in any order, and omit them altogether if you assigned a default value at creation time. You can also specify OUTPUT parameters, which return values back from the procedure. All of this makes SP parameters far more flexible than UDF.

Each parameter is declared as a specific type and can also be declared as OUTPUT or with the VARYING keyword (for cursor parameters only). When calling SPs, you have two choices: you can specify parameters by position or by name. If you specify an unnamed parameter list, the values are assigned based on position. If you specify named parameters in the format @parameter = value, they can be in any order. If your parameter specifies a default value in its declaration, you don’t have to pass a value in for that parameter. Unlike UDFs, SPs don’t require the DEFAULT keyword as a placeholder to specify default values. Just leaving a parameter out when you call the SP will apply the default value to that parameter.

Unlike UDFs, which can return results only via the RETURN statement, SPs can communicate with the caller in a variety of ways:

• The RETURN statement of the SP can return an int value to the caller. Unlike UDFs, SPs do not require a RETURN statement. If the RETURN statement is left out of the SP, 0 is returned by default if no errors were raised during execution.
• SPs don’t have the same restrictions on database side effects and determinism as do UDFs. SPs can read, write, delete, and update permanent tables. In this way, the caller and SP can communicate information to one another through the use of permanent tables.
• When a temporary table is created in an SP, that temporary table is available to any SPs called by that SP. There are two types of temporary tables, local and global. The scope of the local temporary table is the current session and the global temporary table is all the sessions. The local temporary table is prefixed with # and the global temporary table is prefixed with ##. Furthermore, the temporary tables are accessible to any SPs subsequently called by those SPs. As an example, if dbo.MyProc1 creates a local temporary table named #Temp and then calls dbo.MyProc2, dbo.MyProc2 will be able to access #Temp as well. If dbo.MyProc2 then calls dbo.MyProc3, dbo.MyProc3 will also have access to the same #Temp temporary table. Global temporary tables are accessible by all users and all connections after they are created. This provides a useful method of passing an entire table of temporary results from one SP to another for further processing.
• Output parameters provide the primary method of retrieving scalar results from an SP. Parameters are specified as output parameters with the OUTPUT keyword.
• To return table-type results from an SP, the SP can return one or more result sets. Result sets are like virtual tables that can be accessed by the caller. Unlike views, updates to these result sets by applications do not change the underlying tables used to generate them. Also, unlike TVFs and inline functions that return a single table only, SPs can return multiple result sets with a single call.

Metadata Discovery

SQL Server 2012 introduces two new stored procedures and supporting Dynamic Management Views (DMV) to provide new capabilities to help determine metadata associated with code batches or stored procedures. This set of capabilities replaces the SET FMTONLY option, which is being deprecated. Often it is necessary to determine the format of the result set without actually executing the query and there are also scenarios where we have to ensure that the column and parameter metadata from query execution is compatible or identical with the format you specified before executing the query. For example, if you want to generate dynamic screens based on a select statement, you need make sure there are no metadata errors after query execution, so in turn you need to determine if the parameter metadata is compatible pre- and post-query execution.

The new functionality introduces metadata discovery capabilities for result set and parameters using the stored procedures sp_describe_first_result_set and sp_describe_undeclared_parameters and DMV’s dm_exec_describe_first_result_set and dm_exec_describe_first_result_set_for_object.

The stored procedure sp_describe_first_result_set analyzes all possible first result sets and returns the metadata information for the first result set that is executed from the input T-SQL batch. If the stored procedure returns multiple result sets, this procedure will only return the first result set. If SQL Server is unable to determine the metadata for the first query, then an error will be raised.This procedure takes three parameters: @tsql passes the T-SQL batch, @params passes the parameters for the T-SQL batch, and @browse_information_mode determines if additional browse information for each result set is returned.

Alternatively you can use the DMV sys.dm_exec_describe_first_result_set to query against, and this DMV returns the same details as the stored procedure sp_describe_first_result_set. You can use the DMV sys.dm_exec_describe_first_result_set_for_object to analyze objects such as stored procedures or triggers in the database and return the metadata for the first possible result set and the errors associated with them. Let’s say you want to analyze all the objects within the database and use the information for documentation purpose; instead of analyzing the objects one by one, you can use the DMV sys.dm_exec_describe_first_result_set_for_object with query similar to following:

SELECT p.name, p.schema_id, x.* FROM sys.procedures p CROSS APPLY
sys.dm_exec_describe_first_result_set_for_object(p.object_id,0) x

The stored procedure sp_describe_undeclared_parameters analyzes the T-SQL batch and returns the suggestion for the best parameter datatype based on least number of conversions. This feature is very useful when you have complicated calculations or expressions and you are trying to figure out the best datatype for the undeclared parameter value.

Calling Stored Procedures

You can call an SP without the EXECUTE keyword if it is the first statement in a batch. For instance, if you have an SP named MyProc with schema dbo, you can call it like this:

dbo.MyProc;

We recommend you qualify stored procedures with schema names. If a nonqualified stored procedure is called then the database engine looks for the procedure in the following order: in the sys schema, in the caller’s default schema, and then in the dbo schema. On the other hand, you can invoke an SP from anywhere in a batch or from another SP with the EXECUTE statement. Calling it like the following will discard the int return value:

EXECUTE dbo.MyProc;

If you need the return value from the SP, you can use the following variation of EXECUTE to assign the return value to a predefined int variable:

EXECUTE @variable = dbo.MyProc;

Listing 5-1 is a simple SP example with the schema Person that accepts an AdventureWorks employee’s ID and returns the employee’s full name and e-mail address via output parameters.

Listing 5-1.  Retrieving an Employee’s Name and E-mail with an SP

CREATE  PROCEDURE  Person.GetEmployee  (@BusinessEntityID  int  =  199,
    @Email_Address nvarchar(50) OUTPUT,
    @Full_Name nvarchar(100) OUTPUT)
AS
BEGIN
    -- Retrieve email address and full name from HumanResources.Employee table
    SELECT @Email_Address = ea.EmailAddress,
        @Full_Name = p.FirstName + ' ' + COALESCE(p.MiddleName,'') + ' ' + p.LastName
    FROM  HumanResources.Employee  e
    INNER JOIN Person.Person p
        ON  e.BusinessEntityID  =  p.BusinessEntityID
    INNER JOIN Person.EmailAddress ea
        ON  p.BusinessEntityID  =  ea.BusinessEntityID
    WHERE e.BusinessEntityID = @BusinessEntityID;
    --  Return a code of 1 when no match is found, 0 for success
    RETURN (
        CASE
        WHEN  @Email_Address  IS  NULL  THEN  1
        ELSE 0
        END
    );
END;
GO

The SP in the example, Person.GetEmployee, accepts a business entity ID number as an input parameter and returns the corresponding employee’s e-mail address and full name as output parameters. If the business entity ID number passed in is valid, the SP returns 0 as a return value; otherwise 1 is returned. Listing 5-2 shows a sample call to the Person.GetEmployee SP, with results shown in Figure 5-1.

Listing 5-2.  Calling the Person.GetEmployee SP

-- Declare variables to hold the result
DECLARE @Email nvarchar(50),@Name nvarchar(100),@Result int;
--Call procedure to get employee information
EXECUTE @Result = Person.GetEmployee 123, @Email OUTPUT, @Name OUTPUT;

Display the results SELECT @Result AS Result, @Email AS Email, @Name AS [Name];

The sample SP call retrieves the information for the employee with ID number 123 in variables, and displays the results in a result set via SELECT. Notice that the OUTPUT keyword in the call to the SP is required after the two output parameters.

Let’s discuss another common scenario we come across, which occurs when we need to define or modify the metadata of the stored procedure or dynamic SQL or batch. For example there are cases where the column names need to be redefined to indicate the result set better, or the data type needs to be changed for certain columns.

You can write complex code using table variables or temporary tables or even use openrowset without creating a temporary table, however you will need to enable the Ad Hoc Distributed Queries feature. SQL 2012 introduces a new feature called WITH RESULT SETS to the EXECUTE statement that allows you to modify the data types or column names returned by the stored procedure. One thing to keep in mind when using WITH RESULT SETS is that the column set should match the number of columns in the SP execution. If the data type for the column returned by the query does not match the WITH RESULT SETS option, SQL Server will make an attempt to convert the data returned by the query implicitly and will raise an error if the conversion is not possible.

Listing 5-3 is a slight modification to the SP example that you saw in Listing 5-1. This stored procedure accepts an AdventureWorks employee’s ID and returns the contact’s id, full name, title, last updated date, and the type of contact.

Listing 5-3.  Retrieving a Contact’s ID, Name, Title, and DOB with an SP

CREATE  PROCEDURE  Person.GetContactDetails  (@ID  int)
AS
BEGIN
 SET NOCOUNT ON
    -- Retrieve Name and title for a given PersonID
  SELECT @ID, p.FirstName + ' ' + COALESCE(p.MiddleName,'') + ' ' + p.LastName, ct.[Name], cast(p.ModifiedDate as varchar(20)), 'Vendor Contact'
 FROM [Purchasing].[Vendor] AS v
 	INNER JOIN [Person].[BusinessEntityContact] bec
 	ON bec.[BusinessEntityID] = v.[BusinessEntityID]
 	INNER JOIN [Person].ContactType ct
 	ON ct.[ContactTypeID] = bec.[ContactTypeID]
 	INNER JOIN [Person].[Person] p
 	ON p.[BusinessEntityID] = bec.[PersonID]
 WHERE bec.[PersonID] = @ID;
END;
GO

The SP in the example, Person.GetContactDetails, accepts a BusinessEntityID number as an input parameter and returns the corresponding contact’s id, name, title, last updated date, and type of contact in the result set, and there is nothing fancy about any of that. However, when the result set is being returned, the output column names have to be ContactID, ContactName, Title and LastUpdatedBy, and also the data type for the column LastUpdatedBy has to be varchar. Listing 5-4 shows a sample call to Person.GetContactDetails using WITH RESULT SETS. Figure 5-2 shows the resulting output.

Listing 5-4.  Calling the Person.GetContactDetails SP

-- Declare variables to hold the result
DECLARE @ContactID int;
SET @ContactID = 1511;
--Call procedure to get consumer information
EXEC dbo.GetContactDetails @ContactID with result sets(
(
ContactID int,--Column Name changed
ContactName varchar(200),--Column Name changed
Title varchar(50),--Column Name changed
LastUpdatedBy varchar(20),--Column Name changed and the data type has been changed from date to varchar
TypeOfContact varchar(20)

))

The feature WITH RESULT SETS can be extended to Multiple Active Result Sets (MARS) as well. MARS Connection is the connection attribute that enables the applications to execute multiple batches in one connection. Let’s extend the stored procedure in Listing 5-5 and see how we can use this feature with MARS.

Listing 5-5.  Retrieving a Contact’s ID, Name, Title, and DOB with an SP

ALTER  PROCEDURE  Person.GetContactDetails
AS
BEGIN
 SET NOCOUNT ON
    -- Retrieve Name and title for a given PersonID
  SELECT p.BusinessEntityID, p.FirstName + ' ' + COALESCE(p.MiddleName,'') + ' ' + p.LastName, ct.[Name], cast(p.ModifiedDate as varchar(20)), 'Vendor Contact'
 FROM [Purchasing].[Vendor] AS v
 	INNER JOIN [Person].[BusinessEntityContact] bec
 	ON bec.[BusinessEntityID] = v.[BusinessEntityID]
 	INNER JOIN [Person].ContactType ct
 	ON ct.[ContactTypeID] = bec.[ContactTypeID]
 	INNER JOIN [Person].[Person] p
 	ON p.[BusinessEntityID] = bec.[PersonID];
 SELECT p.BusinessEntityID, p.FirstName + ' ' + COALESCE(p.MiddleName,'') + ' ' + p.LastName, ct.[Name], cast(p.ModifiedDate as varchar(20)), p.Suffix, 'Store Contact'
 FROM [Sales].[Store] AS s
 	INNER JOIN [Person].[BusinessEntityContact] bec
 	ON bec.[BusinessEntityID] = s.[BusinessEntityID]
 	INNER JOIN [Person].ContactType ct
 	ON ct.[ContactTypeID] = bec.[ContactTypeID]
 	INNER JOIN [Person].[Person] p
 	ON p.[BusinessEntityID] = bec.[PersonID];
END;
GO

Listing 5-6 shows a sample call to Person.GetContactDetails using WITH RESULT SETS. Figure 5-3 shows the output. The order of the rows in the result set may vary in your system.

Listing 5-6.  Calling the Modified Person.GetContactDetails SP

--Call procedure to get consumer information
EXEC Person.GetContactDetails  with result sets(
--Return Vendor Contact Details
(
ContactID int,--Column Name changed
ContactName varchar(200),--Column Name changed
Title varchar(50),--Column Name changed
LastUpdatedBy varchar(20),--Column Name changed and the data type has been changed from date to varchar
TypeOfContact varchar(20)
),
--Return Store Contact Details
(
ContactID int,--Column Name changed
ContactName varchar(200),--Column Name changed
Title varchar(50),--Column Name changed
LastUpdatedBy varchar(20),--Column Name changed and the data type has been changed from date to varchar
Suffix varchar(5),
TypeOfContact varchar(20)
)

)

As with UDFs, there are additional options you can specify when you create a procedure. The options include the following:

• The ENCRYPTION option obfuscates the SP text and helps prevent unauthorized users from accessing the obfuscated text. This option does for SPs what the UDF ENCRYPTION option does for functions.
• The RECOMPILE option prevents the SQL Server engine from caching the execution plan for the SP, forcing runtime compilation of your SP.
• The EXECUTE AS clause specifies the context that the SP will run under. You can specify CALLER, SELF, OWNER, or a specific username with the EXECUTE AS clause. These options are the same as they are for the UDF EXECUTE AS clause, described in Chapter 4.

Additionally, you can specify FOR REPLICATION to create an SP specifically for replication purposes. An SP created with the FOR REPLICATION option can’t be executed on the replication subscriber. FOR REPLICATION can’t be used with the RECOMPILE option, and this option is not available for contained databases either. A contained database stores all the database and application level objects within the database such as tables, functions, schemas, logins, linked sever details, etc.

Managing Stored Procedures

T-SQL provides two statements that allow you to modify and delete SPs: the ALTER PROCEDURE and DROP PROCEDURE statements, respectively. ALTER PROCEDURE allows you to modify the code for an SP without first dropping it. The syntax is the same as the CREATE PROCEDURE statement, except that the keywords ALTER PROCEDURE are used in place of CREATE PROCEDURE. ALTER PROCEDURE, like CREATE PROCEDURE, must always be the first statement in a batch. Using the CREATE, DROP and ALTER PROCEDURE statements forces SQL Server to generate a new query plan. The advantage of ALTER over CREATE or DROP is that ALTER preserves the permissions for the object whereas CREATE or DROP resets the permissions.

To delete a procedure from your database, use the DROP PROCEDURE statement. Listing 5-7 shows how to drop the procedure created in Listing 5-1.

Listing 5-7.  Dropping the Person.GetEmployee SP

DROP PROCEDURE Person.GetEmployee;

You can specify multiple SPs in a single DROP PROCEDURE statement by putting the SP names in a comma-separated list. Note that you cannot specify the database or server name when dropping an SP, and you must be in the database containing the SP in order to drop it. Additionally, as with other database objects, you can grant or deny EXECUTE permissions on an SP through the GRANT and DENY statements.

Stored Procedures Best Practices

Stored procedures enable you to store batches of Transact-SQL or Managed CLR (Common Language Runtime) code centrally on the server. Stored procedures can be very efficient, and here are some of the best practices that can aid development and avoid common pitfalls that can hurt performance.

• Use the SET NOCOUNT ON statement after the AS keyword, as the first statement in the body of the procedure when you have multiple statements within your stored procedure. This turns off the DONE_IN_PROC messages that SQL Server sends back to the client after each statement in the stored procedure is executed. This also reduces the processing performed by SQL Server and the size of response sent across the network.
• Use schema names when creating or referencing the stored procedure and the database objects within the procedure. This will help SQL Server to find the objects faster and thus reduces compile lock, which will result in less processing time.
• Do not use SP_ or sys** prefixes for naming user-created database objects. They are reserved for Microsoft and have different behaviors.
• Avoid using scalar functions in SELECT statements that return many rows of data. Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.
• Avoid the use of SELECT * and select only the columns you need. This will reduce the processing in the database server as well as network traffic.
• Use parameters when calling stored procedures to increase performance. In your stored procedures explicitly create parameters with type, size, and precision to avoid type conversions.
• Use explicit transactions by using BEGIN/END TRANSACTION and keep transactions as short as possible. The longer the transaction, the more chances you have for locking or blocking and in some cases deadlocking as well. So, keep the transactions short to reduce blocking and locking.
• Use the Transact-SQL TRY...CATCH feature for error handling inside a procedure. TRY...CATCH can encapsulate an entire block of Transact-SQL statements. If you are using TRY...CATCH with loops, place it outside the loop for better performance.This not only creates less performance overhead; it also makes error reporting more accurate with significantly less programming.
• Use NULL or NOT NULL for each column in a temporary table. The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way the Database Engine assigns the NULL or NOT NULL attributes to columns when these attributes are not specified in a CREATE TABLE or ALTER TABLE statement. If a connection executes a procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behavior. If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created by using the same nullability for all connections that execute the procedure.
• Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values. The UNION filters and removes the duplicate records, whereas the UNION ALL operator requires less processing overhead since duplicates are not filtered out of the result set.

Stored Procedure Statistics

SQL Server 2012 provides DMVs and dynamic management functions (DMFs) to expose SP query plan usage and caching information that can be useful for performance tuning and general troubleshooting. Listing 5-15 is a procedure that retrieves and displays several relevant SP statistics from a few different DMVs and DMFs.

Listing 5-15.  Procedure to Retrieve SP Statistics with DMVs and DMFs

CREATE  PROCEDURE  dbo.GetProcStats  (@order  varchar(100)  =  'use')
AS
BEGIN
    WITH GetQueryStats
    (
        plan_handle,
        total_elapsed_time,
        total_logical_reads,
        total_logical_writes,
        total_physical_reads
    )
    AS
    (
        SELECT
        qs.plan_handle,
        SUM(qs.total_elapsed_time)  AS  total_elapsed_time,
        SUM(qs.total_logical_reads)  AS  total_logical_reads,
        SUM(qs.total_logical_writes) AS total_logical_writes,
        SUM(qs.total_physical_reads)  AS  total_physical_reads
        FROM sys.dm_exec_query_stats qs
        GROUP BY qs.plan_handle
    )
    SELECT
        DB_NAME(st.dbid) AS database_name,
        OBJECT_SCHEMA_NAME(st.objectid, st.dbid) AS schema_name,
        OBJECT_NAME(st.objectid, st.dbid) AS proc_name,
        SUM(cp.usecounts) AS use_counts,
        SUM(cp.size_in_bytes) AS size_in_bytes,
        SUM(qs.total_elapsed_time) AS total_elapsed_time,
        CAST
        (
        SUM(qs.total_elapsed_time)  AS  decimal(38, 4)
        )  /  SUM(cp.usecounts)  AS  avg_elapsed_time_per_use,
        SUM(qs.total_logical_reads) AS total_logical_reads,
        CAST
        (
        SUM(qs.total_logical_reads)  AS  decimal(38, 4)
        )  /  SUM(cp.usecounts)  AS  avg_logical_reads_per_use,
        SUM(qs.total_logical_writes) AS total_logical_writes,
        CAST
        (
        SUM(qs.total_logical_writes)  AS  decimal(38, 4)
        )  /  SUM(cp.usecounts)  AS  avg_logical_writes_per_use,
        SUM(qs.total_physical_reads) AS total_physical_reads,
        CAST
        (
        SUM(qs.total_physical_reads)  AS        decimal(38, 4)
        )  /  SUM(cp.usecounts)  AS  avg_physical_reads_per_use,
        st.text
    FROM  sys.dm_exec_cached_plans  cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
    INNER JOIN GetQueryStats qs
        ON  cp.plan_handle  =  qs.plan_handle
    INNER JOIN sys.procedures p
        ON st.objectid = p.object_id
        WHERE p.type IN ('P', 'PC')
        GROUP BY st.dbid, st.objectid, st.text
        ORDER BY
        CASE  @order
        WHEN  'name'  THEN  OBJECT_NAME(st.objectid)
        WHEN 'size' THEN SUM(cp.size_in_bytes)
        WHEN  'read'  THEN      SUM(qs.total_logical_reads)
        WHEN  'write'  THEN  SUM(qs.total_logical_writes)
        ELSE  SUM(cp.usecounts)
        END DESC;
END;
GO

This procedure uses the sys.dm_exec_cached_plans and sys.dm_exec_query_stats DMVs in conjunction with the sys.dmexecsqltext DMF to retrieve relevant SP execution information. The sys.procedures catalog view is used to limit the results to only SPs (type P). Aggregation is required on most of the statistics since the DMVs and DMFs can return multiple rows, each representing individual statements within SPs. The dbo.GetProcStats procedure accepts a single parameter that determines how the result rows are sorted. Setting the @order parameter to size sorts the results in descending order by the sizeinbytes column, while read sorts in descending order by the totallogicalreads column. Other possible values include name and write—all other values sort by the default usecounts column in descending order.

Listing 5-16 demonstrates how to call this SP. Sample results are shown in Figure 5-10.

Listing 5-16.  Retrieving SP Statistics

EXEC dbo.GetProcStats @order = 'use';

GO

SQL Server DMVs and DMFs can be used in this way to answer several questions about your SPs, including the following:

• Which SPs are executed the most?
• Which SPs take the longest to execute?
• Which SPs perform the most logical reads and writes?

The answers to these types of questions can help you quickly locate performance bottlenecks and focus your performance-tuning efforts where they are most needed. We will discuss the performance tuning in detail in Chapter 19, Performance Monitoring and Tuning.

Parameter Sniffing

SQL Server uses a method known as parameter sniffing to further optimize SP calls. During compilation or recompilation of an SP, SQL Server captures the parameters used and passes the values along to the optimizer. The optimizer then generates and caches a query plan optimized for those parameters. This can actually cause problems in some cases—for example, when your SP can return wildly varying numbers of rows based on the parameters passed in. Listing 5-17 shows a simple SP that retrieves all products from the Production.Product table with a Name like the @Prefix parameter passed into the SP.

Listing 5-17.  Simple Procedure to Demonstrate Parameter Sniffing

CREATE  PROCEDURE  Production.GetProductsByName
    @Prefix NVARCHAR(100)
AS
BEGIN
    SELECT
        p.Name,
        p.ProductID
    FROM  Production.Product  p
    WHERE p.Name LIKE @Prefix;
END;
GO

Calling this SP with the @Prefix parameter set to % results in a query plan optimized to return 504 rows of data with a nonclustered index scan, as shown in Figure 5-11.

If you run the Production.GetProductsByName procedure a second time with the @Prefix parameter set to M%, the query plan will show that the plan is still optimized to return 504 estimated rows, although only 102 rows are actually returned by the SP. Figure 5-12 shows the query plan for the second procedure call.

In cases where you expect widely varying numbers of rows to be returned by your SPs, you can override parameter sniffing on a per-procedure basis. Overriding parameter sniffing is simple—just declare a local variable in your SP, assign the parameter value to the variable, and use the variable in place of the parameter in your query. When you override parameter sniffing, SQL Server uses the source table data distribution statistics to estimate the number of rows to return. The theory is that the estimate will be better for a wider variety of possible parameter values. In this case, the estimate will still be considerably off for the extreme case of the 504 rows returned in this example, but it will be much closer and will therefore generate better query plans for other possible parameter values. Listing 5-18 alters the SP in Listing 5-17 to override parameter sniffing. Figure 5-13 shows the results of calling the updated SP with a @Prefix parameter of M%.

Listing 5-18.  Overriding Parameter Sniffing in an SP

ALTER PROCEDURE Production.GetProductsByName
@Prefix NVARCHAR(100)
AS
BEGIN
DECLARE @PrefixVar NVARCHAR(100) = @Prefix;
    SELECT
        p.Name,
        p.ProductID
    FROM  Production.Product  p
    WHERE p.Name LIKE @PrefixVar;
END;
GO

With parameter sniffing overridden, the query plan for the SP in Listing 5-18 uses the same estimated number of rows, in this case 27.0914, no matter what value you pass in the @Prefix parameter. This results in a query plan that uses a nonclustered index seek—not an index scan—which is a much better query plan for the vast majority of possible parameter values for this particular SP.

Recompilation

As we discussed previously in this chapter, SQL Server optimizes performance by caching compiled query plans while it can. The recompilation of stored procedures is performed on individual statements within stored procedures rather than entire stored procedures to avoid unnecessary recompile consuming CPU resources.

There are several reasons why the stored procedures are recompiled:

• If the object is modified between executions, each statement within the SP that references this object is recompiled.
• If sufficient data has changed in the table that is being referenced by the SP since the original query plan was generated, the SP will recompile the plan.
• Use of temporary table in the SP may cause the SP to be recompiled evey time the procedure is executed.
• If the SP was created with the recompile option, this may cause the SP to be recompiles every time the procedure is executed.

Caching the query plan eliminates the overhead associated with recompiling your query on subsequent runs, but occasionally this feature can cause performance to suffer. When you expect your SP to return widely varying numbers of rows in the result set with each call, the cached query execution plan will only be optimized for the first call. It won’t be optimized for subsequent executions. In cases like this, you may decide to force recompilation with each call. Consider Listing 5-19, which is an SP that returns order header information for a given salesperson.

Listing 5-19.  SP to Retrieve Orders by Salesperson

CREATE  PROCEDURE  Sales.GetSalesBySalesPerson  (@SalesPersonId  int)
AS
BEGIN
    SELECT
        soh.SalesOrderID,
        soh.OrderDate,
        soh.TotalDue
    FROM  Sales.SalesOrderHeader  soh
    WHERE soh.SalesPersonID = @SalesPersonId;
END;
GO

There happens to be a nonclustered index on the SalesPersonID column of the Sales.SalesOrderHeader table, which you might expect to be considered by the optimizer. However, when this SP is executed with the EXECUTE statement in Listing 5-20, the optimizer ignores the nonclustered index, and instead performs a clustered index scan, as shown in Figure 5-14.

Listing 5-20.  Retrieving Sales for Salesperson 277

EXECUTE Sales.GetSalesBySalesPerson 277;

The reason the SP ignores the nonclustered index on the SalesPersonID column is because 473 matching rows are returned by the query in the procedure. SQL Server uses a measure called selectivity, the ratio of qualifying rows to the total number of rows in the table, as a factor in determining which index, if any, to use. In Listing 5-20, the parameter value 277 represents low selectivity, meaning that there are a large number of rows returned relative to the number of rows in the table. SQL Server favors indexes for highly selective queries, to the point of completely ignoring indexes when the query has low selectivity.

If you subsequently call the SP with the @SalesPersonId parameter set to 285, which represents a highly selective value (only 16 rows are returned), query plan caching forces the same clustered index scan, even though it’s suboptimal for a highly selective query. Fortunately, SQL Server provides options that allow you to force recompilation at the SP level or the statement level. You can force a recompilation in your SP call by adding the WITH RECOMPILE option to your EXECUTE statement, as shown in Listing 5-21.

Listing 5-21.  Executing an SP with Recompilation

EXECUTE Sales.GetSalesBySalesPerson 285 WITH RECOMPILE;

The WITH RECOMPILE option of the EXECUTE statement forces a recompilation of your SP when you execute it. This option is useful if your data has significantly changed since the last SP recompilation or if the parameter value you’re passing to the procedure represents an atypical value. The query plan for this SP call with the highly selective value 285 is shown in Figure 5-15.

You can also use the sp_recompile system SP to force an SP to recompile the next time it is run.

If you expect that the values submitted to your SP will vary a lot, and that the “one execution plan for all parameters” model will cause poor performance, you can specify statement-level recompilation by adding OPTION (RECOMPILE) to your statements. The statement-level recompilation also considers the values of local variables during the recompilation process. Listing 5-22 alters the SP created in Listing 5-20 to add statement-level recompilation to the SELECT query.

Listing 5-22.  Adding Statement-Level Recompilation to the SP

ALTER  PROCEDURE  Sales.GetSalesBySalesPerson  (@SalesPersonId  int)
AS
BEGIN
    SELECT
        soh.SalesOrderID,
        soh.OrderDate,
        soh.TotalDue
    FROM  Sales.SalesOrderHeader  soh
    WHERE soh.SalesPersonID = @SalesPersonId
    OPTION (RECOMPILE);
END;
GO

As an alternative, you can specify procedure-level recompilation by adding the WITH RECOMPILE option to your CREATE PROCEDURE statement. This option is useful if you don’t want SQL Server to cache the query plan for the SP. With this option in place, SQL Server recompiles the entire SP every time you run it. This can be useful for procedures containing several statements that need to be recompiled often. Keep in mind, however, that this option is less efficient than a statement-level recompile since the entire SP needs to be recompiled. Because it is less efficient than statement-level recompilation, this option should be used with care.

To extend on the Stored Procedure Statistics section, SQL Server 2012 provides details about the last time when the SP or the statements were recompiled with DMVs. This will help you identify the most recompiled stored procedures and allow you to focus on resolving the recompilation issues. Listing 5-23 is a procedure that returns the stored procedures that have been recompiled.

Listing 5-23.  SP to Retutn List of Stored Procedures That Have Been Recompiled

CREATE  PROCEDURE  dbo.GetRecompiledProcs
AS
BEGIN
    SELECT
        sql_text.text,
        stats.sql_handle,
        stats.plan_generation_num,
        stats.creation_time,
        stats.execution_count,
        sql_text.dbid,
        sql_text.objectid
    FROM sys.dm_exec_query_stats stats
        Cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
    WHERE stats.plan_generation_num > 1
        and sql_text.objectid is not null --Filter adhoc queries
    ORDER BY stats.plan_generation_num desc
END;
GO

This procedure uses the sys.dm_exec_query_stats DMV with the sys.dm_exec_sql_text DMF to retrieve relevant SP execution information. The query returns only the stored procedures that have been recompiled by filtering the plan_generation_num, and the ad hoc queries are being filered out by removing the object_id with null values.

Listing 5-24 demonstrates how to call this SP, and partial results are shown in Figure 5-16.

Listing 5-24.  Retrieving SP Statistics

EXEC dbo.GetRecompiledProcs;

GO