Performance improvements : Gaining performance by adding SET NOCOUNT ON in stored procedures, triggers and using Qualified object names in dynamic sql

Hi Team,

I was looking at articles for improving performance on sql server and here are few things that we might be missing in our environment and could help in gaining some extra kick.

SET NOCOUNT ON:

I have verified by scripting few stored procedure none is using SET NOCOUNT ON using this option suppresses the number of rows effected messages which are returned after execution of all inserts selects deletes updates. Its fine when you are having these on ssms but when you are executing sp from app this small bit of information can really gain some performance by avoiding sending this data over the network.

Note: In case you need to have the rows affected you can still use @@rowcount to get that information

It can be done by adding SET NOCOUNT ON just after AS clause in CREATE PROCEDURE STATEMENT.
In-fact in new versions of SQL SERVER SSMS the template has by default updated and introduced its usage:

CREATE PROCEDURE PROCEDURENAME
AS
SET NOCOUNT ON
BEGIN
DO STUFF
COMMIT
GO

QUALIFIED OBJECTS:
Using schema and own name with object can help reduce look up times and add up few cents in performance.
Example instead of just using exec spname use dbname.dbo.spname

Read more about it on microsoft here:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql?view=sql-server-2017

Best Practices for SP here:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-2017#best-practices

Here is a graph for test results from one of the article over internet:


Things to consider before making changes(Recommended by some developers):

One thing to keep in mind, is that it can interfere with certain technologies. For example if you have old ADO code (prior to ASP.NET), it interprets the DONE_IN_PROC messages as independent resultsets, so your existing code may already have things like rs.nextRecordSet() to skip them. Also certain modules in Entity Framework (and probably other ORMs) rely on those messages to determine success of DML operations. So don't just blindly add them to all of your code if you use these technologies and already have working code.

Comments

Popular posts from this blog

Enabling fixed Sidebar for blogger Contempo template for screen sizes smaller than 1440px

Adding copy to clipboard button to every google code prettify pre blocks in blogger

Solution: More Posts & Posts Links not working with Adsense Anchor ads on Touch screen mobiles in blogger