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
AS
SET
NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

  • 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:

IF EXISTS (SELECT 1 FROM sysobjects
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:

DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET
@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,

DECLARE @Query NVARCHAR(100)
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:

BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH

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
GO
EXEC sp_spaceused [HumanResources.Shift]
GO

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
GO
SELECT *
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('HumanResources.Shift')
GO

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
GO
-- Clean Cache
DBCC FREEPROCCACHE
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[CompSP]') AND type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[CompSP]
GO
-- Create New Stored Procedure
CREATE PROCEDURE CompSP
AS
SELECT
*
FROM HumanResources.Department
GO
-- 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;
GO
/* Execute Stored Procedure */
EXEC CompSP
GO
-- 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;
GO

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 */
CREATE PROCEDURE TestSP
AS
SELECT
GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
GO

Traditional Method:

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

Alternate Method: Table Valued Function

/* Create table valued function*/
CREATE FUNCTION dbo.TestFn()
RETURNS @retTestFn TABLE
(
MyDate SMALLDATETIME,
IntValue INT
)
AS
BEGIN
DECLARE
@MyDate SMALLDATETIME
DECLARE @IntValue INT
INSERT INTO
@retTestFn
SELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
RETURN;
END
GO
/* Select data from Table Valued Function */
SELECT *
FROM dbo.TestFn()
GO

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)

SQL SERVER – Plan Caching and Schema Change – An Interesting Observation

Last week, I had published details regarding SQL SERVER – Plan Caching in SQL Server 2008 by Greg Low on this blog. Similar to any other white paper, I have read this paper very carefully and enjoyed reading it. One particular topic in the white paper that caught my attention is definition of schema change. I was well aware of this definition, but I have often found that users are not familiar with what exactly does a schema change mean.

Many people assume that a change in the table structure is schema change. In fact, creating or dropping index on any table also qualifies as schema change.

As per the white paper, “Schema change” is defined as follows:

  • Adding or dropping columns to a table or view.
  • Adding or dropping constraints, defaults, or rules to/from a table.
  • Adding an index to a table or an indexed view.
  • Dropping an index defined on a table or an indexed view (only if the index is used by the query plan in question).
  • Dropping a statistic (not creating or updating!) defined on a table will cause a correctness-related recompilation of any query plans that use that table. Such recompilations occur at that instant when the query plan in question begins execution. Updating a statistic (both manual and auto-update) will cause an optimality-related (data related) recompilation of any query plans that uses this statistic.

As per the above definition, I decided to experiment with schema change. In white paper, it was mentioned that schema change will recompile the execution batch. I decided to test this out. I created a small script (given below) and decided to test it.

As per the script, I created a large table and stored the procedure that was accessing that table. I created another object that was an index to the table; when SP was ran with index on table, it showed a plan different from that without the index on table. It was evident that the batch was recompiled when the index on the table was modified.

The script is available here to download.

Without Index execution plan:

With Index execution plan:

It is clear from the execution plan that it is different, and this proves the point that schema change recompiles the execution plan.

There is one more script provided in the white paper, which gets data from DMV related to cached plan. When I ran the following SP, it did not give me very interesting results.

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;


When I ran the above script, I found that the UseCount column did not reset when QueryPlan is changed. This was very surprising as I was expecting UseCount to reset on changing the query plan. I sent an email to Dr. Greg Low, who is author of this white paper. He was very kind to respond back within minutes with a proper answer, which is as follows:

You’ll notice in Profiler when you run the sproc after creating the index that it does in fact cause a recompile. However, in SQL Server 2008 (and 2005), these are statement level recompiles not proc level recompiles. I imagine that’s why they’re not reflected in the UseCount values ie: the plan itself isn’t recompiled and that’s what the counts are based on.

Well, this simple explanation clarified my doubts. Let me know what you think, and I strongly suggest you all to read the white paper written by Dr. Greg Low.

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

SQL SERVER – Get a List of Fixed Hard Drive and Free Space on Server

When I am not blogging, I am typically working on SQL Server Optimization projects. Time and again, I only have access to SQL Server Management Studio that I can remotely connect to server but do not have access to Operating System, and it works just fine.

At one point in optimization project, I have to decide on index filegroup placement as well TempDB files (.ldf and .mdf) placement. It is commonly known that system gives enhanced performance when index and tempdb are on separate drives than where the main database is placed.

As I do not have access to OS I use the following Stored Procedure to figure out the number of fixed drives (hard drive) a system has along with free space on each of those drives. Based on this result, I decide my strategy of where to put my indexes and tempdb.

EXEC master..xp_fixeddrives

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

SQL SERVER – List All Missing Identity Values of Table in Database

The best part of any blog is when readers ask each other questions. Better still, is when a reader takes the time to provide a detailed response.

A few days ago, one of my readers, Yasmin, asked a very interesting question:

How we can find the list of tables whose identity was missed (not is sequential order) within the entire database?

A big thank you to SQL Server Expert, Imran Mohammed, for his excellent response to this question. He also provided an extremely impressive script, which is well described and contains inline comments.

This script is well worth keeping in your archive for future reference.

Click to Download Scripts

If you and use the script, please comment here and let me know about your experience.

If you know any other way that this can be accomplished or have a different solution, please post your comment here, or send me an email and I will publish it with due credit.

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

SQL SERVER – List All the Tables for All Databases Using System Tables

Today we will go over very simple script which will list all the tables for all the database.

sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'

Update: Based on comments received below I have updated this article. Thank you to all the readers. This is good example where something small like this have good participation from readers.

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

SQL SERVER – 2008 – Optimize for Ad hoc Workloads – Advance Performance Optimization

Every batch (T-SQL, SP etc) when ran creates execution plan which is stored in system for re-use. Due to this reason large number of query plans are stored in system. However, there are plenty of plans which are only used once and have never re-used again. One time ran batch plans wastes memory and resources.

SQL Server 2008 has feature of optimizing ad hoc workloads. Before we move to it, let us understand the behavior of SQL Server without optimizing ad hoc workload.

Please run following script for testing. Make sure to not to run whole batch together. Just run each command separately to really see the effect of subject of article.

Download complete script of this article here.

/* Test 0 */
/* Clean Cache and Buffers */
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
USE AdventureWorks
GO
/* Run Adhoc Query First Time */
SELECT * FROM HumanResources.Shift
GO
/* Check if Adhoc query is cached.
It will return one result */
SELECT usecounts, cacheobjtype, objtype, TEXT
FROM
sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 0 AND
TEXT LIKE '%SELECT * FROM HumanResources.Shift%'
ORDER BY usecounts DESC;
GO

Now let us check result of this script. It is clear from result that when we ran query once it cached its plan in memory. If we never run this again in future or if we have just ran as part of building longer query the cache plan of this query is just waste of memory.

Let us now enable the option of optimizing ad hoc workload. This feature is available in all the versions of SQL Server 2008.

sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO

sp_CONFIGURE ‘optimize for ad hoc workloads’,1
RECONFIGURE
GO

We will now run the code for Test 1 which is almost same as Test 0. Make sure to clean the cache and buffer before running the query to create real life scenario of live case.

/* Test 1 */
/* Clean Cache and Buffers */
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
USE AdventureWorks
GO
/* Run Adhoc Query First Time */
SELECT * FROM HumanResources.Shift
GO
/* Check if Adhoc query is cached.
It will not return any result */
SELECT usecounts, cacheobjtype, objtype, TEXT
FROM
sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 0 AND
TEXT LIKE 'SELECT * FROM HumanResources.Shift%'
ORDER BY usecounts DESC;
GO

We can clear see now as we have advance option enabled we do not have query cache planed stored in database.

We are interested to know now that if we run the batch more than 1 time it will cache its execution plan.  With advance option of optimizing ad hoc workload.

/* Test 2 */
/* Clean Cache and Buffers */
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
/* Run Adhoc Query two  Time */
SELECT * FROM HumanResources.Shift
GO 5
/* Check if Adhoc query is cached.
It will return result with Adhoc Query ran two times*/
SELECT usecounts, cacheobjtype, objtype, TEXT
FROM
sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 0 AND
TEXT LIKE '%SELECT * FROM HumanResources.Shift%'
ORDER BY usecounts DESC;
GO

From our image it is quite clear that when the batch is ran for more than 1 time it caches its execution plan. This is generic behavior with or without turning on advance option.

This may be very simple to see from the top but if you are using SQL Server 2008 and have millions of ad hoc query running every day you wil realize how important this feature is. This feature improves performance by relieving memory pressure by not storing the single time used compiled plans.

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