Yet another SQL Server enthusiast

2015-04-09

How to get full path of every XML element using XQuery and recursive Common Table Expressions

Filed under: sql server,xml,xquery — admin @ 6:36 AM

Solution:

Code Snippet
DECLARE @x XML = N'
<A>
    <B>
        <C>Text1</C>
        <D>Text2</D>
        <E>
            <F>Text3</F>
        </E>
    </B>
    <G>Text4</G>
</A>';

  WITH XmlRec
  AS (
    SELECT    a.XmlCol.query('.') AS CurrentElement,
            a.XmlCol.value('(text())[1]', 'NVARCHAR(100)') AS CurrentElementValue,
            a.XmlCol.value('local-name(.)', 'SYSNAME') AS CurrentElementName,
            CONVERT(NVARCHAR(4000), a.XmlCol.value('local-name(.)', 'SYSNAME')) AS CurrentElementPath
    FROM    @x.nodes('*') a(XmlCol)
    UNION ALL
    SELECT    b.XmlCol.query('.') AS CurrentElement,
            b.XmlCol.value('(text())[1]', 'NVARCHAR(100)') AS CurrentElementValue,
            b.XmlCol.value('local-name(.)', 'SYSNAME') AS CurrentElementName,
            rec.CurrentElementPath + '/' + CONVERT(NVARCHAR(4000), b.XmlCol.value('local-name(.)', 'SYSNAME')) AS CurrentElementPath
    FROM    XmlRec rec
    CROSS APPLY rec.CurrentElement.nodes('*/*') b(XmlCol)
  )
  SELECT    r.*
  FROM        XmlRec r
  OPTION (MAXRECURSION 100)

Results:

XML.Element.Full.Path

2015-04-07

Fun with SQL Profiler and Tunning template

Filed under: don't,fun — admin @ 10:58 PM

Fun.With.SQL.Profiler.Don't

2015-03-29

How to compute number of weekends between two dates

Filed under: sql server — admin @ 6:28 PM

Solution: one solution is to use DATEDIFF function to compute difference in weeks between two dates:

Code Snippet
DECLARE @Start DATE, @Stop DATE;
SELECT    @Start = {d '2015-03-27'}, @Stop = {d '2015-03-30'};
SELECT    DATEDIFF(WEEK, @Start, @Stop);
— Result: 1 week

In this example, the result is 1 week and can be interpreted also as 1 full weekend (Saturday + Sunday).

Why it works ? DATEDIFF function has an interesting behavior when it comes about @@DATEFIRST setting and weeks:

Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function is deterministic.

Because for DATEDIFF function, Sunday is always the first day of week, a simple difference in weeks between Saturday (ex. 2015-03-28) and next day which is Sunday (ex. 2015-03-29) will give 1 [week] and also this result can be interpreted as 1 weekend:

Compute.Number.Of.Weekend.Without.Calendar.Table

More, difference in weeks between Saturday (ex. 2015-03-28) and the next Saturday (ex. 2015-04-04) will give 1 week which can be considered to be 1 full weekend even there are 1 Saturday + 1 Sunday and another Saturday:

Code Snippet
DECLARE @Start DATE, @Stop DATE;
SELECT    @Start = {d '2015-03-28'}, @Stop = {d '2015-04-04'};
SELECT    DATEDIFF(WEEK, @Start, @Stop);
— Result: 1 week

2015-03-16

How to see variable values in SQL Profiler ?

Filed under: debug,RAISERROR,sql server — admin @ 10:35 PM

For scalar variables, one answer is RAISERROR with a severity of 1 (which is equivalent to an warning) plus User Error Message event:

Code Snippet
CREATE PROCEDURE dbo.DoSomethingWithA
@a INT
AS
BEGIN
    DECLARE @b VARCHAR(100)
    SET @b = QUOTENAME(@a)

    — Get values for SQL Profiler
    PRINT '@b='
    PRINT @b
    RAISERROR('Variables: @a=%d, @b=%s', 11, 1, @a, @b)
    — End of Get values for SQL Profiler
END
GO

— Test
EXEC dbo.DoSomethingWithA 123
GO

SQL Profiler output:

Raiserror.Sql.Profiler.Variavle.Values

Note: this solution should be used only for debugging.

Note #2: It won’t work with user defined functions.

2015-03-07

ITDevConnect 2015 / April 25 – 26

Filed under: Uncategorized — admin @ 10:18 PM

“Join us at ITdevConnect, a two day event packed with knowledge about the latest innovations in the field of technology. Find out what’s new in 2015: Visual Studio 2015, C# 6, ASP.NET 5, SQL Server 2014 and try the programmes during a hands-on lab on Windows Azure.”

http://www.adces.ro/agenda-itdevconnect-2015/

2015-03-01

NonSARGable predicates #2: about nullable columns, ISNULL, IS NULL and COALESCE

Filed under: index,optimization,predicate,SARG,scalar,sql server 2012 — admin @ 12:37 AM

This post discuss how query complexity can be reduced and query performance can be improved by simply using mandatory columns (NOT NULL) instead of optional (nullable) columns. Also, no query change is needed.

First, let’s create a table to store all changes of products prices:

Code Snippet
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.PriceHistory(
    ID            INT IDENTITY(1,1) NOT NULL
    CONSTRAINT    PK_PriceHistory_ID PRIMARY KEY (ID),
    ProductID    INT NOT NULL, — Foreign key
    Price        NUMERIC(9,2) NOT NULL,
    StartDate    DATETIME NULL, — nullable / optional column
    EndDate        DATETIME NULL — nullable / optional column
);
GO
CREATE    INDEX IX_PriceHistory_StartDate
ON        dbo.PriceHistory(StartDate)
INCLUDE    (EndDate)
GO

Our query should compute how many prices are active now. I’ve used three different methods to write conditions for row filtering and none of them follow the rules for SARG-able predicates ([1], [2]):

Code Snippet
DECLARE    @d DATETIME = GETDATE()

SELECT    COUNT(*)
FROM    dbo.PriceHistory d
WHERE    ISNULL(d.StartDate, @d) <= @d
AND        @d <= ISNULL(d.EndDate, @d)

SELECT    COUNT(*)
FROM    dbo.PriceHistory d
WHERE    (d.StartDate <= @d OR d.StartDate IS NULL)
AND        (@d <= d.EndDate OR d.EndDate IS NULL)

SELECT    COUNT(*)
FROM    dbo.PriceHistory d
WHERE    COALESCE(d.StartDate, @d) <= @d
AND        @d <= COALESCE(d.EndDate, @d)

For these queries the execution plans include an Index Scan which is the expected data access operator because all conditions are non SARG-able:

Nullable.Columns.Query.Execution.Plan

Second, we change StartDate and EndDate in order to be mandatory columns (NOT NULL) thus:

Code Snippet
DROP INDEX IX_PriceHistory_StartDate ON dbo.PriceHistory
GO
UPDATE    dbo.PriceHistory
SET        StartDate    = CASE WHEN StartDate IS NULL THEN {d '1753-01-01'} ELSE StartDate END,
        EndDate        = CASE WHEN EndDate IS NULL THEN {ts '9999-12-31 23:59:59.997'} ELSE EndDate END
        — 1753-01-01 and 9999-12-31 23:59:59.997
        — represents the minimum and maximum value for DATETIME data type
WHERE    StartDate IS NULL
OR        EndDate    IS NULL
GO
ALTER TABLE dbo.PriceHistory ALTER COLUMN StartDate DATETIME NOT NULL
ALTER TABLE dbo.PriceHistory ALTER COLUMN EndDate DATETIME NOT NULL
GO
CREATE    INDEX IX_PriceHistory_StartDate
ON        dbo.PriceHistory(StartDate)
INCLUDE    (EndDate)
GO

and then re-run the same queries. Second time, we get next execution plans:

Mandatory.Columns.Query.Execution.Plan

This time, for the first two queries, the execution plans include the Index Seek operator even though the conditions (ISNULL / OR IS NULL) aren’t SARG-able. Examining the properties of Index Seek operator for Query 1 and Query 2 reveals the SQL Server rewrote the filtering conditions (Query 1: @d <= ISNULL(d.EndDate, @d), Query 2: (d.StartDate <= @d OR d.StartDate IS NULL) AND (@d <= d.EndDate OR d.EndDate IS NULL)) into something much simple because StartDate and EndDate are mandatory columns (doesn’t allow NULLs). For example, the condition for Query 1 becomes StartDate >= @d AND EndDate <= @d:

Index.Seek Also, we can see that for the third query the condition (COALESCE) wasn’t changed and the execution plan includes Index Scan.

TLTR

When next predicates

  • ISNULL(Column, @variable) {=|>|>=|<|<=} @variable 
  • Column  {=|>|>=|<|<=} @variable OR Column IS NULL

are defined on null-able indexed columns SQL Server uses a Scan operators for data access. When nullability is changed to NOT NULL then SQL Server can choose a Seek without any change in source code. For example changing the source code from ISNULL(Column, @variable) <= @variable to Column <= @variable is not needed. I have observed this behavior on SQL Server 2008 and SQL Server 2012.

2014-11-01

Cannot process the object "… DMX / MDX DDL statement …". The OLE DB provider "MSOLAP" for linked server "…" indicates that either the object has no columns or the current user does not have permissions on that object.

Filed under: Uncategorized — admin @ 12:25 AM

Sometimes, we have to execute DMX / MDX DDL statements from T-SQL. At first sight, the solution is use a linked server and OPENROWSET or OPENQUERY functions:

Code Snippet
SELECT    *
FROM    OPENQUERY(SVR_AS_SQL2008R2, N'CREATE MINING STRUCTURE [New Mailing]
(
    CustomerKey LONG KEY,
    Gender TEXT DISCRETE,
    [Number Cars Owned] LONG DISCRETE,
    [Bike Buyer] LONG DISCRETE
)')

But this query generates following error:

Code Snippet
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "CREATE MINING STRUCTURE [New Mailing]
(
    CustomerKey LONG KEY,
    Gender TEXT DISCRETE,
    [Number Cars Owned] LONG DISCRETE,
    [Bike Buyer] LONG DISCRETE
)". The OLE DB provider "MSOLAP" for linked server "SVR_AS_SQL2008R2" indicates that either the object has no columns or the current user does not have permissions on that object.

The cause of this error is that the statement(s) executed by OPENROWSET / OPENQUERY should return at least one resultset (OPENROWSET / OPENQUERY will return only the first one). In this case, the statement is CREATE MINING STRUCTURE and it doesn’t returns a resultset.

The solution is to use EXEC … AT linked_server statement thus:

Code Snippet
EXEC (
N'CREATE MINING STRUCTURE [New Mailing]
(
    CustomerKey LONG KEY,
    Gender TEXT DISCRETE,
    [Number Cars Owned] LONG DISCRETE,
    [Bike Buyer] LONG DISCRETE
)') AT SVR_AS_SQL2008R2

2014-10-13

One solution to optimize SQL queries that use scalar functions

Filed under: function,optimization,scalar,UDF — admin @ 7:05 AM

Scenario

  1. There is a query that uses big tables,
  2. It’s executed rarely (ex. it’s executed once or twice per month),
  3. This query has many conditions, some of them uses UDF scalar functions. The combined selectivity of non-UDF conditions is high (ex. 3.295 rows from 31.263.601).

Problem

The query is slow. How can be optimized quickly this query without rewriting scalar function ?

Scalar UDF used for this test:

Code Snippet
CREATE FUNCTION dbo.SlowScalarFunction(@Name NVARCHAR(250))
RETURNS NVARCHAR(250)
AS
BEGIN
    DECLARE @Result NVARCHAR(250), @Index TINYINT = 1, @Len TINYINT = LEN(@Name + '$') 1;
    SET @Result = CASE WHEN @Name IS NOT NULL THEN '' END
    WHILE @Index <= @Len
    BEGIN
        SET @Result += UPPER(SUBSTRING(@Name, @Index, 1))
        SET @Index += 1
    END
    RETURN @Result
END;

Note: this scalar function was wrote intentionally to be slow.

I’ve used following query for this test:

Code Snippet
— Test #1
SELECT    dbo.SlowScalarFunction(h.ProductName) AS UpperName, h.Quantity
FROM    [dbo].[BigProduct] p JOIN BigTransactionHistory h ON p.ProductID = h.ProductID
WHERE    p.ListPrice > 3550 AND h.Quantity >= 100 AND dbo.SlowScalarFunction(h.ProductName) LIKE N'ROAD%'
GO

Execution plan

Serial.Execution.Plan.Scalar.User.Defined.Function.WHERE.01

As you can see,

  • The WHERE clause contains one condition that calls a scalar UDF ( dbo.SlowScalarFunction(h.ProductName) LIKE N’ROAD%’ ).
  • The same scalar UDF is used also by SELECT clause.
  • The execution plan is serial.
  • There is a “missing index” warning that tells we should create a non-clustered index:
Code Snippet
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[BigTransactionHistory] ([ProductID],[Quantity])
INCLUDE ([ProductName])

[One] Solution

1) Because this query is executed rarely, the suggested “missing” index will be / could be used rarely (for read operations). This means that after creation of this index will be an overhead due to maintenance of this index after every INSER, UPDATE or DELETE operation.

2) The usage of scalar function forces SQL Server to choose a serial plan.

Because of these two reasons I decided to not create the suggested index and to decompose the initial query thus:

  • One query without scalar functions calls. This query inserts data that into a temporary table (#Results). This table will contain, usually, a small amount of data. Because of lack of scalar functions, the execution plan will use parallelism.
  • For that small amount of rows inserted into temporary table I call the scalar UDF.
Code Snippet
SELECT    ProductName, Quantity
INTO    #Results
FROM    [dbo].[BigProduct] p JOIN BigTransactionHistory h ON p.ProductID = h.ProductID
WHERE    ListPrice > 3550 AND Quantity >= 100

ALTER TABLE #Results
ADD UpperName NVARCHAR(250)

UPDATE    #Results
SET        UpperName = dbo.SlowScalarFunction(ProductName)

SELECT    UpperName, Quantity
FROM    #Results
WHERE    UpperName LIKE N'ROAD%'

DROP TABLE #Results

Execution plan:

Paralelism.Plan.Scalar.User.Defined.Function.WHERE.02

Using a warm buffer, after these changes, the execution time dropped from 10 seconds to 1 second .

2014-09-28

Simple solution to optimize bulk insert operations

Filed under: optimization,sql server,sql server 2012 — admin @ 11:44 PM

What can be done to optimize (minimize execution time of) bulk insert operations of large *.csv files into a SQL Server database ? Here you may find an excellent article on this subject.

But there is also a simple thing you should consider: autogrowth events. These operations can slow down some operations executed on a SQL Server database (including bulk insert operations). This means that if we eliminate these auto growth events the execution time will decrease.

To test this hypothesis I’ve done two sets of tests using a *.csv file (4.366.563 rows) which has a size of 462 MB uncompressed:

  1. One test using default database options (for size and auto growth) and
  2. The other test use a database with an initial size of 1GB and a log file with an initial size of 500 MB. Because source file uses UTF-8 encoding the text columns are defined using NVARCHAR data type, for every char SQL Server uses two bytes (without data compression).

I’ve used following scripts:

  1. First test:

    Code Snippet
    CREATE DATABASE TestBulkInsert
    ON PRIMARY  (NAME = 'TestBulkInsert_01_Data', FILENAME = 'E:\BD\TestBulkInsert.mdf')
    LOG ON (NAME = 'TestBulkInsert_01_Log', FILENAME = 'E:\BD\TestBulkInsert.ldf')
    GO
    ALTER DATABASE TestBulkInsert
    SET RECOVERY SIMPLE
    GO
    USE TestBulkInsert;
    GO
    SELECT    file_id,  name, size * 8 AS [size KB],
            CASE
                WHEN growth = 0 THEN 'fixed size'
                WHEN is_percent_growth = 1 THEN STR(growth, 10, 0) + ' %'
                ELSE STR(growth * 8, 10, 0) + ' KB'
            END  AS growth_description
    FROM    sys.database_files
    GO
    /*
    file_id name                   size KB     growth_description
    ——- ———————- ———– ——————
    1       TestBulkInsert_01_Data 3136              1024 KB
    2       TestBulkInsert_01_Log  1024                10 %
    */

  2. Second test:
    Code Snippet
    CREATE DATABASE TestBulkInsert
    ON PRIMARY  (NAME = 'TestBulkInsert_01_Data', FILENAME = 'E:\BD\TestBulkInsert.mdf', SIZE = 2GB, FILEGROWTH = 500MB)
    LOG ON (NAME = 'TestBulkInsert_01_Log', FILENAME = 'E:\BD\TestBulkInsert.ldf', SIZE = 100MB, FILEGROWTH = 100MB)
    GO
    ALTER DATABASE TestBulkInsert
    SET RECOVERY SIMPLE
    GO
    USE TestBulkInsert;
    GO
    SELECT    file_id,  name, size * 8 AS [size KB],
            CASE
                WHEN growth = 0 THEN 'fixed size'
                WHEN is_percent_growth = 1 THEN STR(growth, 10, 0) + ' %'
                ELSE STR(growth * 8, 10, 0) + ' KB'
            END  AS growth_description
    FROM    sys.database_files
    GO
    /*
    file_id name                   size KB     growth_description
    ——- ———————- ———– ——————
    1       TestBulkInsert_01_Data 2097152         512000 KB
    2       TestBulkInsert_01_Log  102400          102400 KB
    */

Target table was created using the next script:

Code Snippet
CREATE TABLE [dbo].[currenttmp](
    [ColA] [bigint] NOT NULL PRIMARY KEY,
    [ColB] [nvarchar](20) NULL,
    [ColC] [nvarchar](4000) NULL
) ON [PRIMARY]
GO

To import data I’ve used an SSIS package:

ssis.dataflowtask

Bellow are my results:

  Test 1 Test 2 Difference
Average execution time (sec.) 326 222 104
Average execution time (mm:ss) 5:26 3:42 1:44
Autogrowth events 946 0 946

As you can see, simply eliminating auto growth events can reduce significantly the execution  time when importing large files. This is possible because autogrowth events are expensive.

Note: It’s worth reading about Instant File Initialization (not used in these tests).

2014-08-25

sp_DeadlockAnalyzer v02

Filed under: Uncategorized — admin @ 7:07 AM

I have made following changes in sp_DeadlockAnalyzer:

  • “Resources” record-set includes resources (mainly objects – tables or indexes views, indexes), pivoted SPIDs and locks (acqr = acquired / owner process, wait = waiter process) for a better understanding.
  • “SQL Statements” record-set includes now the source line of every statement.
  • “Data access operators” record-set includes those operators which access resources

Sample output:

sp_DeadlockAnalyzer.Diag.2

Source code:

Code Snippet
USE master;
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF NOT EXISTS (
    SELECT    *
    FROM    sys.procedures p
    JOIN    sys.schemas s ON p.schema_id = s.schema_id
    WHERE    s.name = 'dbo'
    AND        p.name = 'sp_DeadlockAnalyzer'
)
BEGIN
    EXEC ('CREATE PROCEDURE dbo.sp_DeadlockAnalyzer AS SELECT 1');
END
GO

ALTER PROCEDURE dbo.sp_DeadlockAnalyzer
(
    @xdl XML — Deadlock graph event
)
AS
BEGIN
    IF OBJECT_ID(N'tempdb.dbo.#Resources') IS NOT NULL
        DROP TABLE #Resources;
    IF OBJECT_ID(N'tempdb.dbo.#Processes') IS NOT NULL
        DROP TABLE #Processes;

    — Information about resources
    CREATE TABLE #Resources (
        Id                            INT IDENTITY PRIMARY KEY,
        Resource_LockGranularity    SYSNAME NOT NULL,
        Resource_DatabaseId            INT NOT NULL,
        Resource_HoBtID                BIGINT NOT NULL,
        Resource_ObjectName            SYSNAME NULL,
        Resource_IndexName            SYSNAME NULL,
    
        ProcessOwner_Id                SYSNAME NOT NULL,
        ProcessOwner_LockMode        SYSNAME NOT NULL,

        ProcessWaiter_Id            SYSNAME NOT NULL,
        ProcessWaiter_LockMode        SYSNAME NOT NULL
    );

    INSERT    #Resources (
        Resource_LockGranularity    ,
        Resource_DatabaseId            ,
        Resource_HoBtID                ,
        Resource_ObjectName            ,
        Resource_IndexName            ,
    
        ProcessOwner_Id                ,
        ProcessOwner_LockMode        ,

        ProcessWaiter_Id            ,
        ProcessWaiter_LockMode
    )
    SELECT    Resource_LockGranularity= x.XmlCol.value('local-name(.)','SYSNAME'),
            Resource_Database        = x.XmlCol.value('@dbid','INT'),
            Resource_HoBtID            = x.XmlCol.value('@hobtid','BIGINT'),
            Resource_ObjectName        = QUOTENAME(PARSENAME(x.XmlCol.value('@objectname','SYSNAME'), 3)) + '.' + QUOTENAME(PARSENAME(x.XmlCol.value('@objectname','SYSNAME'), 2)) + '.' + QUOTENAME(PARSENAME(x.XmlCol.value('@objectname','SYSNAME'), 1)),
            Resource_IndexName        = QUOTENAME(x.XmlCol.value('@indexname','SYSNAME')),

            ProcessOwner_Id            = own.XmlCol.value('@id', 'SYSNAME'),
            ProcessOwner_LockMode    = own.XmlCol.value('@mode', 'SYSNAME'),

            ProcessWaiter_Id        = wtr.XmlCol.value('@id', 'SYSNAME'),
            ProcessWaiter_Mode        = wtr.XmlCol.value('@mode', 'SYSNAME')
    FROM    @xdl.nodes('deadlock-list/deadlock/resource-list/*') x(XmlCol)
    OUTER APPLY x.XmlCol.nodes('owner-list/owner') own(XmlCol)
    OUTER APPLY x.XmlCol.nodes('waiter-list/waiter') wtr(XmlCol);

    CREATE TABLE #Processes (
        SPID             INT NOT NULL,
        IsVictim         BIT NOT NULL,
        Database_Name     SYSNAME NOT NULL,
        InputBuffer         XML NULL,
        TransactionName  SYSNAME NULL,
        IsolationLevel     SYSNAME NULL,
        Snapshot_Isolation_State SYSNAME NULL,
        DeadlockPriority SMALLINT NULL,
        LogUsed             INT NULL,
        ClientApp         SYSNAME NULL,
        HostName         SYSNAME NULL,
        LoginName         SYSNAME NULL,
        Database_Id         INT NOT NULL,
        CallStack         XML NULL,
        Process_Id         SYSNAME PRIMARY KEY
    );
    INSERT     #Processes
    SELECT    y.SPID, y.IsVictim,
            QUOTENAME(DB_NAME(y.Database_Id)) AS Database_Name, y.InputBuffer,
            y.TransactionName, y.IsolationLevel, db.snapshot_isolation_state_desc,  y.DeadlockPriority, y.LogUsed,
            y.ClientApp, y.HostName, y.LoginName, y.Database_Id,
            y.CallStack,
            y.Process_Id
    FROM (
        SELECT    Process_Id    = x.XmlCol.value('(@id)[1]', 'SYSNAME'),
                SPID        = x.XmlCol.value('(@spid)[1]', 'INT'),
                IsVictim = x.XmlCol.exist('(.)[(@id)[1] = (../../@victim)[1]]'),

                — CurrentDatabase=  DB_NAME(x.XmlCol.value('(@currentdb)[1]', 'SMALLINT')) + N' (' + x.XmlCol.value('(@currentdb)[1]', 'NVARCHAR(5)') + N')',
                Database_Id    =  x.XmlCol.value('(@currentdb)[1]', 'SMALLINT'),
                InputBuffer    = x.XmlCol.query('inputbuf'),

                TransactionName    = x.XmlCol.value('(@transactionname)[1]', 'SYSNAME'),
                IsolationLevel = x.XmlCol.value('(@isolationlevel)[1]', 'SYSNAME'),
                DeadlockPriority = x.XmlCol.value('(@priority)[1]', 'SMALLINT'),
                LogUsed        = x.XmlCol.value('(@logused)[1]', 'INT'),
        
                ClientApp    = x.XmlCol.value('(@clientapp)[1]', 'SYSNAME'),
                HostName    = x.XmlCol.value('(@hostname)[1]', 'SYSNAME'),
                LoginName    = x.XmlCol.value('(@loginname)[1]', 'SYSNAME'),
                CallStack    = x.XmlCol.query('./executionStack')
        FROM    @xdl.nodes('deadlock-list/deadlock/process-list/process') x(XmlCol)
    ) y INNER JOIN sys.databases db ON y.Database_Id = db.database_id;

    DECLARE @DistinctProcesses TABLE (SPID_Description VARCHAR(16) PRIMARY KEY);
    INSERT    @DistinctProcesses (SPID_Description)
    SELECT    DISTINCT QUOTENAME('SPID ' + CONVERT(VARCHAR(11), p.SPID))
    FROM    #Processes p;
    DECLARE @DistinctProcessesList NVARCHAR(4000);
    SET        @DistinctProcessesList = '';
    SELECT    @DistinctProcessesList = @DistinctProcessesList + ', ' + dp.SPID_Description
    FROM    @DistinctProcesses dp;
    SET        @DistinctProcessesList = STUFF(@DistinctProcessesList, 1, 2, '');

    DECLARE @SqlStatement NVARCHAR(MAX);
    SET        @SqlStatement = N'
    SELECT    t.Resource_ObjectName, t.Resource_IndexName, t.Resource_LockGranularity,
            ' + @DistinctProcessesList + ',
            t.Resource_DatabaseId, t.Resource_HoBtID
    FROM (
        SELECT    x.Resource_LockGranularity, x.Resource_ObjectName, x.Resource_IndexName,
                ''SPID '' + CONVERT(VARCHAR(11), y.SPID) AS SPID_Description, y.LockInfo,
                x.Resource_DatabaseId, x.Resource_HoBtID
        FROM (
            SELECT    r.Resource_LockGranularity, r.Resource_ObjectName, r.Resource_IndexName,
                    r.ProcessOwner_Id, ''acqr '' + r.ProcessOwner_LockMode AS OwnerLockInfo,
                    r.ProcessWaiter_Id, ''wait '' + r.ProcessWaiter_LockMode AS WaitwerLockInfo,
                    r.Resource_DatabaseId, r.Resource_HoBtID
            FROM    #Resources r
        ) x
        CROSS APPLY (
            SELECT    p.SPID, x.OwnerLockInfo
            FROM    #Processes p WHERE p.Process_Id = x.ProcessOwner_Id
            UNION ALL
            SELECT    p.SPID, x.WaitwerLockInfo
            FROM    #Processes p WHERE p.Process_Id = x.ProcessWaiter_Id
        ) y(SPID, LockInfo)
    ) z
    PIVOT( MAX(z.LockInfo) FOR z.SPID_Description IN (' + @DistinctProcessesList + ') ) t';
    EXEC (@SqlStatement);

    — Information about server processes / conections including SQL batches
    SELECT * FROM #Processes ORDER BY IsVictim DESC;

    — Statements and execution plans
    DECLARE @Statements TABLE (
        Process_Id            SYSNAME NOT NULL,
        SPID                INT NOT NULL,
        IsVictim            BIT NOT NULL,
        [Statement]            SYSNAME NOT NULL,    
        [SqlHandle]            VARBINARY(64) NULL,
        [Text]                NVARCHAR(4000) NULL,
        Line                INT NULL,
        StmtStartOffset        INT NULL,
        StmtEndOffset        INT NULL,
        StatementNum        INT NOT NULL,
        PlanHandle            VARBINARY(64) NULL,
        QueryPlan            XML NULL
    );
    INSERT    @Statements
    SELECT    y.*, qs.plan_handle AS PlanHandle, pln.query_plan AS QueryPlan
    FROM (
        SELECT    Process_Id        = x.Process_Id,
                SPID            = x.SPID,
                IsVictim= x.IsVictim,
                [Statement]        = y.XmlCol.value('(@procname)[1]', 'SYSNAME'),
                [SqlHandle]        = CONVERT(VARBINARY(64), y.XmlCol.value('(@sqlhandle)[1]', 'VARCHAR(128)'), 1),
                [Text]            = y.XmlCol.value('(text())[1]', 'NVARCHAR(4000)'),
                Line            = y.XmlCol.value('(@line)[1]', 'INT'),
                StmtStartOffset    = ISNULL(y.XmlCol.value('(@stmtstart)[1]', 'INT'), 0),
                StmtEndOffset    = ISNULL(y.XmlCol.value('(@stmtend)[1]', 'INT'), 1),
                StatementNum    = ROW_NUMBER() OVER(ORDER BY y.XmlCol DESC)
        FROM    #Processes x
        OUTER APPLY x.CallStack.nodes('executionStack/frame') y(XmlCol)
    ) y
    LEFT JOIN sys.dm_exec_query_stats qs ON y.SqlHandle = qs.sql_handle  AND y.StmtStartOffset = qs.statement_start_offset AND y.StmtEndOffset = qs.statement_end_offset
    OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) pln

    SELECT    s.SPID, s.IsVictim,
            s.[Statement], s.[Text], s.Line,
            s.QueryPlan, s.PlanHandle, s.SqlHandle,
            s.Process_Id
    FROM    @Statements s
    ORDER BY s.IsVictim DESC, s.StatementNum;

    — Data access operators
    WITH XMLNAMESPACES (
        'http://www.w3.org/2001/XMLSchema-instance&#039; AS xsi,
        'http://www.w3.org/2001/XMLSchema&#039; AS xsd,
        DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan&#039;
    ),
    QueryPlans AS (
        SELECT    x.Process_Id, x.SPID, x.IsVictim, x.QueryPlan
        FROM (
            SELECT    s.Process_Id, s.SPID, s.IsVictim, s.QueryPlan, ROW_NUMBER() OVER(PARTITION BY BINARY_CHECKSUM(CONVERT(NVARCHAR(MAX),s.QueryPlan)) ORDER BY @@SPID) AS RowNum
            FROM    @Statements s
            WHERE    s.QueryPlan IS NOT NULL
        ) x
        WHERE    x.RowNum = 1
    )
    SELECT    b.SPID, b.IsVictim,
            b.StatementType, b.[Statement],
            b.LogicalOp, b.PhysicalOp,
            b.ObjectName, b.IndexName, b.IndexKind,
            b.Warnings, b.MissingIndexes,
            b.Process_Id,
            b.BatchId, b.StatementId, b.NodeId
    FROM (
        SELECT    a.Process_Id, a.SPID, a.IsVictim,
                a.BatchId, a.StatementId, a.NodeId,
                a.StatementType, a.[Statement], /*a.ParamStatement,*/
                LogicalOp = CASE
                                WHEN a.TableReferenceId = 1 AND a.IndexKind = N'Clustered' AND a.LogicalOp = 'Clustered Index Seek' THEN 'Key Lookup'
                                ELSE a.LogicalOp
                            END,
                PhysicalOp = CASE
                                WHEN a.IndexKind = N'NonClustered' AND a.PhysicalOp IN('Clustered Index Insert', 'Table Insert') THEN 'Index Insert'
                                WHEN a.IndexKind = N'NonClustered' AND a.PhysicalOp IN('Clustered Index Update', 'Table Update') THEN 'Index Update'
                                WHEN a.IndexKind = N'NonClustered' AND a.PhysicalOp IN('Clustered Index Delete', 'Table Delete') THEN 'Index Delete'
                                WHEN a.IndexKind = N'NonClustered' AND a.PhysicalOp IN('Clustered Index Merge', 'Table Merge') THEN 'Index Merge'
                                ELSE a.PhysicalOp
                            END,
                a.ObjectName, a.IndexName, a.IndexKind,
                a.Warnings, a.MissingIndexes
        FROM (
            SELECT    — batch_XmlCol = batch.XmlCol.query('.'),
                    BatchId            = DENSE_RANK() OVER(ORDER BY batch.XmlCol),
                    StatementId        = stmt.XmlCol.value('(@StatementId)[1]', 'INT'),
                    NodeId            = oper.XmlCol.value('(@NodeId)[1]', 'INT'),

                    StatementType    = stmt.XmlCol.value('(@StatementType)[1]', 'SYSNAME'),
                    [Statement]        = stmt.XmlCol.value('(@StatementText)[1]', 'NVARCHAR(4000)'),
                    ParamStatement    = stmt.XmlCol.value('(@ParameterizedText)[1]', 'NVARCHAR(4000)'),

                    LogicalOp        = oper.XmlCol.value('(@LogicalOp)[1]', 'SYSNAME'),
                    PhysicalOp        = oper.XmlCol.value('(@PhysicalOp)[1]', 'SYSNAME'),
                    [TableReferenceId]= objt.XmlCol.value('(@TableReferenceId)[1]', 'INT'),
                    [IndexKind]        = objt.XmlCol.value('(@IndexKind)[1]', 'SYSNAME'),

                    [ObjectName]    = ISNULL(objt.XmlCol.value('(@Database)', 'SYSNAME') + '.' + objt.XmlCol.value('(@Schema)', 'SYSNAME') + '.', '') + objt.XmlCol.value('(@Table)[1]', 'SYSNAME'),
                    [IndexName]        = ISNULL(objt.XmlCol.value('(@Index)', 'SYSNAME'), ''),

                    Warnings        = wrng.XmlCol.query('.'),
                    MissingIndexes    = misx.XmlCol.query('.'),

                    Process_Id        = xp.Process_Id,
                    SPID            = xp.SPID,
                    IsVictim= xp.IsVictim
            FROM    QueryPlans xp
            CROSS APPLY xp.QueryPlan.nodes('//Batch') batch(XmlCol)
            CROSS APPLY batch.XmlCol.nodes('Statements/StmtSimple') stmt(XmlCol)
            OUTER APPLY stmt.XmlCol.nodes('QueryPlan/Warnings') wrng(XmlCol)
            OUTER APPLY stmt.XmlCol.nodes('QueryPlan/MissingIndexes') misx(XmlCol)
            OUTER APPLY stmt.XmlCol.nodes('QueryPlan//RelOp') oper(XmlCol) — Operators
            –OUTER APPLY oper.XmlCol.nodes('(.//Object)[1]') objt(XmlCol)
            OUTER APPLY oper.XmlCol.nodes('./*/Object') objt(XmlCol)
        ) a
    ) b
    WHERE b.PhysicalOp IN  (
        'Table Insert', 'Table Update', 'Table Delete', 'Table Merge', 'Table Scan',
        'Clustered Index Insert', 'Clustered Index Update', 'Clustered Index Delete', 'Clustered Index Merge', 'Clustered Index Scan', 'Clustered Index Seek',
        'Index Insert', 'Index Update', 'Index Delete', 'Index Merge', 'Index Scan', 'Index Seek',
        'RID Lookup' — Key Lookup = Clustered Index Seek
    )    AND EXISTS (
        SELECT    *
        FROM    #Resources r
        WHERE    (r.ProcessOwner_Id = b.Process_Id OR r.ProcessWaiter_Id = b.Process_Id)
        AND        r.Resource_ObjectName = b.ObjectName
        AND        r.Resource_IndexName = b.IndexName
    )
    ORDER BY IsVictim DESC, SPID, BatchId, StatementId, NodeId;
END;
GO

Sample usage: Create a trace with SQL Profiler including [Deadlock graph event]. After intercepting a deadlock event you have to use [Extract event data] command to save on local computer the XML content of [Deadlock graph event] into a *.xdl file (ex. D:\DbEvents\deadlock18.xdl). Then, you have to execute the stored procedure:

Code Snippet
DECLARE @xdl XML — Deadlock graph event
SELECT    @xdl = x.BulkColumn
FROM    OPENROWSET(BULK 'D:\DbEvents\deadlock18.xdl', SINGLE_NCLOB) x;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
EXEC dbo.sp_DeadlockAnalyzer @xdl;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Note #1: The type of resource can be keylock or pagelock (at this moment).

Note #2: I’ve tested this procedure only with SQL Profiler – SQL Server 2012.

Note #3: This procedure should be executed on the same SQL instance.

Note #4: Next update will analyze also triggers execution plans. A future version will try to correlate data access operators and type of SQL statements  (ex. SELECT -> S locks, UPDATE -> U or X lock on target table, etc.).

« Previous PageNext Page »

Create a free website or blog at WordPress.com.