SQL SERVER – Recompile Stored Procedure at Run Time

I recently received an email from reader after reading my previous article on SQL SERVER – Plan Recompilation and Reduce Recompilation – Performance Tuning regarding how to recompile any stored procedure at run time. There are multiple ways to do this. If you want your stored procedure to always recompile at run time, you can add the keyword RECOMPILE when you create the stored procedure. Additionally, if the stored procedure has to be recompiled at only one time, in that case, you can add RECOMPILE word one time only and run the SP as well. Let us go over these two options.

The RECOMPILE hint is used with a query and recompiles only that particular query. However, if the parameters are used in many statements in the stored procedure and we want to recompile all the statements, then instead of using the RECOMPILE option with all the queries, we have one better option that uses WITH RECOMPILE during stored procedure creation or execution.

Option 1:

CREATE PROCEDURE dbo.PersonAge (@MinAge INT, @MaxAge INT)
FROM dbo.tblPerson
WHERE Age >= @MinAge AND Age <= @MaxAge

Option 2:

EXEC dbo.PersonAge 65,70 WITH RECOMPILE

This method is not recommended for large stored procedures because the recompilation of so many statements may outweigh the benefit of a better execution plan.

Note: Recompilation is not necessarily the best solution.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Plan Recompilation and Reduce Recompilation – Performance Tuning

Recompilation process is same as compilation and degrades server performance. In SQL Server 2000 and earlier versions, this was a serious issue but in SQL server 2005, the severity of this issue has been significantly reduced by introducing a new feature called Statement-level recompilation. When SQL Server 2005 recompiles stored procedures, only the statement that causes recompilation is compiled, rather than the entire procedure. Recompilation occurs because of following reason:

  • On schema change of objects.
    • Adding or dropping column to/from a table or view
    • Adding or dropping constraints, defaults, or rules to or from a table.
    • Adding or dropping an index to a table or indexed view if index is used by the plan.
    • Adding or dropping trigger from a table
    • Dropping statistics from a table that is used in plan.
  • On change of the SET options: When a compiled plan is created, the SQL server also stores the environmental setting of a connection (SET option) with it. If the same stored procedure is executed by another connection that has a different SET option, then the existing cached plan is not reused. To reduce recompilation caused by Environment change, we should not change the SET options in the connection setting and stored procedure.
  • On statistics change of tables: Every time the SQL Server uses an already cached compiled plan, it checks the optimality before using it. SQL Server decides whether the plan is optimal for current amount of data in underlying tables. It could be that the data amount in underlying tables changed so much that the previously created plan is not optimized. For each table & index, the SQL server maintains a modification counter and if the counter values exceed the defined threshold, the previously created compiled plan is considered stale plan and a new plan is created.

Detecting recompilations: The below query retrieves the top 10 statements for which the recompilation count is maximum. Here, plan_generation_num returns a number that indicates the recompilation count of a statement.

DB_NAME(st.dbid) AS DbName,
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
ORDER BY plan_generation_num DESC

Another tool is to get recompilation details is the SQL Profiler. Along with the statements text, it also tells the reason of recompilation in EventClass and EventSubClass columns.

Reducing recompilations: We have no option to avoid “schema change” and “SET options” based recompilation. But we have following query and stored procedure hints to avoid “statistics change” based recompilation:

  • KEEP PLAN hint: The modification counter threshold for a temporary table is 6. This implies that when a stored procedure that creates a temporary table inserts 6 or more rows into this table, Stored Procedures will be recompiled as soon as this table is accessed. For permanent tables, this threshold is at least 500. We can increase the first threshold for the temporary table (6) to same as that of the permanent table (500) by using the KEEP PLAN query hint in the statement where the temporary table is used. For example,

SELECT TT.col4, SUM(PermTable.col1)
FROM dbo.PermTable
ON PermTable.col1 = TT.col2

  • KEEPFIXED PLAN hint: This hint completely avoids “statistics change” based recompilation of a query.

SELECT col1, col2
FROM dbo.PermTable
WHERE col3 &lt; 100

  • Use Table variable instead of temporary tables: Because a change in cardinality of a table variable does not cause recompilations, consider using a table variable instead of a temporary table when faced with a problem of excessive recompilation. However, the use of table variables can lead to poorer query plans. Distribution statistics are not stored for table variables, and cardinality is only available during recompilation and not during initial compilation. One has to check whether this is the case and make an appropriate trade-off.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Stored Procedure Optimization Tips – Best Practices

We will go over how to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips, which we will cover in future articles.

  • Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.

CREATE PROC dbo.ProcName
--Procedure code here
SELECT column1 FROM dbo.TblTable1

  • Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like

SELECT * FROM dbo.MyTable -- Preferred method
-- Instead of
SELECT * FROM MyTable -- Avoid this method
--And finally call the stored procedure with qualified name like:
EXEC dbo.MyProc -- Preferred method
--Instead of
EXEC MyProc -- Avoid this method

  • Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
  • Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:

WHERE name = 'MyTable' AND type = 'U')

  • Use the sp_executesql stored procedure instead of the EXECUTE statement.
    The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:

@Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

  • Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
  • Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
  • Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:

--Your t-sql code goes here
--Your error handling code goes here

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Size of Index Table – A Puzzle to Find Index Size for Each Index on Table

It is very easy to find out some basic details of any table using the following Stored Procedure.

USE AdventureWorks
EXEC sp_spaceused [HumanResources.Shift]

Above query will return following resultset

The above SP provides basic details such as rows, data size in table, and Index size of all the indexes on the table.

If we look at this carefully, a total of three indexes can be found on the table HumanResources.Shift.

USE AdventureWorks
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('HumanResources.Shift')

The above query will give result with query listing all the index on the table.

There is a small puzzle for all of you here. The puzzle is to write a query that will return the size for each index that is listed in above query. We need a query that will return an additional column in the above listed query and it should contain the size of the index. In our case, we will have three different sizes, which should add up to a total of 40 KB as shown in earlier query, where the total size is displayed.

I will publish the solution with due credit on this blog.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Stored Procedure are Compiled on First Run – SP taking Longer to Run First Time

During the PASS summit, one of the attendees asked me the following question.

Why the Stored Procedure takes long time to run for first time?

The reason for the same is because Stored Procedures are compiled when it runs first time. When I answered the same, he replied that Stored Procedures are pre-compiled, and this should not be the case. In fact, Stored Procedures are not pre-compiled; they compile only during their first time execution.

There is a misconception that stored procedures are pre-compiled. They are not pre-compiled, but compiled only during the first run. For every subsequent runs, it is for sure pre-compiled.

If you create any SP, you will find that there is no cache entry for the execution of that SP.

After running the SP for the first time, the entry for the cache is made in the system.

If we see the following script, we can notice the different of cache when SP was created and SP was executed.

/* Exeercise to verify if stored procedure pre-compiled */
USE AdventureWorks
-- Clean Cache
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[CompSP]') AND type IN (N'P', N'PC'))
-- Create New Stored Procedure
FROM HumanResources.Department
-- Check the Query Plan for SQL Batch
-- You will find that there is no ObjectName with CompSP
SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.TEXT AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;
/* Execute Stored Procedure */
-- Check the Query Plan for SQL Batch
-- You will find that there is one entry with name ObjectName with name CompSP
SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.TEXT AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;

The result set of above query is as following.

The above script to find out the cache is taken from the white paper SQL SERVER – Plan Caching in SQL Server 2008 by Greg Low. You can also read my follow up article SQL SERVER – Plan Caching and Schema Change – An Interesting Observation, where I have given an interesting conversation with Greg Low.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Insert Values of Stored Procedure in Table – Use Table Valued Function

I recently got many emails requesting to write a simple article.  I also got a request to explain different ways to insert the values from a stored procedure into a table. Let us quickly look at the conventional way of doing the same.

Please note that this only works with the stored procedure with only one resultset. Let us create a stored procedure that returns one resultset.

/* Create Stored Procedure */
GETDATE() AS MyDate, 1 AS IntValue
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue

Traditional Method:

/* Create TempTable */
/* Run SP and Insert Value in TempTable */
INSERT INTO #tempTable (MyDate, IntValue)
/* SELECT from TempTable */
FROM #tempTable
/* Clean up */
DROP TABLE #tempTable

Alternate Method: Table Valued Function

/* Create table valued function*/
IntValue INT
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
/* Select data from Table Valued Function */
FROM dbo.TestFn()

It is clear from the resultset that option 2, where I have converted stored procedures logic into the table valued function, is much better in terms of logic as it saves a large number of operations. However, this option should be used carefully. Performance of the stored procedure is “usually” better than that of functions.

We will discuss in another post regarding the type of stored procedure that can be converted into a table valued function. Let me know what you all think about this post.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Converting Stored Procedure into Table Valued Function

In one of my recent articles, I mentioned the use of Table Valued Function (TVF) instead of Stored Procedure (SP). I received a follow up email asking what type of SP can be converted into a TVF. This is indeed a very interesting question! In fact, not all the SPs qualify to be converted to a TVF.

Please note that I am not encouraging to convert all the SPs to TVFs. Each SPs have their own usage and need. Here, I shall discuss about the type of SP that can be converted to a TVF.

First of all, you need to convert the SP that you are using to retrieve data and to insert it into any other table. Please read my original post wherein I have explained the scenario for the same. If you do not have to insert the data from the SP into another table, then there is no need to convert the SP to a TVF. This conversion will not necessarily enhance the performance.

In general, SP that returns only one resultset qualifies for conversion to TVF. If there are more than one resultset, then the SP cannot be converted to TVF as the TVF always returns one resultset. Moreover, if the SP has DML statements such as DELETE, UPDATE, INSERT or MERGE, it cannot be converted to a TVF. The usage of TVF is limited to the SELECT statement only. If the SP must be executed by using EXEC, it cannot be converted to TVF.

Do you know any other reason why a SP needs to be converted to a TVF?

Reference: Pinal Dave (http://blog.sqlauthority.com)