Category Archives: Store Procedures

SQL Server: How Local Variables Can Reduce Query Performance

It’s a common practice by database developers to use local variables in stored procedures and scripts to place filter on basis of these local variables. YES, these local variables can slowdown your queries. Let’s prove it.
Create a new table and insert dummy rows.


      (tempID UNIQUEIDENTIFIER,tempMonth INT,tempDateTime DATETIME )

INSERT INTO TempTable (tempID, tempMonth, tempDateTime)

SELECT NEWID(),(CAST(100000*RAND() AS INT) % 12) + 1 ,GETDATE()
GO 100000 — (EXECUTE THIS BATCH 100000 TIME)

— Create an index to support our query

CREATE NONCLUSTERED INDEX[IX_tempDateTime] ON [dbo].[TempTable]
([tempDateTime] ASC)
Now let’s execute a simple query with hard coded values in WHERE clause


WHEREtempDateTime > ‘2012-07-10 03:18:01.640’
Table ‘TempTable’. Scan count 1, logical reads 80,physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Check out its execution plan and index seeks properties. You can find that estimated rows are double to actual rows but that’s not a big difference to affect execution plan and resultantly optimizer has selected a proper plan to execute this query.

Query optimizer has estimated number of rows from its base statistics histogram i.e.  EQ_ROWS + AVG_RANGE_ROWS (77 + 88.64286)

DBCC SHOW_STATISTICS (‘dbo.TempTable’, IX_tempDateTime)

Now, let’s modify our SELECT query and use local variable and execute it. You will find that query optimizer has selected a different plan this time, a more costly plan. WHY ??

SET@RequiredDate = ‘2012-07-10 03:18:01.640’

WHEREtempDateTime  >@RequiredDate
Table ‘TempTable’. Scan count 1, logical reads 481, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Huge difference of estimated and actual number of rows clearly indicating that query optimizer was unable to proprly estimate number of rows and with this wrong estimation, it has selected a more costly execution plan. Basically Query Optimizer does not know the value of local variable at the time of optimization and resultantly can’t use histogram of statistics. It behaves differently with inequality and equality operators.

In Case of Inequality Operator:
In our case of inequality operator in query, query optimizer used a simple formula of 30% of total rows.

Estimated Rows =(Total Rows * 30)/100 = (100000*30)/100 = 30000

 In Case of Equality Operator:

SET@RequiredDate = ‘2012-07-10 03:18:01.640’

WHEREtempDateTime  =@RequiredDate

If equality operator is used with local variables, query optimizer gets estimated rows figure from a different formula i.e.  Density * Total Number of Table Rows. Execute following query to get density value.

DBCC SHOW_STATISTICS (‘dbo.TempTable’, IX_tempDateTime)

All Density = 0.0007358352

Total Number of Rows in Table = 100000
Estimated Rows = Density * Total Number =  0.0007358352 *  100000 = 73.5835

Drop table when not required



>SQL Server: Automatic Query Execution at Every Instance Startup

Though production database servers are design to stay up for 24×7, but still when ever these production database servers go down and restart, sometime we need to execute some queries automatically on every start-up, like clean some setup tables or capture some sort of necessary data which is only available at instance start-up.

For such queries which need to be executed automatically at every start-up, we have to create a store procedure to encapsulate all these queries. Then automatic execution of this stored procedure is achieved by using the sp_procoption system stored procedure.
(Note: Best place to store such stored procedure is MASTER database)
Let’s create a stored procedure to store instance start-up time in a log table.
–Create table to hold startup time
CREATE TABLE dbo.InstanceLog
(StartupTime DATETIME)
–Create stored procedure to execute on startup automatically
CREATE PROCEDURE dbo.Proc_InsertStartupTime
INSERT dbo.InstanceLog
Now we will use SP_PROCOPTION to tell SQL Server that we want to execute our stored procedure at every instance start-up. Syntax will be as follow:
@ProcName = ‘Proc_InsertStartupTime’,
@OptionName = ‘STARTUP’,
@OptionValue = ‘TRUE’
After executing above statement, when ever SQL Server instance will restart, stored procedure will be executed automatically and a new row in our log table dbo.InstanceLog will be inserted.
To revert this option and to stop stored procedure from automatic execution, we will use following syntax.
EXEC sp_procoption
@ProcName = ‘Proc_InsertStartupTime’,
@OptionName = ‘STARTUP’,
@OptionValue = ‘OFF’
(Applicable for SQL Server 2005 and above versions)

>SQL Server: How to Avoid Big Single Error Log File on Production Servers


One thing, strange I found on our production database servers was a BIG SINGLE Error log file. It was taking long time to open and exploring it for required information was even worse. This happened because production database servers never go down and SQL Server is keeping error log to a single file. On every restart SQL Server initiates a new error log file but for production servers, restart occurs after very long time. That is why error log file was growing to a very large size.
Only solution for this problem is system stored procedure sp_cycle_errorlog. This system stored procedure is used to cycle error log file without restarting SQL Server. Executing this stored procedure with job or manually helps to cycle the error log file periodically.
Exec master.dbo.sp_cycle_errorlog

>Sql Server:Performance Counter to Count Stored Procedure Re-compilations


In an ideal situation a stored procedure is compiled once and forth coming queries related to this stored procedure are satisfied with already created query plan. The following actions may cause recompilations of a stored procedure plan:
  • Use of a WITH RECOMPILE clause in the CREATE PROCEDURE or EXECUTE statement.
  • Schema changes to any of the referenced objects, including adding or dropping constraints, defaults, or rules.
  • Running sp_recompile for a table referenced by the procedure.
  • Restoring the database containing the procedure or any of the objects the procedure references (if you are performing cross-database operations).
  • Sufficient server activity causing the plan to be aged out of cache
  • A sufficient percentage of data changes in a table that is referenced by the stored procedure.
  • The procedure interleaves Data Definition Language (DDL) and Data Manipulation Language (DML) operations.
These recompilations of stored procedure add overhead on the processor. We should closely monitor the occurrence of recompilation of these stored procedures.  Performance monitor counter Sql Re-Compilations/sec is very helpful counter for this recompilation monitoring task.
Recommended value for SQL Re-Compilations/sec is close to ZERO. If nonzero values are consistently occurring for this counter, we should seriously search for the culprit stored procedures. Sql Profiler is a nice tool for this task.
How to monitor Sql Re-Compilation/sec counter values.
Step 1
On database server machine    > click on RUN — > type Perfmon — > delete existing counters by clicking on cross button on top.

           Click on + button to add new counter. Add counter Form select “SQL Server: SQL Statistics”. Now select “SQL Re-Compilations/sec” from counters list. Click “Add “button . Click “Close” button to view graphical view of performance monitor.

Why should we use Store Procedure instead of Ad hoc queries

Stored procedure is a set of Structured Query Language statements with an assigned name which are stored with in the database in compiled form so that it can be used by a number of programs.

Ad hoc queries are normally written on application side and are meant to be used for once only and are never saved to run again.

At the beginning developers who are not good at database side, like to use ad hoc queries for fetching and to make changes in required data. These ad hoc queries can kill performance and some time it is hard to control complex logics through these ad hoc queries. Store procedures are the best choice to accomplish these data processes. These are helpful in following regards.

  • Reduce Network Traffic

Excessive network traffic is a big performance killer. Frequent trips to database server from client application (because of ad hoc queries) may be a cause of this excessive network traffic. Store Procedures helps you to reduce such network traffic by holding group of statements and returning required result with a single call.

Avoid lengthy transactions in store procedures to prevent lock contention problems.

  • Database Privileges

Users can be restricted from having access to read/write to tables directly in database by using store procedures. Only developer of store procedure require specific privileges, while creating a store procedure but to execute these store procedures client of application only need execute privileges.

  • Code Security

Sql Injections, which uses AND or Or to append commands on to a valid input parameter can be defended by using store procedures, but If you still have a string in your application with the store procedure name and concatenated parameters from user input to that string in your code, you are still on risk.

  • Execution Plan Re-use

Store procedures are compiled once and resultant execution plan are utilized for future executions. This results in tremendous performance boosts when store procedures are called repeatedly.

  • Efficient Re-use of Code

Commonly used store procedures can be effectively used for different projects.

For example, create a store procedure which returns amount in words against integer input. (INPUT= 1542214, OUTPUT= 1.5 Million, Forty Two Thousand, Two Hundred and Fourteen). Store procedure like this, can be used in any application.

  • Single Point of Maintenance

Change in business rules defined for a project, over a time is normal. If such business rules are controlled with in store procedures rather then application, it is easy to make changes in database and NO need to recompile your application code.