Yet another SQL Server enthusiast

2014-08-25

sp_DeadlockAnalyzer v02

Filed under: Uncategorized — ---- @ 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' AS xsi,
        'http://www.w3.org/2001/XMLSchema' AS xsd,
        DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
    ),
    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.).

2014-05-05

Simple way to simulate a deadlock

Filed under: Uncategorized — ---- @ 11:47 PM

Why ?

Sometimes we need to test our apps for deadlocks. In this case we might need a simple way to simulate this type of error.

Intro

According to this, a deadlock occurs when two or more transactions block each other thus:

  • Every transaction (T1, T2) has successfully got a lock  on a resource (T1 <- R1, T2 <- R2) and
  • Every transaction try to lock the other resource (T1 -> R2, T2 -> R1) and
  • For every resource, existing locks and requested locks are incompatible (ex. T1 <- R1 and T2 -> R1).

IC135461[1] (source)

Resources: here (section Deadlock Information Tools, Resource attributes) and here (section Resource Details)  you may find a full list with all resources types. Usually, resources which appear in deadlocks are KEYs and PAGes. A KEY refers to index records and this type of resource is identified by a hash value (hash). A PAGe refers to 8K data pages and this type of resource is indentified by a pair <file_id>:<page_in_file>.

Example:

Code Snippet
CREATE DATABASE SimpleDeadlock;
GO

USE SimpleDeadlock;
GO

CREATE TABLE dbo.MyTable (
    Col1 INT NOT NULL CONSTRAINT PK_MyTable_Col1 PRIMARY KEY (Col1),
    Col2 VARCHAR(50) NOT NULL
);
GO

INSERT dbo.MyTable (Col1, Col2) VALUES (1, 'A');
INSERT dbo.MyTable (Col1, Col2) VALUES (2, 'B');
GO

Note: for every PRIMARY KEY constraint SQL Server automatically creates a UNIQUE INDEX (clustered or non-clustered). In this case, SQL Server creates an UNIQUE index (PK_MyTable_Col1) for the primary key constraint.

In order to get information about the hash value of every record within index PK_MyTable_Col1 we could use the following query:

Code Snippet
SELECT    *, %%lockres%% AS [KEY: hash value]
FROM    dbo.MyTable WITH(NOLOCK)
/*
Col1 Col2  KEY: hash value
—- —– —————
1    A    �a0)
2    B     (61a06abd401c)
*/

Note: %%lockres%% function is undocumented.

Because this table is small it consumes only one 8K page. We can get information about this page by using sp_AllocationMetadata stored procedure (or by using sys.system_internals_allocation_units view):

Code Snippet
EXEC dbo.sp_AllocationMetadata 'dbo.MyTable'
/*
Object Name Index ID    Alloc Unit ID        Alloc Unit Type First Page
———– ———– ——————– ————— ———-
MyTable     1          �5104    IN_ROW_DATA     (1:283)   
*/

As you can see, this page is 283 within file 1.

Lock modes (types) are described here and lock compatibility is described here. For example:

  • X (exclusive) locks are “Used for data-modification operations, such as INSERT, UPDATE, or DELETE”,
  • S (shared) locks are “Used for read operations that do not change or update data, such as a SELECT statement” and
  • S and X locks are incompatible.

Example

In this example we will simulate a write – read deadlock thus:

  • Every transaction will take an eXclusive lock on those two records and then
  • Every transaction will try to get a Shared lock on the other record.
  • Because X and S locks are incompatible these two transactions will generate a deadlock.

Using SQL Server Management Studio, open a new window (SQLQuery1.sql) and start a transaction T1 thus

Code Snippet
– Step #1
BEGIN TRAN;
    UPDATE    dbo.MyTable
    SET        Col2 =  Col2 + '*' – 'A' -> 'A*'
    WHERE    Col1 = 1;
– End of Step #1

At this moment only one resourcea0) is locked X by transaction T1:

 Step#1   Step#1.Locks

Now, open a new window (SQLQuery2.sql) and initiate a new transaction (T2):

Code Snippet
– Step #2
BEGIN TRAN;
    UPDATE    dbo.MyTable
    SET        Col2 =  Col2 + '*' – 'B' -> 'B*'
    WHERE    Col1 = 2;

    SELECT *
    FROM    dbo.MyTable
    WHERE    Col1 = 1;
– End of Step #2

At this moment, UPDATE statement will take an X lock on record 2 B and SELECT will try to take a S lock on record 1 A (which is already locked by T1). Because record 1 A is already locked X by T1, T2 will have to wait till this X lock is released:

Step#2

Step#2.Locks.Before.Deadlock

Now, we have to return in the first window (SQLQuery1.sql) and execute

Code Snippet
– Step #3
    SELECT *
    FROM    dbo.MyTable
    WHERE    Col1 = 2;
ROLLBACK;
– End of Step #3

In this case, SELECT statement from transaction T1 will try to take a S lock on record 2 B

Step#3Because this record is already locked X by T2 and because S and X locks are incompatible, T1 which request a S lock will have to wait till X lock (T2) are released.

In this moment the circle is completed and SQL Server deadlock monitor will automatically detect and end this deadlock:

Code Snippet
Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In my case, SQL Server selected transaction T2 as deadlock victim. This means that T2 is automatically cancelled (ROLLBACK) and all locks took by this transaction are released.

Note: transaction isolation level used for this example is read committed (default).

2014-04-01

Join Elimination: reasons which prevent this optimization

Filed under: Uncategorized — ---- @ 12:29 AM

Intro

SQL Server Query optimizer can automatically remove joins between parent and child tables if  the query’s result  remains unchanged. This is optimization is called foreign key join elimination.

Bellow example creates two tables with a simple foreign key defined on a mandatory column:

Code Snippet
CREATE DATABASE FKJoinElimination;
GO
USE FKJoinElimination;
GO

CREATE TABLE dbo.Customer (
    CustomerID INT PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL
);
GO

CREATE TABLE dbo.SalesOrder (
    SalesOrder INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID INT NOT NULL
        CONSTRAINT FK_SalesOrder_CustomerID
        REFERENCES dbo.Customer(CustomerID),
    TotalAmount NUMERIC(18,2) NOT NULL
);
GO

For the following query

Code Snippet
SELECT    COUNT(*)
FROM    dbo.SalesOrder so INNER JOIN dbo.Customer c ON so.CustomerID = c.CustomerID
GO

SQL Server generates an execution plan (SSMS: Query > Display estimated execution plan)  which includes a single data access operator (Clustered Index Scan on child table dbo.SalesOrder):

Foreign.key.join.elimination.test1

In above example, Query Optimizer automatically removes the JOIN between parent table dbo.Customer and child table dbo.SalesOrder and, also, it removes data access operator on parent table dbo.Customer:

Foreign.key.join.elimination.test1.2

This optimization is possible, mainly, because the foreign key constraint is defined on a single mandatory column and this constraint is trusted.

Reasons

I think it worth mentioning those reasons which prevent Query Optimized to apply this optimization:

  1. When join’s type is INNER JOIN:
    1. FK column allows NULLs. If join’s type is INNER this optimization is possible if FK column is mandatory (NOT NULL).
    2. FK constraint includes multiple columns. If join’s type is INNER this optimization is possible if FK’s constraint is defined on a single column.
    3. FK constraint isn’t trusted but is enabled and isn’t marked [NOT FOR REPLICATION]. This optimization is possible if FK constraint is trusted.
    4. FK constraint isn’t trusted because is disabled and/or is marked [NOT FOR REPLICATION]. This optimization is possible if FK constraint is trusted (FK constraint is enabled and isn’t marked [NOT FOR REPLICATION]).
    5. tempdb.
  2. When join’s type is OUTER JOIN. For simplicity, I will consider only the LEFT JOIN: dbo.ChildTable LEFT [OUTER] JOIN dbo.ParentTable ON …:
    1. There is no unique constraint / primary key constraint / unique index on parent table defined on join’s columns.

Example for 1.3

Code Snippet
– Test #2: what happens if FK constraint isn't enabled properly?
– I disable the FK constraint.
ALTER TABLE dbo.SalesOrder
NOCHECK CONSTRAINT FK_SalesOrder_CustomerID;
GO

SELECT fk.is_disabled, fk.is_not_for_replication, fk.is_not_trusted FROM sys.foreign_keys fk
WHERE fk.name = N'FK_SalesOrder_CustomerID';
GO
/*
is_disabled is_not_for_replication is_not_trusted
———– ———————- ————–
1           0                      1
*/

SELECT    COUNT(*)
FROM    dbo.SalesOrder so INNER JOIN dbo.Customer c ON so.CustomerID = c.CustomerID
GO

– Test #2 Disabling and enabling FK constraint.
– What happens if FK constraint is enabled but is not trusted and is not marked [NOT FOR REPLICATION]?
– FK constraint is enabled and optimization is allowed
SELECT fk.is_disabled, fk.is_not_for_replication, fk.is_not_trusted FROM sys.foreign_keys fk
WHERE fk.name = N'FK_SalesOrder_CustomerID';
GO
/*
is_disabled is_not_for_replication is_not_trusted
———– ———————- ————–
0           0                      0
*/

– We disable the FK constraint
ALTER TABLE dbo.SalesOrder
NOCHECK CONSTRAINT FK_SalesOrder_CustomerID;
GO

– Now, the FK constraint is disabled and becomes not trusted. Optimization is not possible.
SELECT fk.is_disabled, fk.is_not_for_replication, fk.is_not_trusted FROM sys.foreign_keys fk
WHERE fk.name = N'FK_SalesOrder_CustomerID';
GO
/*
is_disabled is_not_for_replication is_not_trusted
———– ———————- ————–
1           0                      1
*/
SELECT    COUNT(*)
FROM    dbo.SalesOrder so INNER JOIN dbo.Customer c ON so.CustomerID = c.CustomerID
GO

The execution plans includes data access operator for both tables:

Foreign.key.join.elimination.test2

Code Snippet
– I enable the FK constraint without data validation. FK constraint remains not trusted. Optimization is not possible.
ALTER TABLE dbo.SalesOrder
CHECK CONSTRAINT FK_SalesOrder_CustomerID;
GO

SELECT fk.is_disabled, fk.is_not_for_replication, fk.is_not_trusted FROM sys.foreign_keys fk
WHERE fk.name = N'FK_SalesOrder_CustomerID';
GO
/*
is_disabled is_not_for_replication is_not_trusted
———– ———————- ————–
0           0                      1
*/

SELECT    COUNT(*)
FROM    dbo.SalesOrder so INNER JOIN dbo.Customer c ON so.CustomerID = c.CustomerID
GO

The execution plan is the same:

Foreign.key.join.elimination.test2

Code Snippet
– Solution: I enabled (CHECK) FK constraint with data validation (WITH CHECK). Thus, FK constraint becomes trusted.
ALTER TABLE dbo.SalesOrder
WITH CHECK CHECK CONSTRAINT FK_SalesOrder_CustomerID;
GO

SELECT fk.is_disabled, fk.is_not_for_replication, fk.is_not_trusted FROM sys.foreign_keys fk
WHERE fk.name = N'FK_SalesOrder_CustomerID';
GO
/*
is_disabled is_not_for_replication is_not_trusted
———– ———————- ————–
0           0                      0
*/

SELECT    COUNT(*)
FROM    dbo.SalesOrder so INNER JOIN dbo.Customer c ON so.CustomerID = c.CustomerID
GO

And the execution plan shows only one data access operator:

Foreign.key.join.elimination.test1

Script

To check what FK constraints allows FK join elimination optimization I wrote this script:

Code Snippet
SELECT    fk.name, fk.is_disabled, fk.is_not_for_replication, fk.is_not_trusted, ca.ColumnCount, ca.NullableColumnCount,
        CASE WHEN fk.is_not_trusted = 0 AND ca.ColumnCount = 1 AND ca.NullableColumnCount = 0 AND DB_NAME() <> 'tempdb' THEN 1 ELSE 0 END AS FK_JoinEliminationIsPossible
FROM    sys.foreign_keys fk
INNER JOIN (
        SELECT    fkcol.constraint_object_id,
                COUNT(*) AS ColumnCount,
                SUM(CONVERT(TINYINT, col.is_nullable)) AS NullableColumnCount
        FROM    sys.foreign_key_columns fkcol
        INNER JOIN sys.columns col ON fkcol.parent_object_id = col.object_id AND fkcol.parent_column_id = col.column_id
        GROUP BY fkcol.constraint_object_id
) ca ON fk.object_id = ca.constraint_object_id
–WHERE fk.name = N'FK_SalesOrder_CustomerID'
GO

2014-03-26

SQl Server: optimizarea interogarilor SQL

Filed under: Uncategorized — ---- @ 9:03 AM

SQl Server: optimizarea interogarilor SQL (SARG) @ softbinator {ts ‘2014-03-26 19:50:00.000′}

2014-02-12

Diviziunea relațională

Filed under: Uncategorized — ---- @ 8:52 AM

1) PPTX + scripturi SQL + funcție SQL CLR download 

Funcția SQL CLR DivideBy este prezentată doar cu titlu de exemplu. Nu este optimizată și nu este pe deplin testată.

2) Exemplu de utilizare a funcției DivideBy:

Code Snippet
SELECT    *
FROM    dbo.DivideBy('dbo.Rezultat', 'dbo.Disciplina', 'IdStudent', 'IdDisciplina') dr;
/*
Id
–
1
2
3
*/

SELECT    s.IdStudent, s.Nume, s.Prenume
FROM    dbo.DivideBy('dbo.Rezultat', 'dbo.Disciplina', 'IdStudent', 'IdDisciplina') dr
JOIN    dbo.Student s ON dr.Id = s.IdStudent;
/*
IdStudent Nume  Prenume
——— —– ———-
1         A     Alexandru
2         B     Bogdan
3         C     Constantin
*/

3) Resurse:

Divided We Stand: The SQL of Relational Division

Proper Relational Division With Sets

 

Mulțumesc

2014-02-10

How to avoid SQL injections: one simple solution

Filed under: Uncategorized — ---- @ 8:30 AM

SQL injections are one of the top threats for web applications. This kind of attack is possible if application concatenates and executes strings received from the end-users. This article shows a simple technique to avoid this attacks: [at database level] instead of receiving (and concatenating) strings (for example column’s or table’s name) from the client app it receives only integers. Every column will have an INT identifier associated.

Example:

If the column Name included into Person.PhoneNumberType could have the ID 4

Code Snippet
CREATE TABLE dbo.AllowedTable (
    IDAllowedTable INT PRIMARY KEY,
    TableSchema SYSNAME NOT NULL,
    TableName SYSNAME NOT NULL,
        UNIQUE (TableSchema,TableName),
    Caption NVARCHAR(50) NOT NULL – This is what end-user will see
);
INSERT    INTO dbo.AllowedTable
        (IDAllowedTable, TableSchema, TableName, Caption)
VALUES    (1, N'Person', N'AddressType', 'Type of address'),
        (2, N'Person', N'PhoneNumberType', 'Type of phone number');

CREATE TABLE dbo.AllowedColumn (
    IDAllowedColumn INT PRIMARY KEY, – This is what client-all will send to SQL Server
    IDAllowedTable INT NOT NULL REFERENCES dbo.AllowedTable(IDAllowedTable),
    ColumnName SYSNAME NOT NULL,
        UNIQUE(IDAllowedTable, ColumnName),
    IsPKColumn BIT NOT NULL,
    Caption NVARCHAR(50) NOT NULL – This is what end-user will see
);
– Every table has a simple PK (single column)
CREATE UNIQUE INDEX IUF_AllowedColumn_IDAllowedTable_WhereIsPKColumnIs1
ON dbo.AllowedColumn(IDAllowedTable)
WHERE IsPKColumn = 1;
GO
INSERT    INTO dbo.AllowedColumn
        (IDAllowedColumn, IDAllowedTable, ColumnName, IsPKColumn, Caption)
VALUES    (1, 1, N'AddressTypeID', 1, 'ID'),
        (2, 1, N'Name', 0, 'Name'),
        (3, 2, N'PhoneNumberTypeID', 1, 'ID'),
        (4, 2, N'Name', 0, 'Name'),
        (5, 2, N'ModifiedDate', 0, 'The last modification date');
GO

then if the client app needs a list of all values from this column it could execute a stored procedure having an INT parameter instead of [N]VARCHAR parameter:

Code Snippet
EXEC dbo.ExecuteDynamicQuery @IDColumn = 4;

Code Snippet
CREATE PROCEDURE dbo.ExecuteDynamicQuery
(
    @IDColumn INT – Client app will send only the ID of object (the id of column in this example)
)
AS
BEGIN
    DECLARE    @IDTable INT, @SecondColumnName SYSNAME;
    
    SELECT    @IDTable    = ac.IDAllowedTable,
            @SecondColumnName = ac.ColumnName
    FROM    dbo.AllowedColumn ac
    WHERE    ac.IDAllowedColumn = @IDColumn;

    IF @IDTable IS NULL – This will check if the source table exists
        RAISERROR('Error', 16, 1); – Object not found
    ELSE
    BEGIN
        DECLARE  @PkColumnName SYSNAME;
        SELECT    @PkColumnName = ac.ColumnName
        FROM    dbo.AllowedColumn ac
        WHERE    ac.IDAllowedTable = @IDTable
        AND        ac.IsPKColumn = 1;

        DECLARE @SqlStatement NVARCHAR(MAX);
        SELECT    @SqlStatement =
                N'SELECT ' + QUOTENAME(@PkColumnName)
                + N' , ' + QUOTENAME(@SecondColumnName)
                + N' FROM '  + QUOTENAME(at.tableSchema) + N'.' + QUOTENAME(at.TableName)
        FROM    dbo.AllowedTable at
        WHERE    at.IDAllowedTable = @IDTable;

        PRINT @SqlStatement;

        EXEC sp_executesql @SqlStatement;
    END
END;
GO

First, this stored procedure will validate the value of parameter (the column’s ID: @IDColumn INT) and then if the requested ID exists will generate and execute a SQL statement.

Usage:

Code Snippet
EXEC dbo.ExecuteDynamicQuery @IDColumn = 100;
/*
Msg 50000, Level 16, State 1, Procedure ExecuteDynamicQuery, Line 16
Error
*/
EXEC dbo.ExecuteDynamicQuery @IDColumn = NULL;
/*
Msg 50000, Level 16, State 1, Procedure ExecuteDynamicQuery, Line 16
Error
*/
EXEC dbo.ExecuteDynamicQuery @IDColumn = 2;
/*
Executed @SqlStatement:
SELECT [AddressTypeID] , [Name] FROM [Person].[AddressType]
*/
EXEC dbo.ExecuteDynamicQuery @IDColumn = 4;
/*
Executed @SqlStatement:
SELECT [PhoneNumberTypeID] , [Name] FROM [Person].[PhoneNumberType]
*/

Note: Microsoft has published a comprehensive article on this subject here .

2013-12-01

Filtered indexes and parameters

Filed under: Uncategorized — ---- @ 8:32 PM

SQL Server 2008 introduced filtered indexes.

While there are scenarios were filtered indexes are a great solution there is a simple scenario where SQL Server can’t use these type of indexes (for a good reason): predicates with variables/parameters.

Example (I used Adventure Works sample database for SQL Server 2012). If we create the following filtered index for some queries that shows the sales orders having SubTotal >= 100000:

Code Snippet
USE AdventureWorks2012;
GO
CREATE INDEX INF_SalesOrderHeader_SubTotal
ON Sales.SalesOrderHeader(SubTotal)
INCLUDE (OrderDate)
– Only 54 orders from 31564 orders have SubTotal greather than 100000
WHERE SubTotal >= 100000;
GO

then the execution plans of the following query

Code Snippet
– Query #1
SELECT    OrderDate, SubTotal
FROM    Sales.SalesOrderHeader h
WHERE    h.SubTotal >= 105000
GO

will include an Index Seek on this index:

01.Execution.Plan.Predicate.With.ConstantBut if we rewrite this  query to use a variable / parameter instead of constant:

Code Snippet
– Query #2
DECLARE @p MONEY = 105000
SELECT    OrderDate, SalesOrderID
FROM    Sales.SalesOrderHeader h
WHERE    h.SubTotal >= @p
GO

then the execution plan will include an index scan on clustered index PK_SalesOrderHeader_SalesOrderID because the execution plan must be generated for all values of @p (less than 100000 or greater than 100000):

02.Execution.Plan.Predicate.With.Parameter

What solutions do we have if we want to show only those orders with SubTotal >= 100000 but the the minimum subtotal must be a variable / parameter ?

The usual solution for this problem is to simple add the RECOMPILE query hint thus:

Code Snippet
– Query #3
DECLARE @p MONEY = 105000
SELECT    OrderDate, SalesOrderID
FROM    Sales.SalesOrderHeader h
WHERE    h.SubTotal >= @p
OPTION (RECOMPILE)
GO

03.Execution.Plan.Predicate.With.Parameter.RECOMPILE

But what if we want to benefit from execution plan caching ? The solution is to add the predicate from WHERE clause of CREATE INDEX statement within WHERE clause of SELECT statement thus WHERE CreateIndexWherePredicate AND PredicateWithVariable:

Code Snippet
– Query #4
DECLARE @p MONEY = 105000
SELECT    OrderDate, SalesOrderID
FROM    Sales.SalesOrderHeader h
WHERE    h.SubTotal >= 100000  AND h.SubTotal >= @p
GO

04.Execution.Plan.Predicate.With.Parameter.Without.RECOMPILE

This way SQL Server knows that it must shows always those orders having SubTotal >= 100000 or greater and thus it can use safely the filtered index.

Note: here can be found all predicates (IN, IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<) allowed within WHERE clause of CREATE INDEX statement.

Note #2: The execution plan for Query #2 shows an UnmatchedIndexes warning that can be found by examining the XML execution plan:

Code Snippet
            <UnmatchedIndexes>
              <Parameterization>
                <Object Database="[AdventureWorks2012]" Schema="[Sales]" Table="[SalesOrderHeader]" Index="[INF2_SalesOrderHeader_SubTotal]" />
              </Parameterization>
            </UnmatchedIndexes>
            <Warnings UnmatchedIndexes="true" />

Note #3: You can detect these UnmatchedIndexes warnings also by using an Extended Event session and sqlserver.unmatched_filtered_indexes event:

Code Snippet
CREATE EVENT SESSION [XEWarnings] ON SERVER
ADD EVENT sqlserver.unmatched_filtered_indexes(
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text)
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2012'))
ADD TARGET package0.ring_buffer
GO

2013-11-26

Dev.Connect presentation: The usage of SQL Server extended events to troubleshoot issues

Filed under: Uncategorized — ---- @ 1:11 AM

PowerPoint + demo scripts

My demo is based on these resources:

http://www.sqlskills.com/blogs/jonathan/tracking-problematic-pages-splits-in-sql-server-2012-extended-events-no-really-this-time/

http://www.sqlskills.com/blogs/paul/can-guid-cluster-keys-cause-non-clustered-index-fragmentation/

I used also the following resources:

http://www.sqlskills.com/blogs/jonathan/category/extended-events/ 

http://technet.microsoft.com/en-us/magazine/2009.01.sql2008.aspx?pr=blog

http://sqlblog.com/blogs/michael_zilberstein/archive/2011/04/25/35175.aspx

2013-10-31

Dev.Connect 2013

Filed under: Uncategorized — ---- @ 12:02 AM

Concurs: Participare gratuita la Dev.Connect

Dev.Connect.Logo

2013-10-30

SQL Server 2014: inline non-unique indexes

Filed under: index,sql server,sql server 2014,Uncategorized — ---- @ 7:49 AM

SQL Server 2014 (download CTP 2) has new T-SQL features. For example you can create inline non-unique indexes. Till SQL Server 2014 the only option to create inline indexes was to define PRIMARY KEY [NON]CLUSTERED or UNIQUE [NON]CLUSTERED constraints because SQL Server maintains these primary key/unique constraints by creating unique indexes (see section “PRIMARY KEY or UNIQUE constraint” from Create Unique Indexes).

Example:

Code Snippet
CREATE TABLE dbo.SalesOrder
(
    SalesOrderID INT IDENTITY(1,1),
        – Old method to create an CLUSTERED unique index
        CONSTRAINT PK_SalesOrder_SalesOrderID PRIMARY KEY CLUSTERED (SalesOrderID),
    OrderNumber CHAR(8) NOT NULL,
        – Old method to create an NON-CLUSTERED unique index
        CONSTRAINT UN_SalesOrder_OrderNumber UNIQUE(OrderNumber),
    – [New] This will create a non-unique index on (OrderDate)
    OrderDate DATE NOT NULL INDEX IX_SalesOrder_1 NONCLUSTERED ,
    CustomerID INT NOT NULL,
        – [New] This will create a non-unique index on (CustomerID, OrderDate)
        INDEX IX_SalesOrder_2 NONCLUSTERED (CustomerID, OrderDate)
);

Next Page »

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.