Enable Trace Flag 1222

Deadlock graph can be detected from SQL Profiler client tool as explained here. This approach requires to keep the client tool running during monitoring periods, and it will probably be ideal for intensive monitoring work. If deadlock occurs seldom but we don't want to miss the deadlock details, you might want to enable SQL Server engine trace flag 1222.

Trace flag 1222 outputs deadlock detail information to SQL Server Error log. It dumps the deadlock details in text format, so there is no fancy UI as in SQL Profiler. But the log contains all the same information as in SQL Profiler Deadlock Graph event, so some extra detective analysis will give deadlock picture.

To enable trace flag 1222, go to SQL Server Configuration Manager (assuming SQL 2008).

Click SQL Server Services on the left tree and doubeclick SQL Server instance.
Go to Advanced tab and find [Startup Parameters].
Add ";-T1222" and click OK.
Semicolon is needed to separate each parameter and -T option means SQL engine trace flag.

One drawback(?) of using SQL Server trace flag is that you need to restart SQL Server services to take effect. So in production server, maintenance should be scheduled properly.

Convert rows to columns - PIVOT

Suppose there is a table having multiple rows with a single column and you want to convert the data to a row with multiple corresponding columns. For example, you have the following table A that has only one column (col). The table A has 4 rows. Now let's say you want to convert it to a single row with 4 columns.

CREATE TABLE A(col int)
GO
insert A values (10)
insert A values (20)
insert A values (30)
insert A values (40)
GO

This conversion can be done by using PIVOT. To make things easy, we can add another computed column (r) that has row number. First, we select the single column (col) and computed row number from source table. For each row, PIVOT statement asks to calcualte SUM of col, which basically the same col value in this case since there is no aggregation here. If column type is non-numeric value such as string type, other aggregate function such as MIN() can be used. So from those four rows, the pivot returns 4 column data.

SELECT [1],[2],[3],[4]
FROM (SELECT col, row_number() over (order by col) r
      FROM A) AS sourceTable
PIVOT
(
   SUM(col) FOR r IN ([1],[2],[3],[4])
) AS p
GO

The result is as follows.



If rows are unknown, we can use dynamic pivot by adding the corresponding number of columns in IN clause and use SELECT * instead of SELECT [1],[2],[3],[4]. And the dynamic SQL will be run by using EXECUTE().

Locks in nested stored procedure

When a stored procedure(SP) calls nested stored procedure, if both the outer SP and inner SP have begin tran - commit tran statement, how long are the (exclusive) locks for the inner DML statement going to be held? That is, if the inner SP commits transaction, will the locks that acquired in the inner SP transaction be released? Or are their locks going to last all the way up to commit statement of outmost SP?

The short answer is it will last until the commit of the outmost SP.
Here is an simple experiment that can provide some proof.

CREATE PROC InnerSP
AS 
BEGIN        
 BEGIN TRAN        
    UPDATE dbo.Tab1
      SET Name='Name1'
      WHERE Id=1 
            
    SELECT 'After Update Tab1'            
    SELECT * FROM sys.dm_tran_locks
      WHERE resource_type != 'DATABASE'
    
    UPDATE dbo.Tab2
     SET City='Seattle'
     WHERE Id=1  
 COMMIT TRAN  
 
 SELECT '(InnerSP) After Commit'
 SELECT * FROM sys.dm_tran_locks 
   WHERE resource_type != 'DATABASE'
END
GO

CREATE PROC OuterSP
AS 
BEGIN        
  BEGIN TRAN  
     EXEC InnerSP
     -- cut --         
  COMMIT TRAN
  
  SELECT '(OuterSP) After Commit'    
  SELECT * FROM sys.dm_tran_locks    
    WHERE resource_type != 'DATABASE'
END
GO 

EXEC OuterSP

OuterSP calls InnerSP and InnerSP has BEGIN TRAN - COMMIT TRAN. If the exclusive locks are released after innerSP commit, dm_tran_locks will show no locks are held against Tab1 and Tab2 tables. But as shown below, the result is that the X locks are still intact after inner SP commit. Their locks are only released when outer SP commit is called.


The example above has UPDATE statements whose lock type is X (exclusive) lock. For Insert, Update, Delete, it holds X lock within a transaction, but for SELECT - within a transaction - the Shared (S) lock will be released once the rows are read in the default READ COMMITTED isolation level. If transaction isolation level is higher, S lock will last until it meets COMMIT.
On another note, if ROLLBACK is called in nested SP, the entire transaction is rolled back regardless of nested SP level. For example, when you have a SP that calls nestedSP1 that calls nestedSP2, if you call ROLLBACK in nestedSP2, the whole transaction from outmost SP will be rolled back. To avoid this, you can limit the transaction scope by using save transaction point. An example shown below rolls back nestedSP transaction only and UPDATE in outerSP is still valid.

CREATE PROC NestedSP
AS 
BEGIN        

BEGIN TRY            
  SAVE TRAN T2
    UPDATE dbo.Tab SET City='Redmond' WHERE Id=1  
    RAISERROR ('Error', 16, 1 );            
    COMMIT TRAN T2
END TRY                        
BEGIN CATCH            
    ROLLBACK TRAN T2
END CATCH            
            
END
GO

CREATE PROC OuterSP
AS 
BEGIN        
  BEGIN TRAN T1
 EXEC NestedSP
 -- cut --         
 UPDATE dbo.Tab SET City='Seattle' WHERE Id=1  
  COMMIT TRAN T1
END
GO 

EXEC OuterSP