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.

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

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

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,

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:

Though I would expect a few people to come up with the ‘it depends’ and ‘not always’ arguments, this is certainly a helpful set of tips/best-practices that every TSQL developer should add to his/her check list.

ugh..I have a complex stor proc that I could so use all of your help on. It runs fine but takes over 8 hours to run! Any suggestions would be so greatly appreciated.
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

/*
IF OBJECT_ID(‘tempdb..#tempAgingRecords’) IS NOT NULL
drop table #tempAgingRecords
*/

set @posQty = @posQty + @negQty
set @posInventoryAmount = @posInventoryAmount + @negInventoryAmount
end
else
–If the negative quantity has left to consume, get another positive record
while @negQty < 0
begin
–If there are no more positive record (still negatives left), then insert and end
if (select isnull(sum(posProcessed), 0) from @posTable) = 0
begin
insert into work_invent_trans_aging
values (@negRecid, @posRecid, @itemid, @dataareaid, @negDatestatus, '1/1/1900', @negQty, 'New2', @negInventoryAmount)

Knowing that it is there to use, you could always take a look in Books Online or search elsewhere for in depth examples. There are lots of examples and trying them out will help you understand how it works more clearly. This set of tips doesnt go in to depth on any of the items as they each could have an article on their own.

If i am using temporary tables, local and global in some of my store procedures, should i use indexes (clustered & non-clustered) on these tables to get maximum performance, specially when i know that these temporary tables will contain lot of rows ???

Creating an index to be used for once would not provide any improvement because processing consumed to create an index itself would overcome the benifit of its uses. We should create an index on any table (permanent or temporary table) if that is usable in more than one statements.

Like SQL Genius (Pinal) said, if we use temp tables more than once in stored procedure, then definitely it is suggested to create indexes on temporary tables especially when you are dealing with huge amounts of data.

I have seen significant differences in my procedure performance after I created Indexes on huge temp tables.

Sorry, but I disagree with your statement about there is no sense for creating an index on # table if this index will be used just once. Many times in my practice “creation index on the fly” saved tens of minutes, sometimes hours on data processing. It is all about data amount. Sometimes you can’t avoid of using a # table and that table becomes huge in some cases. When we talking about million records temporary table and we have to join this table with another multimillion table we have to create indexes otherwise we can wait forever till procedure is completed.
When we have deal with VLDB, the creation of index(es) on # table on the fly is definitely solution.

Interesting that you wrote this article today. Along with my other responsibilities, I am also part of the DBA team and was working on a code review today and recommended the usage of schema qualifiers (dbo.MyStoredProcedure) for the stored procedures. I had logically thought it through and was going to do a little experiment tomorrow to see the performance difference that qualifers provide. I will run the test tomorrow and update if time permits.

Great compilation, by the way. I agree with Jacob – this should be part of every database developer’s/administrator’s checklist.

Well, I would think that in the case of EXISTS the subquery is terminated when first matching row is found and only boolean value is returned. This would seem logical.

But since MSDN doesn’t say anything about this I think the safest way would be to use:

EXISTS (SELECT TOP(1) 1 FROM foo WHERE bar = ..)

This is because if subquery isn’t terminated it would do the matching against all the rows in ‘foo’ and return 0..n rows. This means that there would be 0..n unneccessary matching operations and 0..n * column_count data readings.

With TOP(1) you tell that you are only insterested in the first matching row and no subsequent rows are matched. And because you are returning constant value 1 there will be no unnecessary read operation to the table.

INDEX usage would not be based on the *, but on the WHERE clause. Think about it, EXISTS causes a lookup which we are hoping to be an INDEX scan of sorts. The match is based on the WHERE clause where the correlation is defined.

I have seen notable peformance improvements by using SELECT 1 instead of SELECT *. Looking at the Execution Plan to address to performance, I could see that SELECT * was having to bring back all fields using the clustered index. When SELECT 1 was used instead, a covering index based on the WHERE clause was used by the engine and performance was notably better. As in a query taking 4 secs now takes 0.15 secs.

Also, from a developers point of view, SELECT 1 is preferred because it is symantically stating that the fields are not important to the query.

A couple of things that every developer should realize is that before directly jumping to tune your stored procedures, make sure you have checked your table size, schema, index information etc etc. Most of the times, the ’cause’ of slow performing queries is bad design.

A stored procedure with prefix “sp_” whether in dbo schema or user defined schema, whether fully qualified name or non-qualified name, always first checked in master database. And if your stored procedure is in another database then an extra search would be an overhead every time you call that stored procedure.

“I not think so because there is no significant difference in transferring of 1 byte and a null value.”

While that is true in this exact case I like to be pedantic about this and elaborate this a bit and probably confuse everybody up :)

There is no concept NULL value if you think about C code or even the CPU. Pointers in C can have NULL values but that just means that the pointer is pointing to memory in the address 0x00000000 (in 32-bit machine). The actual pointer is still taking sizeof(VOID*) amount of memory even when it points to NULL.

Now if you think about functions in C or any other language they always reserve space in the function stack for the return value. You can say, again in C, that you don’t care about the return value and declare the function to return “void” but still it takes 4 bytes of memory in 32-bit machine for return value.

And actually, of you think about the CPU, there is 32 bit register reserved exactly for this.

And now considering that it makes no difference to return one byte or four bytes since there is space for four bytes anyway. And in fact, many times (all the times?) when you deal with C data types CHAR (1 byte) or SHORT (2 bytes) you end up taking 4 bytes because of the padding to keep memory aligned.

Uh! I almost felt like going back in time some ten years when I was writing kernel drivers for Windows :)

after testing several queries using both IF EXISTS(SELECT * AND SELECT 1, in none of my tests did the SELECT 1 statement outperform the SELECT * statement. In all tests the IF EXISTS statement always only returned 1 row, the I/O Cost and CPU Cost were identical whichever statement was used, however my tables were relatively small with less than 10000 records in each.

I am searching for a stored procedure which will eliminate all the STOP words like “in”, “the” in my query and accordingly search the result.

Is it correct :-
//This is my stored procedure
CREATE PROCEDURE sp_GetInventory
@location varchar(50) AS
select column1, column2 from table1
where column1 like ‘%SearchString%’ or column2 like ‘%SearchString%’

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.

I created two stored procedures with the same name in “Master” and “Another new DataBase”, and I executed it from the new one.
The result returned from the new one.
This mean that, it didn’t execute the one in the Master, although there’s a SP in the Mater with the same name.
Can you please elaborate this.

This is a killer post since optimizing database code or SQL statements is a killer job.
However the hints provided here are very useful. Simple optimization hints, that anyone can use, and more advanced but still easy to understand.
About the SELECT 1 versus the SELECT * i think that the first should be preferred over the last.
A few years ago i came across a few optimization tips for Oracle (my previous professional background) and this tip was already mentioned. But later i read that Oracle internally optimizes EXIST (SELECT * FROM …). Perhaps the same approach is used inside SQL Server and that’s why the difference isn’t noticed.
The reason stated for using SELECT ‘ANY_CONSTANT’ over SELECT * is that * would read every column, so it would be more efficient to return a constant. The gains would be obtained in the reduced reading of data.
Regards.

Could you please tell some what in detail about performance tuning, may be with an example like – If a query takes more time what are all the causes for that or if a stored procedure takes more time what are all causes for that so that many DBA’s as like me who are all in intermediate stage would gain a lot . Thanks!

Hi sir,
I am fresher and i am working in welcomenetwork company
i have string like ‘0012340056789’
but i want only the string like ‘12340056789’
that is starting 0 should be removed so how can i
do in sql server function
Reply
Thanks & Regards,
Dipak B. Kansara

Sir,
Thanks for the information or tips you provide for optimazation in SQL. I have been working on an application which uses XML file and database as well. We read XML files and extract required information and update that information in database. We have approx 18 stored procedure which updates 7-8 tables for a single XML file.
And we have about 22500 Xml files. It takes about 3 hours on local computer and if we update remote server using this application it takes about 10 hours to run completely. Please tell me more about optimization in SQL stored procedures.

> The reuse of the existing complied plan will result in >improved performance.
It is true for sp_executesql if we have uniform distribution of data. In other case we will have degradation of performance. You can save a bit time on reuse existing plan but you can lose a lot time when you reuse bad plan. So in my opinion if you have big table with non-uniform distribution of data reusing of plan is bad idea

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

Hi Dave,
As you said
“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.”

– so i have created the stored procedure sp_test in my own database and same in master database.

i have excuted the stored procedure from my local database and i found that local stored procedure is getting executed all time.

I have a typical situation. I have two tables, 1. user 2.address. each user contains more than 2 or 3 address. I have to update user table with all address ids and count concatenated string in a field.

ex: user.address = 4;addid1;sddid2;addid3.

I have used cursor, while loop and temp table to update. in all cases it is taking more than 24 hours. for production we don’t have that much time. I have to complete in 4 to 6 hours.

But as you mentioned there in your article-
“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
—————————————————————————————————
we could face problem when we have more than one database position like a test database server and a live database and database admin are different or database admin can be changed time to time, in that case to maintain the synchronization we need to change the query regarding database admin, which is quite impossible for a developer.

For this situation to work, as Pinal is suggesting, you will have to take note of the time taken before the cached plan is found. Having the qualified schema name reduces the time taken to search for the correct plan.

Obviously we’re splitting hairs here, but in the world of DBA’s that’s often what we do if there’s a requirement to attain the fastest possible response.
Dallas

OPEN Cur_AB1
FETCH NEXT FROM Cur_AB1 INTO @ProductionTracingUnitID,@OrderID,@OrderType,@ProductID,@DyeingOrderQty,@JobOrderQty,@ProductionPipeLineQty,@ProductionFinishedQty,@BuyerID,@FactoryID,@ProductionGraceQty,@WeightLossGainQty,@RateInLBS,@ProductionLossQty,@ActualDeliveryQty,@ReadyStockInhand,@ColorName,@LabLabDipID,@ReturnQty,@EWYDLRelabNo,@EWYDLColorNo,@ShadeFromOrder
WHILE(@@Fetch_Status -1)
BEGIN–2
SET @LCID=0
SET @LCNo=”
SET @PIQty=0
SET @AcceptanceValue =0
SET @MaturityValue= 0
SET @Acceptance= ”
SET @Maturity =”

SET @DeliveryQty=@ActualDeliveryQty-@ReturnQty
SET @YetToDelivery=@JobOrderQty-@ActualDeliveryQty+@ReturnQty
set @PTUDQty=(select sum(Qty) from PTUDistribution where ProductionTracingUnitID=@ProductionTracingUnitID )
IF(@PTUDQty>@YetToDelivery)
BEGIN–sih
SET @StockInHand =@YetToDelivery
END –sih
ELSE
BEGIN–sih2
SET @StockInHand =@PTUDQty
END –sih2
SET @YetToProduction=@JobOrderQty-@ReadyStockInhand-@ActualDeliveryQty+@ReturnQty
IF (@YetToProduction0 and @ExportLCValue is not null)
BEGIN
SET @BillAcceptancePercentage =(@BillAcceptanceValue*100)/@ExportLCValue — bill Percentage
SET @BillMaturityPercentage =(@BillMaturityValue*100)/@ExportLCValue

IF((@ChangingQty*@RateInLBS)>0 and (@ChangingQty*@RateInLBS) is not null)
BEGIN
SET @AcceptanceValue=(@AcceptanceValue*100)/(@ChangingQty*@RateInLBS)– PI ValuePercentage
SET @MaturityValue=(@MaturityValue*100)/(@ChangingQty*@RateInLBS)
END

SET @Acceptance=Convert(varchar(20),(CONVERT(float,round((@AcceptanceValue+@MaturityValue),0)))) +’%’
SET @Maturity =Convert(varchar(20),(CONVERT(float,round(@MaturityValue,0)))) +’%’
END

SET @FactoryName=”
IF(@FactoryID>0)
BEGIN–jam3
SET @FactoryName=(SELECT [Name] FROM Contractor WHERE ContractorID=@FactoryID)
END–jam3

SET @BuyerName=”
IF(@BuyerID>0)
BEGIN–jam4
SET @BuyerName=(SELECT [Name] FROM Contractor WHERE ContractorID=@BuyerID)
END–jam4

END–jam1
ELSE
BEGIN –jam2 IF Sample
SET @LCID=0
SET @LCNo=”
SET @OrderNumber=”
SET @PIQty=0
SET @DeliveryTo=0
SET @FactoryPersonnelID=0
SET @BuyerPersonnelID=0
SET @OrderRcvBy=0
SET @ChangingQty=0
SET @MerchandiserName=”
SET @MKTPersonName=”
SET @OrderState=0
SET @AmendmentStatus=0
SET @PIQty= (SELECT ISNULL(SUM(Qty),0) FROM SampleOrderDetail WHERE PTUID=@ProductionTracingUnitID)
SET @ChangingQty=@PIQty
SELECT @OrderNumber=ISNULL(SampleOrderNo,”), @DeliveryTo=ISNULL(DeliveryTo,0),@FactoryPersonnelID=ISNULL(FactoryPersonnelID,0),@BuyerPersonnelID=ISNULL(BuyerPersonnelID,0),@OrderRcvBy=ISNULL(OrderRcvBy,0),@OrderState=ISNULL(OrderState,0) FROM SampleOrder WHERE SampleOrderID=@OrderID
SET @AmendmentStatus=@OrderState
IF(@DeliveryTo=3)
BEGIN
SET @MerchandiserName=(SELECT ISNULL([Name],”) FROM ContactPersonnel WHERE ContactPersonnelID=@FactoryPersonnelID)
END

IF(@DeliveryTo=2)
BEGIN
SET @MerchandiserName=(SELECT ISNULL([Name],”) FROM ContactPersonnel WHERE ContactPersonnelID=@BuyerPersonnelID)
END
SET @MKTPersonName =(SELECT [Name] FROM Employee WHERE EmployeeID=@OrderRcvBy)

IF(@DeliveryTo=3)
BEGIN
SET @FactoryName=(SELECT [Name] FROM Contractor WHERE ContractorID=@FactoryID)
END

IF(@DeliveryTo=2)
BEGIN
SET @FactoryName=(SELECT [Name] FROM Contractor WHERE ContractorID=@BuyerID)
END

SET @BuyerName=”
IF(@BuyerID>0)
BEGIN–jam4
SET @BuyerName=(SELECT [Name] FROM Contractor WHERE ContractorID=@BuyerID)
END–jam4
END–jam2

Hi Pinal,
I am required to write stored procedure in sql server to get:
-the number of sessions on daily and hourly basis
-number of unique users
-Number of first time users.
i have a table with 100’s of rows & columns :
RequestID,ApplicationID,SessionID,UserId,UserName,RequestURL,RequestTimeStamp,InsertTimestampGMT,UpdateTimestampGMT,Idx
Please help!

Any problem if I use the prefix “sp” in the stored procedure name: If a stored procedure name begins with “SP,” will the SQL server first searches in the master database??? For example : spFact.StoredProcNameA and spRpt.StoredProcNameB

I have a stored proc which is about 2000 lines of code. It has several set of logic embedded into it.
My question is is it better to break this stored procs into different stored procs and run them as part of a stored proc.

Can i break this proc into 3 different stored procs of 500 lines of code each and run it the same proc. Hope my question is clear

just an example… I have a stored proc NewUser which lets say takes a new user. In the next steps of the same proc it adds the user to login table,notify’s the admin that there is a new user,and several other steps.

Can I break this adding user to login table… and notifying the admin.. into different stored procs but run them as different steps inside the NewUser stored proc.

Hi Pinal Dave sir, Its very pleasure in reading topics in your blog. I always got doubts how to check which query executes fast. I am biggest fan of the stored procedure. But when it comes to write complex logic, I usually have more than one way to get the result. So how can i check the performance. Could you suggest easy way. I don’t want use extra code for checking performance.

Pinal can we use select * statement in stored procedure?
and if we want all the field from the table by having only one inout parameter, then at the time of execution it is require to declear all the output attributes
or to declear only the input value inthe case if we use the select * query.
Please reply .
I am new for stored procedure.

I’m using cursor for looping so that I can massage the data record by record and also migrate the child data together.
From your article, you mention try to avoid using cursors, should replace with SET-based approach.
Can you please tell you should I use SET-based in below sample

This [dbo].[Transform_Account] will loop the data in Account_tbl table and called [dbo].[Transform_Account_Tel_Number] stored procedure.
In [dbo].[Transform_Account_Tel_Number], it will do another loop to migrate the records in [dbo].[Account_Tel_Number_tbl] records by records.

Can you please tell me how to use SET-based approach for this??

My current problem is, I have huge database to loop.
I have more than 2mils account in [dbo].[Account_tbl], each Account has about 6 child table to loop. Each of them also has more than 2mils to loop. It takes more than 10 hours to finish.
I’m look at how to make it fast.

I have a procedure which runs successfully 95% of the time. But times out some times. Getting the error ‘System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding’. I have set sql connection timeout to 2 mins and max pool size as 250 in web.config.

Store procedure taking 5 minutes to execute but if query of which i written procedure then it will take on 4 sec. I did google and find parameters sniffing for improving procedures performance so implemented that and now it still taking 3 minutes.
What should i do?

Hello Pinal Sir,
I have 2 Simple Query that i wants to optimize and made into Single.
I want to reduce the time( 2 Time Selection + 2 time Index Scanning ) that taken by both Update Query on Single Table.

Very helpful article, especially about not starting a stored proc’s name with ‘sp_’. Working at this for 12 years I hadn’t realized that.
By the way, as other’s noted, I found a tremendous performance boost after adding temp table indexing even though the proc used the table only once, due to it being joined to other tables.
Didn’t always prove worth it, I supposed if the join wasn’t a big one, but here and there it made a big difference.

What is the best way to use Transactions and Try/Catch error handling? To make it more interesting what is the best way to do same for queries with repeating logic (using WHILE for example) and committing or rollbacking set of changes depending if there was an error?

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.