Yet another SQL Server enthusiast

2017-05-08

RoSQL 2017-05-06 Table Partitioning

Filed under: Uncategorized — admin @ 9:32 AM

 

Download *.sql scripts

2017-05-02

Moving LOB data to another filegroup

Filed under: Uncategorized — admin @ 9:19 AM

Note #1: This post is (more or less) a reminder for me.

Note #2: Original solutions are described here and here.

The problem (and a partial solution)

Sometimes, within a SQL Server database, we have to move data to another filegroup. In such cases, the simplest solution is to use CREATE INDEX statement with the ON NewFileGroup clause and also with DROP_EXISTING = ON thus:

image

This solution is available for both clustered and non-clustered indexes. In this cases, SQL Server will rebuild current index using the new filegroup (ON NewFileGroup). The drawback of this solution is that Database Engine will not move also LOB data (Large OBject data types include: old data types [n]text, image but also the new data types [n]varchar(max), varbinary(max), xml, CLR, geometry, geography, sql_variant) to new filegroup. The [main] reason of this behavior can be seen in CREATE TABLE syntax:

image

The CREATE TABLE statement includes the TEXTIMAGE_ON clause what allows us to define which filegroup should be used to store LOB data. Above solution will move only row data without moving LOB data (pages) into new filegroup. So, LOB data remain within old (TEXTIMAGE_ON) filegroup.

Note #3: For heap tables, one solution is to create a clustered index on the new filegroup and then drop it. After dropping clustered index,  the LOB data remain [stored] within new filegroup.

Final solution

The final solution is to recreate clustered index using a partition scheme. In this case, the behavior of SQL Server Database Engine is to move to target filegroup(s) (defined by partition scheme) both ROW and LOB data pages:

image

In order to create partition scheme we need a partition function. The main difference between original solution and this one is that in this case, I’ve used an empty partition function.

Demo

Initial status of ProductPhoto table (AdventureWorks2014 database):

image

Partition function (empty partition scheme), partition scheme (with all “partitions” assigned to [UserObjects] filegroup) + ProductPhoto table is recreated using the new partition scheme:

image

After these operations, the new status of ProductPhoto table is

image

but current table is partitioned. In order to remove partitioning (partition scheme) of current table the simplest solution is to recreate (again) the main table structure (clustered index) on the new filegroup:

image

And the final status of ProductPhoto table (unpartitioned) is:

image

2016-06-05

Trolling Ora

Filed under: Uncategorized — admin @ 3:08 PM

CREATE DATABASE Scott;

GO

SQL Server on Linux Ubuntu - First preview

SQL Server on Linux Ubuntu – First preview

Source

2016-03-16

ITdevConnect 2016

Filed under: Uncategorized — admin @ 1:11 AM

ITdevConnect

ITDC16

2015-05-01

eBooks from SQLCAT

Filed under: Uncategorized — admin @ 7:58 AM

On September 1, 2013 SQL Server Customer Advisory Team published three eBooks:

  1. SQLCAT’s Guide to Relational Engine
  2. SQLCAT’s Guide to High Availability and Disaster Recovery
  3. SQLCAT’s Guide to BI and Analytics

Interesting chapters from SQLCAT’s Guide to Relational Engine:

  • DBCC Checks and Terabyte-Scale Databases 4
  • Scheduling Sub-Minute Log Shipping in SQL Server 2008 10
  • SQL DMVStats Toolkit 24
  • Top SQL Server 2005 Performance Issues for OLTP Applications 78
  • Table-Valued Functions and tempdb Contention 80
  • Resolving PAGELATCH Contention on Highly Concurrent INSERT Workloads 95
  • SQL Server Indexing: Using a Low-Selectivity BIT Column First Can Be the Best Strategy 99
  • Bulk Loading Data into a Table with Concurrent Queries 135
  • Eliminating Deadlocks Caused By Foreign Keys with Large Transactions 174
  • Storage Top 10 Best Practices 225

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/

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-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' 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 — admin @ 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     (8194443284a0)
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           72057594043695104    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 resource (8194443284a0) 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 — admin @ 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

Next Page »

Create a free website or blog at WordPress.com.