Tip for SQL code reviewers

One of the primary tasks for any development lead or a tech lead is it to have a code review on written code by other developers , accepting code without reviewing it , may cause a serious problems specially in environment where heavy transactions are always happening.

There are many tips and ways to do this task , but let me focus here on one part only which is SQL stored procedures , the best practices here is to test the IO reads and writes , this can be performed by Below statement:

SET STATISTICS IO ON
Exec USP_YourStoredProcedure @param1

If you set the statistics to ON this will cause SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.

the result of the above execution will give you number of reads and writes for the written SP , now the lower of above numbers you get is the best SP you have .

The following lists describes the output items

Table : Name of the table.
Scan count :Number of index or table scans performed.
logical reads : Number of pages read from the data cache.
physical reads : Number of pages read from disk.
read-ahead reads : Number of pages placed into the cache for the query.
lob logical reads :Number of text, ntext, image, or large value type (varchar(max), nvarchar(max), varbinary(max)) pages read from the data cache.
lob physical reads : Number of text, ntext, image or large value type pages read from disk.
lob read-ahead : reads Number of text, ntext, image or large value type pages placed into the cache for the query.

We should always focus on logical read as it is the main factor to identify any serious problem , now lets say that you have executed SP with 10,0000 logical reads , then it is for sure that you have to go and rewrite some logic in the SP or to assign a proper index on columns referred in the joins or where condition .