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
An event that it will execute Master.sys.SP_WHO
Problems if a 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 an 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 a 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 we 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 the 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 a 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 there are only around 11 records for ntSeverity = 12 that why query 1 is costlier than query 2
Conclusion: SQL server is not able to compile the query with an updated value of procedure parameters which may lead to the 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 a big difference in execution plan due to change of a 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 the 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 an 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 starts from the 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 created 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 the 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 a 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 a global temporary table in the stored procedure.
Cause: Scope of global temporary tables is a 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 an 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 a user. For example:
Let's assume any user has dbo as default schema. He has created a 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 execute the stored procedure created by him.
So correct and efficient way is to execute any stored procedure is specify the schema name. For example:
EXECUTE dbo.XP_LOGININFO
Indexes best practices in SQL server: Where to start creating indexes?
SQL Server query optimization tips: Tuning best practices with examples
Fastest or most efficient way to insert data in SQL server
Index in SQL server with examples
What is clustered index in SQL server
What is non clustered index in SQL server
Indexes best practices in SQL server: Where to start creating indexes?
SQL Server query optimization tips: Tuning best practices with examples
Fastest or most efficient way to insert data in SQL server
Index in SQL server with examples
What is clustered index in SQL server
What is non clustered index in SQL server
4 comments:
Thank you, very useful
helpful !!!
thanks for such a neat and easily understandable article.
Really great analysis and thorough coverage of particular topic... Keep it up dude !!!
Post a Comment