Feb 11, 2013

Sql server best practices for stored procedure


Best Practice 1:  Name of stored procedure should not start with SP_

Cause: Sql server uses prefix SP_ for system stored procedures which are in sys schema of Master database. For example:  SP_WHO, SP_HELP etc.  If we will create any user defined stored procedure sql server will first search in Master Database then in our current working directory.    I am explaining it by an example:

Let's assume our current database is Exact_Help. Now we are creating stored procedure named SP_WHO

USE Exact_Help

CREATE PROCEDURE SP_WHO
AS
BEGIN
   
    SELECT 'Exact Help'
   
END

Now we are executing this procedure:

EXECUTE SP_WHO

Sample output:

spid
ecid
status
loginame
hostname
blk
dbname
cmd
request_id
1
0
background                   
sa
                                                                                                                               
0   
NULL
RESOURCE MONITOR
0
2
0
background                   
sa
                                                                                                                               
0   
NULL
XE DISPATCHER  
0
3
0
background                   
sa
                                                                                                                               
0   
NULL
XE TIMER       
0
4
0
background                   
sa
                                                                                                                               
0   
NULL
LAZY WRITER    
0
5
0
background                   
sa
                                                                                                                                
0   
NULL
LOG WRITER     
0
6
0
background                   
sa
                                                                                                                               
0   
NULL
LOCK MONITOR   
0
7
0
background                   
sa
                                                                                                                               
0   
master
SIGNAL HANDLER 
0

It is not executing our procedure!!! It is executing Master.sys.SP_WHO

If we will execute:

EXECUTE EXACT_HELP.dbo.SP_WHO

Event that it will execute Master.sys.SP_WHO

Problems if user defines procedures begin with prefix sp_:

a. It will take extra execution time to search the stored procedure in sys schema of Master database.
b. If this procedure is already a system stored procedure we will get unexpected result.

Best Practice 2: We should keep the NOCOUNT setting on in the stored procedure.

Cause: In any application stored is mostly executed by program where there is no need to know the total numbers of affected rows by SELECT, INSERT, UPDATE, DELETE, MARGE etc statements. I am explaining it by an example. We are creating a stored procedure named usp_GetMessage:

CREATE PROCEDURE usp_GetMessage
AS
BEGIN
   
    SELECT * FROM sys.messages
        
END

Now we are executing this procedure:

EXECUTE usp_GetMessage

If we will click on the Messages tab of query result grid will find a message something like:

(97526 row(s) affected)

Sql server has to pay extra cost to calculate total numbers of affected rows by this procedure.   But if we will set the nocount on that is:

ALTER PROCEDURE usp_GetMessage
AS
BEGIN
   
    SET NOCOUNT ON
   
    SELECT * FROM sys.messages
   
END

Now if will execute the procedure sql server will no count the total affected rows by this procedure and we can decrease the total execution time of procedure in each execution.

Note: There is no relation of NOCOUNT setting and @@ROWCOUNT function.

Best Practice 3: Never update the value of parameters of a stored procedure before using in sql queries.

Cause: I am explaining it by an example. Creating following objects in sql server:

--Creating a table
CREATE TABLE tblMessage (
    ntMessageID BIGINT IDENTITY PRIMARY KEY,
    ntSeverity INT,
    vcMessage VARCHAR(500)
)

--Inserting some records
INSERT INTO tblMessage
SELECT
    Severity,
    LEFT ([TEXT], 500)
FROM Sys.messages

--Creating a non clustered index on it
CREATE NONCLUSTERED INDEX NCI_Severity
ON tblMessage(ntSeverity)
INCLUDE(vcMessage)

GO

--Creating stored procedure
CREATE PROC uspGetMsg1(
     @ntSeverity AS INT
)
AS
    
     SET @ntSeverity = @ntSeverity - 4
         
     SELECT vcMessage
     FROM tblMessage
     WHERE ntSeverity = @ntSeverity
GO
    
--Creating similar stored procedure
CREATE PROC uspGetMsg2(
     @ntSeverity AS INT
)
AS
             
     SELECT vcMessage
     FROM tblMessage
     WHERE ntSeverity = @ntSeverity    

Stored procedure uspGetMsg1 and uspGetMsg2 is exactly same except stored procedure uspGetMsg1 modified the parameters  Now we are going the execute the stored procedure uspGetMsg1 and uspGetMsg2:

EXECUTE uspGetMsg1 16  
EXECUTE uspGetMsg2 12

In the procedure uspGetMsg1 :  
@ntSeverity = @ntSeverity 4
ntSeverity = 16-4 = 12

So both stored procedure will get the data from tblMessage of same ntSeverity that is 12. Now we are comparing the execution plan:


For same ntSeverity we are getting different execution cost. Why?
Stored procedure uspGetMsg1 has got compiled for ntSeverity = 16 not for the updated value of ntSeverity. You can check it by right clicking on SELECT operator in the execution plan checking parameter list in the properties:

 

It shows the procedure has compiled for @ntSeverity = 16 but while actual value is 12. So execution plan is displaying the query cost for ntSeverity = 16 not for 12. If we will observe the tblMessage we will find this table has around 69839 records for ntSeverity = 16 while three are only around 11 records for ntSeverity = 12 that why query 1 is costlier than query 2  

Conclusion: Sql server is not able to complied the query with updated value of procedure parameters which may lead to wrong execution plan. So why should not update the procures parameters before using the queries.

Best Practice 4: Don't write such stored procedure which has big difference in execution plan due to change of value of parameters.

Best Practice 5: Use TRY CATCH block to handle the errors.
I am explaining it one example. Let's assume in our current database has three tables of following schema:

CREATE TABLE tblEmp(
     ntEmpID  BIGINT PRIMARY KEY IDENTITY,
     vcName  VARCHAR(10)
)

CREATE TABLE tblDep(
     ntDepID  BIGINT PRIMARY KEY IDENTITY,
     vcName  VARCHAR(20)
)

CREATE TABLE tblOrder(
     ntOrderID  BIGINT PRIMARY KEY IDENTITY,
     vcName  VARCHAR(10)
)

Now we are creating a stored procedure:

CREATE PROC uspInserData
AS
BEGIN
     BEGIN TRY
    
          INSERT INTO tblEmp VALUES('Scott'),('Greg')
          INSERT INTO tblDep VALUES('Information Thechnoloy'),('Electrical')
          INSERT INTO tblOrder VALUES('Book'),('Copy')
         
     END TRY
     BEGIN CATCH
         
          SELECT
              ERROR_MESSAGE() AS ErrMsg,
              ERROR_LINE() AS ErrLine,
              ERROR_PROCEDURE() AS ErrProc
                
     END CATCH
END

Now we are going to execute this stored procedure:

 EXECUTE dbo.uspInserData

We will get error message like:

ErrMsg
ErrLine
ErrProc
String or binary data would be truncated.
7
uspInserData

In the catch block we can handle message according to our requirements. This error message doesn't tell which table has thrown this error message. With help of Error line number we can find it. Line number:
1. Always start from CREATE or ALTER PROC
2. Error line number also includes empty line
3. Since dynamic sql executes in different scope so error line in case of error in dynamic query start from beginning of the dynamic query.  

Best Practice 7: Follow coding convention.
Best Practice 8: Deallocate the temporary tables, sql cursors if it has no use.

Cause: Temporary tables and sql cursors are create in the tempdb database. Disk space etc of system database tempdb is very crucial for query performance so we should use resources of tempdb when it is really necessary. For example:

CREATE uspTempdbManagemet   
AS
BEGIN

     DECLARE @ntSeverity AS INT = 0
     DECLARE @ntCount AS INT
    
     SELECT severity,COUNT(*) AS ntCount
     INTO #Tempdb
     FROM sys.messages
     GROUP BY severity
    
     UPDATE tblEmployee SET ntAge = ntAge + 1
     SELECT * FROM tblEmployee
    
    
     WHILE(EXISTS(SELECT * FROM #Tempdb WHERE severity > @ntSeverity)) BEGIN
    
          SELECT
              @ntSeverity = severity,
              @ntCount = ntCount
          FROM #Tempdb
          WHERE severity <> @ntSeverity
          ORDER BY severity
    
          EXECUTE dbo.uspMsg @ntSeverity,@ntCount
         
          SET @ntSeverity = @ntSeverity + 1
         
     END
    
     SELECT * FROM tblOrder
     SELECT * FROM tblOrderHistory
    
END

Above stored procedure has few problems from use of temporary table point of view. We should:

a. Create temporary table  when it is necessary
b. Drop temporary table when it is not necessary

Now we are writing same stored procedure in better way:

CREATE uspTempdbManagemet   
AS
BEGIN

     DECLARE @ntSeverity AS INT = 0
     DECLARE @ntCount AS INT
    
     UPDATE tblEmployee SET ntAge = ntAge + 1
     SELECT * FROM tblEmployee
    
    
     SELECT severity,COUNT(*) AS ntCount
     INTO #Tempdb
     FROM sys.messages
     GROUP BY severity
    
     WHILE(EXISTS(SELECT * FROM #Tempdb WHERE severity > @ntSeverity)) BEGIN
    
          SELECT
              @ntSeverity = severity,
              @ntCount = ntCount
          FROM #Tempdb
          WHERE severity <> @ntSeverity
          ORDER BY severity
    
          EXECUTE dbo.uspMsg @ntSeverity,@ntCount
         
          SET @ntSeverity = @ntSeverity + 1
         
     END
    
     DROP TABLE #Tempdb
    
     SELECT * FROM tblOrder
     SELECT * FROM tblOrderHistory
    
END

Same is true for other temporary objects like sql cursor etc.

Best Practice 9: Avoid using global temporary table in the stored procedure.
Cause: Scope of global temporary tables is connection. For example:

CREATE PROC uspGlobalTempTable
AS
     SELECT *
     INTO ##tblGlobal
     FROM sys.messages
    
     WAITFOR DELAY '0:00:30';
     DROP TABLE ##tblGlobal 

Now execute this stored procedure in two query window of Sql server management studio (ssms).

Query window 1:
EXECUTE dbo.uspGlobalTempTable

Query window 2:
EXECUTE dbo.uspGlobalTempTable

We may get error message like:
There is already an object named '##tblGlobal' in the database.

So try to avoid using global temporary tables in the stored procedures.

Best Practice 10: Use schema name while executing any stored procedure.

Cause: If we will not specify the schema name in the stored procedure first it will search in sys schema then default schema of user. For example:

Let's assume any user has dbo as default schema. He has created stored procedure in his default schema:

CREATE PROC XP_LOGININFO
AS
     SELECT DB_NAME()

Now he is executing this stored procedure:

EXECUTE XP_LOGININFO    

He is expecting that result set will be current database name while he got something like this:

account name
type
privilege
mapped login name
permission path
NT AUTHORITY\SYSTEM
user
admin
NT AUTHORITY\SYSTEM
NULL
NT SERVICE\MSSQL$SQLEXPRESS
group
admin
NT SERVICE\MSSQL$SQLEXPRESS
NULL
Ritesh-PC\Ritesh
user
admin
Ritesh-PC\Ritesh
NULL
BUILTIN\Users
group
user
BUILTIN\Users
NULL
RITESH-PC\ExactHelp
user
user
RITESH-PC\ExactHelp
NULL


Sql server has first searched this procedure in the sys schema and it found this procedure at there and executed it and it didn't executed the stored procedure created by him. 

So correct and efficient way is to execute any stored procedure is specify the schema name. For example:

4 comments:

  1. Sreedhar12/09/2013

    Thank you, very useful

    ReplyDelete
  2. anurag8/12/2014

    helpful !!!

    ReplyDelete
  3. Anonymous8/21/2014

    thanks for such a neat and easily understandable article.

    ReplyDelete
  4. Anonymous9/22/2014

    Really great analysis and thorough coverage of particular topic... Keep it up dude !!!

    ReplyDelete