SQL SERVER – TRANSACTION, DML and Schema Locks

Today we will be going over a simple but interesting concept. Many a time, I have come across the lack of understanding on how the transactions work in SQL Server. Today we will go over a small but interesting observation. One of my clients had recently invited me to help them out with an interview for their senior developers. I had interviewed nearly 50+ candidates in a single day. There were many different questions, but the following question was incorrectly answered most of the time.

The question was to create a scenario where you can see the SCHEMA LOCK. The interview panel initially thought that this might be a very easy question for this particular interview. I, however, insisted them to keep this question for time being and then remove it from the list of interview questions only when at least 10 candidates got it right. Contrary to our expectations, we never reached a point where we had to remove this question from the list!

Let us see a simple example regarding how to create a schema lock. The answer I was looking for is as follows: create a situation where the Schema is modified in the transaction and check the status of the object or session before the transactions are committed or rolled back.

Run the following code in Query Session 1:

USE AdventureWorks
GO
BEGIN TRANSACTION
GO
CREATE PROCEDURE mySP
AS
SELECT
1
GO
SELECT OBJECT_ID('mySP') ObjectID
GO

The above script will give us the objectID of the created stored procedure. In this case, the received ObjectID is 1300199682; this can be different for your execution.

Run the following code in Query Session 2:

USE AdventureWorks

GO
SELECT *
FROM sys.procedures
GO

This query will never finish running as in Session 1, where we have created the Stored Procedure. The name is already listed in the sys.procedures, but the transactions in Session1 are not yet committed.

If you run the following code, it will also not return any results even though we have received the ObjectID in Session 1.

USE AdventureWorks
GO
SELECT OBJECT_NAME(1300199682)
GO

Run the following code in Query Session 3:

Now to confirm that a schema lock is created, we can check the dynamic management views dm_tran_locks.

USE AdventureWorks
GO
SELECT *
FROM sys.dm_tran_locks
GO

We can clearly see from the example that there is a Sch-M (schema modify) lock over our ObjectID.

You can specify the where condition to this DMV as we are know the ObjectID here.
USE AdventureWorks
GO
SELECT request_type, request_mode, resource_associated_entity_id, request_type
FROM sys.dm_tran_locks
WHERE resource_associated_entity_id = 1300199682
GO

From above example, it is very clear that running DML code in the transactions create a schema modification lock until the transactions are over.

If you run the COMMIT or ROLLBACK statement in Session 1, the Queries in Session 2 will complete right away.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Stored Procedure and Transactions

I just overheard the following statement – “I do not use Transactions in SQL as I use Stored Procedure“.

I just realized that there are so many misconceptions about this subject. Transactions has nothing to do with Stored Procedures. Let me demonstrate that with a simple example.

USE tempdb
GO
-- Create 3 Test Tables
CREATE TABLE TABLE1 (ID INT);
CREATE TABLE TABLE2 (ID INT);
CREATE TABLE TABLE3 (ID INT);
GO
-- Create SP
CREATE PROCEDURE TestSP
AS
INSERT INTO
TABLE1 (ID)
VALUES (1)
INSERT INTO TABLE2 (ID)
VALUES ('a')
INSERT INTO TABLE3 (ID)
VALUES (3)
GO
-- Execute SP
-- SP will error out
EXEC TestSP
GO
-- Check the Values in Table
SELECT *
FROM TABLE1;
SELECT *
FROM TABLE2;
SELECT *
FROM TABLE3;
GO

Now, the main point is: If Stored Procedure is transactional then, it should roll back complete transactions when it encounters any errors. Well, that does not happen in this case, which proves that Stored Procedure does not only provide just the transactional feature to a batch of T-SQL.

Let’s see the result very quickly.

It is very clear that there were entries in table1 which are not shown in the subsequent tables. If SP was transactional in terms of T-SQL Query Batches, there would be no entries in any of the tables. If you want to use Transactions with Stored Procedure, wrap the code around with BEGIN TRAN and COMMIT TRAN.

The example is as following.
CREATE PROCEDURE TestSPTran
AS
BEGIN TRAN
INSERT INTO
TABLE1 (ID)
VALUES (11)
INSERT INTO TABLE2 (ID)
VALUES ('b')
INSERT INTO TABLE3 (ID)
VALUES (33)
COMMIT
GO
-- Execute SP
EXEC TestSPTran
GO
-- Check the Values in Tables
SELECT *
FROM TABLE1;
SELECT *
FROM TABLE2;
SELECT *
FROM TABLE3;
GO
-- Clean up
DROP TABLE Table1
DROP TABLE Table2
DROP TABLE Table3
GO

In this case, there will be no entries in any part of the table. What is your opinion about this blog post? Please leave your comments about it here.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Recompile Stored Procedure at Run Time

I recently received an email from reader after reading my previous article on SQL SERVER – Plan Recompilation and Reduce Recompilation – Performance Tuning regarding how to recompile any stored procedure at run time. There are multiple ways to do this. If you want your stored procedure to always recompile at run time, you can add the keyword RECOMPILE when you create the stored procedure. Additionally, if the stored procedure has to be recompiled at only one time, in that case, you can add RECOMPILE word one time only and run the SP as well. Let us go over these two options.

The RECOMPILE hint is used with a query and recompiles only that particular query. However, if the parameters are used in many statements in the stored procedure and we want to recompile all the statements, then instead of using the RECOMPILE option with all the queries, we have one better option that uses WITH RECOMPILE during stored procedure creation or execution.

Option 1:

CREATE PROCEDURE dbo.PersonAge (@MinAge INT, @MaxAge INT)
WITH RECOMPILE
AS
SELECT
*
FROM dbo.tblPerson
WHERE Age >= @MinAge AND Age <= @MaxAge
GO

Option 2:

EXEC dbo.PersonAge 65,70 WITH RECOMPILE

This method is not recommended for large stored procedures because the recompilation of so many statements may outweigh the benefit of a better execution plan.

Note: Recompilation is not necessarily the best solution.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Plan Recompilation and Reduce Recompilation – Performance Tuning

Recompilation process is same as compilation and degrades server performance. In SQL Server 2000 and earlier versions, this was a serious issue but in SQL server 2005, the severity of this issue has been significantly reduced by introducing a new feature called Statement-level recompilation. When SQL Server 2005 recompiles stored procedures, only the statement that causes recompilation is compiled, rather than the entire procedure. Recompilation occurs because of following reason:

  • On schema change of objects.
    • Adding or dropping column to/from a table or view
    • Adding or dropping constraints, defaults, or rules to or from a table.
    • Adding or dropping an index to a table or indexed view if index is used by the plan.
    • Adding or dropping trigger from a table
    • Dropping statistics from a table that is used in plan.
  • On change of the SET options: When a compiled plan is created, the SQL server also stores the environmental setting of a connection (SET option) with it. If the same stored procedure is executed by another connection that has a different SET option, then the existing cached plan is not reused. To reduce recompilation caused by Environment change, we should not change the SET options in the connection setting and stored procedure.
  • On statistics change of tables: Every time the SQL Server uses an already cached compiled plan, it checks the optimality before using it. SQL Server decides whether the plan is optimal for current amount of data in underlying tables. It could be that the data amount in underlying tables changed so much that the previously created plan is not optimized. For each table & index, the SQL server maintains a modification counter and if the counter values exceed the defined threshold, the previously created compiled plan is considered stale plan and a new plan is created.

Detecting recompilations: The below query retrieves the top 10 statements for which the recompilation count is maximum. Here, plan_generation_num returns a number that indicates the recompilation count of a statement.

SELECT TOP 10
qs.plan_generation_num
,
qs.execution_count,
DB_NAME(st.dbid) AS DbName,
st.objectid,
st.TEXT
FROM
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
ORDER BY plan_generation_num DESC

Another tool is to get recompilation details is the SQL Profiler. Along with the statements text, it also tells the reason of recompilation in EventClass and EventSubClass columns.

Reducing recompilations: We have no option to avoid “schema change” and “SET options” based recompilation. But we have following query and stored procedure hints to avoid “statistics change” based recompilation:

  • KEEP PLAN hint: The modification counter threshold for a temporary table is 6. This implies that when a stored procedure that creates a temporary table inserts 6 or more rows into this table, Stored Procedures will be recompiled as soon as this table is accessed. For permanent tables, this threshold is at least 500. We can increase the first threshold for the temporary table (6) to same as that of the permanent table (500) by using the KEEP PLAN query hint in the statement where the temporary table is used. For example,

SELECT TT.col4, SUM(PermTable.col1)
FROM dbo.PermTable
INNER JOIN #TempTable AS TT
ON PermTable.col1 = TT.col2
OPTION (KEEP PLAN);

  • KEEPFIXED PLAN hint: This hint completely avoids “statistics change” based recompilation of a query.

SELECT col1, col2
FROM dbo.PermTable
WHERE col3 &lt; 100
OPTION (KEEPFIXED PLAN);

  • Use Table variable instead of temporary tables: Because a change in cardinality of a table variable does not cause recompilations, consider using a table variable instead of a temporary table when faced with a problem of excessive recompilation. However, the use of table variables can lead to poorer query plans. Distribution statistics are not stored for table variables, and cardinality is only available during recompilation and not during initial compilation. One has to check whether this is the case and make an appropriate trade-off.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Stored Procedure Optimization Tips – Best Practices

We will go over how to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips, which we will cover in future articles.

  • Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.

CREATE PROC dbo.ProcName
AS
SET
NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

  • Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like

SELECT * FROM dbo.MyTable -- Preferred method
-- Instead of
SELECT * FROM MyTable -- Avoid this method
--And finally call the stored procedure with qualified name like:
EXEC dbo.MyProc -- Preferred method
--Instead of
EXEC MyProc -- Avoid this method

  • Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
  • Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')

  • Use the sp_executesql stored procedure instead of the EXECUTE statement.
    The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:

DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET
@Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

  • Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
  • Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
  • Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:

BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Size of Index Table – A Puzzle to Find Index Size for Each Index on Table

It is very easy to find out some basic details of any table using the following Stored Procedure.

USE AdventureWorks
GO
EXEC sp_spaceused [HumanResources.Shift]
GO

Above query will return following resultset

The above SP provides basic details such as rows, data size in table, and Index size of all the indexes on the table.

If we look at this carefully, a total of three indexes can be found on the table HumanResources.Shift.

USE AdventureWorks
GO
SELECT *
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('HumanResources.Shift')
GO

The above query will give result with query listing all the index on the table.

There is a small puzzle for all of you here. The puzzle is to write a query that will return the size for each index that is listed in above query. We need a query that will return an additional column in the above listed query and it should contain the size of the index. In our case, we will have three different sizes, which should add up to a total of 40 KB as shown in earlier query, where the total size is displayed.

I will publish the solution with due credit on this blog.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Stored Procedure are Compiled on First Run – SP taking Longer to Run First Time

During the PASS summit, one of the attendees asked me the following question.

Why the Stored Procedure takes long time to run for first time?

The reason for the same is because Stored Procedures are compiled when it runs first time. When I answered the same, he replied that Stored Procedures are pre-compiled, and this should not be the case. In fact, Stored Procedures are not pre-compiled; they compile only during their first time execution.

There is a misconception that stored procedures are pre-compiled. They are not pre-compiled, but compiled only during the first run. For every subsequent runs, it is for sure pre-compiled.

If you create any SP, you will find that there is no cache entry for the execution of that SP.

After running the SP for the first time, the entry for the cache is made in the system.

If we see the following script, we can notice the different of cache when SP was created and SP was executed.

/* Exeercise to verify if stored procedure pre-compiled */
USE AdventureWorks
GO
-- Clean Cache
DBCC FREEPROCCACHE
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[CompSP]') AND type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[CompSP]
GO
-- Create New Stored Procedure
CREATE PROCEDURE CompSP
AS
SELECT
*
FROM HumanResources.Department
GO
-- Check the Query Plan for SQL Batch
-- You will find that there is no ObjectName with CompSP
SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.TEXT AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;
GO
/* Execute Stored Procedure */
EXEC CompSP
GO
-- Check the Query Plan for SQL Batch
-- You will find that there is one entry with name ObjectName with name CompSP
SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.TEXT AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;
GO

The result set of above query is as following.

The above script to find out the cache is taken from the white paper SQL SERVER – Plan Caching in SQL Server 2008 by Greg Low. You can also read my follow up article SQL SERVER – Plan Caching and Schema Change – An Interesting Observation, where I have given an interesting conversation with Greg Low.

Reference: Pinal Dave (http://blog.SQLAuthority.com)