SQL Server: Script to Find Tables in Stored Procedures, That Are Without NonClustered Index

From my query bank here is another useful script which can help a Database Administrator to list down stored procedures with tables names which are used in stored procedures but don't contain and non clustered index.

Query Output:

SQL Server: Placing Alert for Compatibility Level Change in SQL 2005

Microsoft SQL Server allows its users to keep behavior of a database compatible to its older versions. Like, if someone is using “*=” type of left outer joins in some quires/Stored Procedures as she created it for SQL Server 2000. Though such join are not allowed in SQL Server 2005 and subsequent versions but one still can keep database behavior as SQL Server 2000 by keeping its compatibility level to 80.

Recently, a client reported that someone (DBA or Application) is changing his database compatibility, which should remain compatible to SQL Server 2000 (compatibility level 80). He wants to know at what time this change is being made.

SQL Server 2008 and subsequent versions keep record of this compatibility change to its log, but SQL Server 2005 has no such facility. It means, in SQL Server 2005, you never know when someone has changed compatibility level.

In SQL Server 2008 and subsequent versions one can change compatibility level of a database by following simple TSql statement.

ALTER DATABASE AdventureWorks SET COMPATIBILITY_LEVEL = 90;

But in SQL Server 2005, only method to change this compatability level is its system stored procedure i.e. sys.sp_dbcmptlevel. SQL Profiler is the only place where you can trace when this stored proecdure was executed. But what if, we need to place an alert for this change and generate a mail for this change. Or what if, we need to stop users/applications to change a database compatability level.

Only way to achieve this functionality is,  to update system stored procedure  sp_dbcmptlevel.

Lets perform this task, step by step.

Step 1:  Stop SQL Server 2005 services

Step 2:  Login using DAC (Dadicated Administrative Connection). For this right click on SQL Server 2005 service, on Advanced tab, change startup parameters by adding -m; at existing values.

Step 3: Start SQL Server 2005 services

Step 4: Open SQL Server Management Studio and open Database Engine Query

Step 5: Login as valid sysadmin user or ADMIN:InstanceName

Step 6: Change mssqlsystemresource database to read_write mode

Step 7: It’s the time to update our system stored procedure i.e. sp_dbcmptlevel. If you need to keep only comptability level to 80 or 90 then change following lines of stored procedures with same values i.e.80 or 90 or as per your choice.

select  @cmptlvl60 = 60, 
@cmptlvl60 = 65,
@cmptlvl60 = 70,
@cmptlvl60 = 80,
@cmptlvl60 = 90, 

And if you also need to add a mail alert for this change then add following code in error control portion of stored procedure.

DECLARE @bodyText VARCHAR(200)

SET @bodyText='User '
+ CONVERT(VARCHAR,SYSTEM_USER)  
+' trying to change Compatibility Level of Database '                            + CONVERT(VARCHAR,@dbname)
+ ' at '
 + CAST(GETDATE() AS VARCHAR(50)) 
EXEC msdb.dbo.sp_send_dbmail @recipients='essmess@gmail.com;', --Change Email Address Accordingly 
@subject = 'Compatibility Level Change Alter', 
@profile_name = 'DBTeam', --Change DB mail Profile Accordingly 
@body = @bodyText, 
@body_format = 'TEXT' ;

Here is complete updated script of stored procedure. (This script is only applicable to SQL Server 2005, for SQL Server 2008 and subsequent version, its totally different, which you can get by sp_helptext)

Step 8: Change mssqlsystemresource database to read_only mode

Step 9: Close SSMS session, stop SQL Server services and change its startup parameters back to normal.

Step 10: Start SQL Server Services and you are done.

SQL Server Scripts: Get All Nested Stored Procedures List (Procedures with dependent Procedures)

A stored procedure can be called from another stored procedure as nested stored procedure. Recently on production server, we were asked for all stored procedures in which other stored procedures are called as nested. Here is simple script.

SELECT  * FROM (SELECT  NAME AS ProcedureName, SUBSTRING(( SELECT  ', ' + OBJDEP.NAME

FROM    sysdepends

        INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID

        INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID

WHERE obj.type = 'P'  

AND Objdep.type = 'P'

AND sysdepends.id = procs.object_id

ORDER BY OBJ.name

FOR

XML PATH('')

), 2, 8000) AS NestedProcedures

FROM sys.procedures  procs )InnerTab

WHERE NestedProcedures IS NOT NULL

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.

USE AdventureWorks

GO
CREATE TABLE TempTable
      (tempID UNIQUEIDENTIFIER,tempMonth INT, tempDateTime DATETIME )
GO

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)
INCLUDE ( [tempID]) WITH ( ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
Now let’s execute a simple query with hard coded values in WHERE clause

SET STATISTICS IO ON

GO
SELECT * FROM TempTable
WHERE tempDateTime > '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 ??

DECLARE @RequiredDate DATETIME
SET @RequiredDate = '2012-07-10 03:18:01.640'

SELECT * FROM TempTable
WHERE tempDateTime  > @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:

DECLARE @RequiredDate DATETIME
SET @RequiredDate = '2012-07-10 03:18:01.640'

SELECT * FROM TempTable
WHERE tempDateTime  = @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

DROP TABLE TempTable

SQL Server: Object Validation Error by Estimated Plan While Actual Plan Working Fine

It happened dozen of times when I tried to get an Estimated Execution Plan, it return object verification error, while the same Stored Procedure was working perfectly. Then what’s wrong with Estimated Execution Plan, lets create a simple procedure.

CREATE PROCEDURE Proc_TestProcedure

AS
BEGIN
      IF 1 = 2
            SELECT * FROM NoTable -- NoTable doesn't exists
      ELSE
      SELECT 'ESLE'
END
GO

On compilation and execution, above stored procedure will not return any error, because condition is never true, so it never need to exec SELECT * FROM NoTable, and to check that table exists or not. Now just try to get execution plan of

EXEC Proc_TestProcedure

It will return error.

Msg 208, Level 16, State 1, Procedure Proc_TestProcedure, Line 8

Invalid object name 'NoTable'.

Why So.

Because on estimation, query optimizer check each and every statement separately and once it try to estimate cost for “NoTable”, which never exists, it returns above mentioned error.

SQL Server: Automatic Query Execution at Every Instance Startup

Though production database servers are design to stay up for 24x7, 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.

USE MASTER

GO

--Create table to hold startup time

CREATE TABLE dbo.InstanceLog

(StartupTime DATETIME)

GO

--Create stored procedure to execute on startup automatically

CREATE PROCEDURE dbo.Proc_InsertStartupTime

AS

INSERT dbo.InstanceLog

SELECT GETDATE()

GO

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:

EXEC SP_PROCOPTION

@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.

Step2

           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.