Yet another SQL Server enthusiast

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 — ---- @ 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 — ---- @ 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:

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 — ---- @ 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:

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 — ---- @ 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:

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.).

2014-07-08

sp_DeadlockAnalyzer v01

Filed under: concurrency,deadlock,sp_DeadlockAnalyzer,sql server — ---- @ 7:09 AM

 

sp_DeadlockAnalyzer is a stored procedure created with one goal: to help DBAs to debug deadlocks in SQL Server. It has one parameter: @xdl which is the XML content from [deadlock graph event] intercepted with SQL Trace (SQL Profiler). It shows information about isolation level, blocked resources, connections and T-SQL statements with their execution plans. The stored procedure should be executed in the context of the original database (ex. SimpleDeadlock in my example).

Future plans:

  1. Information about execution plan operators (and corresponding T-SQL statements) which cause every lock (see section [blocked resources]).
  2. To correlate information about statement’s type (INSERT, UPDATE, DELETE, MERGE, SELECT), table hints (ex. XLOCK), isolation levels, foreign keys.
  3. To show information about how can be deadlock solved.
  4. To test using deadlock graph events generated by SQL Trace/SQL Profiler != SQL Server 2012 (I’ve used only this version for tests) and by Extended Events sessions (including here system_health; >= SQL Server 2008).

Output:

Source code:

Code Snippet
  1. USE master;
  2. GO
  3.  
  4. SET ANSI_NULLS ON;
  5. GO
  6. SET QUOTED_IDENTIFIER ON;
  7. GO
  8.  
  9. IF NOT EXISTS (
  10.     SELECT    *
  11.     FROM    sys.procedures p
  12.     JOIN    sys.schemas s ON p.schema_id = s.schema_id
  13.     WHERE    s.name = ‘dbo’
  14.     AND        p.name = ‘sp_DeadlockAnalyzer’
  15. )
  16. BEGIN
  17.     EXEC (‘CREATE PROCEDURE dbo.sp_DeadlockAnalyzer AS SELECT 1′);
  18. END
  19. GO
  20.  
  21. ALTER PROCEDURE dbo.sp_DeadlockAnalyzer
  22. (
  23.     @xdl XML – Deadlock graph event
  24. )
  25. AS
  26. BEGIN
  27.     DECLARE @Resources TABLE (
  28.         Id                            INT IDENTITY PRIMARY KEY,
  29.         Resource_LockGranularity    SYSNAME NOT NULL,
  30.         Resource_DatabaseId            INT NOT NULL,
  31.         Resource_HoBtID                BIGINT NOT NULL,
  32.         Resource_ObjectName            SYSNAME NULL,
  33.         Resource_IndexName            SYSNAME NULL,
  34.     
  35.         ProcessOwner_Id                SYSNAME NOT NULL,
  36.         ProcessOwner_LockMode        SYSNAME NOT NULL,
  37.  
  38.         ProcessWaiter_Id            SYSNAME NOT NULL,
  39.         ProcessWaiter_LockMode        SYSNAME NOT NULL
  40.     );
  41.  
  42.     INSERT    @Resources (
  43.         Resource_LockGranularity    ,
  44.         Resource_DatabaseId            ,
  45.         Resource_HoBtID                ,
  46.         Resource_ObjectName            ,
  47.         Resource_IndexName            ,
  48.     
  49.         ProcessOwner_Id                ,
  50.         ProcessOwner_LockMode        ,
  51.  
  52.         ProcessWaiter_Id            ,
  53.         ProcessWaiter_LockMode
  54.     )
  55.     SELECT    Resource_LockGranularity= x.XmlCol.value(‘local-name(.)’,‘SYSNAME’),
  56.             Resource_Database        = x.XmlCol.value(‘@dbid’,‘INT’),
  57.             Resource_HoBtID            = x.XmlCol.value(‘@hobtid’,‘BIGINT’),
  58.             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)),
  59.             Resource_IndexName        = QUOTENAME(x.XmlCol.value(‘@indexname’,‘SYSNAME’)),
  60.  
  61.             ProcessOwner_Id            = own.XmlCol.value(‘@id’, ‘SYSNAME’),
  62.             ProcessOwner_LockMode    = own.XmlCol.value(‘@mode’, ‘SYSNAME’),
  63.  
  64.             ProcessWaiter_Id        = wtr.XmlCol.value(‘@id’, ‘SYSNAME’),
  65.             ProcessWaiter_Mode        = wtr.XmlCol.value(‘@mode’, ‘SYSNAME’)
  66.     FROM    @xdl.nodes(‘deadlock-list/deadlock/resource-list/*’) x(XmlCol)
  67.     OUTER APPLY x.XmlCol.nodes(‘owner-list/owner’) own(XmlCol)
  68.     OUTER APPLY x.XmlCol.nodes(‘waiter-list/waiter’) wtr(XmlCol);
  69.  
  70.     – Information about databases
  71.     SELECT    [Object]        = db.name + ‘ (‘ + CONVERT(NVARCHAR(11), db.database_id) + ‘)’,
  72.             [Object_Type]    = ‘database’,
  73.             IsolationLevel    = ‘*snapshot_isolation_state ‘ + db.snapshot_isolation_state_desc
  74.     FROM    sys.databases db
  75.     WHERE    db.database_id IN (SELECT r.Resource_DatabaseId FROM @Resources r)
  76.     UNION ALL
  77.     SELECT    [Object]        = x.XmlCol.value(‘(@id)[1]’, ‘SYSNAME’),
  78.             [Object_Type]    = ‘process’,
  79.             IsolationLevel    = x.XmlCol.value(‘(@isolationlevel)[1]’, ‘SYSNAME’)
  80.     FROM    @xdl.nodes(‘deadlock-list/deadlock/process-list/process’) x(XmlCol)
  81.  
  82.  
  83.     – Information about resources
  84.     SELECT    *
  85.     FROM    @Resources
  86.  
  87.     DECLARE @Processes TABLE (Id SYSNAME PRIMARY KEY);
  88.     INSERT    @Processes (Id)
  89.     SELECT    DISTINCT p.Id
  90.     FROM    @Resources r
  91.     CROSS APPLY (
  92.         SELECT    r.ProcessOwner_Id
  93.         UNION ALL
  94.         SELECT r.ProcessWaiter_Id
  95.     ) p (Id);
  96.  
  97.     – Information about serve processes / conections
  98.     SELECT    Process_Id        = x.XmlCol.value(‘(@id)[1]’, ‘SYSNAME’),
  99.             SPID            = x.XmlCol.value(‘(@spid)[1]’, ‘INT’),
  100.             IsDeadlockVictim = x.XmlCol.exist(‘(.)[(@id)[1] = (../../@victim)[1]]’),
  101.  
  102.             LogUsed            = x.XmlCol.value(‘(@logused)[1]’, ‘INT’),
  103.             TransactionName    = x.XmlCol.value(‘(@transactionname)[1]’, ‘SYSNAME’),
  104.             IsolationLevel    = x.XmlCol.value(‘(@isolationlevel)[1]’, ‘SYSNAME’),
  105.             DeadlockPriority= x.XmlCol.value(‘(@priority)[1]’, ‘SMALLINT’),
  106.         
  107.             CurrentDatabase    =  DB_NAME(x.XmlCol.value(‘(@currentdb)[1]’, ‘SMALLINT’)) + N’ (‘ + x.XmlCol.value(‘(@currentdb)[1]’, ‘NVARCHAR(5)’) + N’)’,
  108.         
  109.             ClientApp        = x.XmlCol.value(‘(@clientapp)[1]’, ‘SYSNAME’),
  110.             HostName        = x.XmlCol.value(‘(@hostname)[1]’, ‘SYSNAME’),
  111.             LoginName        = x.XmlCol.value(‘(@loginname)[1]’, ‘SYSNAME’),
  112.             InputBuffer        = x.XmlCol.query(‘inputbuf’)
  113.     FROM    @xdl.nodes(‘deadlock-list/deadlock/process-list/process’) x(XmlCol)
  114.  
  115.     – Statements & execution plan
  116.     DECLARE @Statements TABLE (
  117.         Process_Id            SYSNAME NOT NULL,
  118.         SPID                INT NOT NULL,
  119.         [Statement]            SYSNAME NOT NULL,   
  120.         [SqlHandle]            VARBINARY(64)   
  121.     );
  122.     INSERT    @Statements
  123.     SELECT    Process_Id        = x.XmlCol.value(‘(@id)[1]’, ‘SYSNAME’),
  124.             SPID            = x.XmlCol.value(‘(@spid)[1]’, ‘INT’),
  125.             [Statement]        = y.XmlCol.value(‘(@procname)[1]’, ‘SYSNAME’),
  126.             [SqlHandle]        = CONVERT(VARBINARY(64), y.XmlCol.value(‘(@sqlhandle)[1]’, ‘VARCHAR(128)’), 1)
  127.     FROM    @xdl.nodes(‘deadlock-list/deadlock/process-list/process’) x(XmlCol)
  128.     CROSS APPLY x.XmlCol.nodes(‘executionStack/frame’) y(XmlCol)
  129.  
  130.     SELECT    stm.Process_Id, stm.SPID,
  131.             Batch            = stm.[Statement],
  132.             StatementText    = SUBSTRING(src.[text], ISNULL(NULLIF(sts.statement_start_offset, -1) / 2, 1),  ISNULL((NULLIF(sts.statement_end_offset, -1) - NULLIF(sts.statement_start_offset, -1)) / 2, 200)),
  133.             DatabasebId        = pln.dbid,
  134.             DatabaseName    = plnattr.query_db_name,
  135.             ObjectId        = pln.objectid,
  136.             ObjectName        = plnattr.query_object_name,
  137.             QueryPlan        = pln.query_plan,
  138.             SqlHandle        = stm.SqlHandle,
  139.             QueryPlanHandle    = sts.plan_handle
  140.     FROM    @Statements stm
  141.     LEFT JOIN sys.dm_exec_query_stats sts ON stm.SqlHandle = sts.sql_handle
  142.     OUTER APPLY sys.dm_exec_query_plan(sts.plan_handle) pln
  143.     OUTER APPLY (
  144.         SELECT    DB_NAME(pvt.dbid) AS query_db_name, OBJECT_NAME(pvt.objectid, pvt.dbid) query_object_name
  145.         FROM (
  146.             SELECT    fplanatt.attribute, CONVERT(INT, fplanatt.value) AS id
  147.             FROM    sys.dm_exec_plan_attributes(sts.plan_handle) fplanatt    – ref: http://msdn.microsoft.com/en-us/library/ms189472.aspx
  148.             WHERE    fplanatt.attribute IN (‘dbid’, ‘objectid’)
  149.         ) srcpvt
  150.         PIVOT( MAX(srcpvt.id) FOR srcpvt.attribute IN ([dbid], [objectid]) ) pvt
  151.     ) plnattr
  152.     OUTER APPLY sys.dm_exec_sql_text(stm.SqlHandle) src
  153. END;
  154. GO

History:

2014-06-10 Small revision of source code (SUBSTRING: ISNULL, NULLIF)

2014-06-29

Cursuri .Net / ASP.NET / MVC / C# / SQL Server

Filed under: .Net,ASP.NET,C#,MVC,sql server — ---- @ 11:34 PM

ADCES –  Asociatia pentru dezvoltare , creativitate și excelență în software organizează cursuri .Net / ASP.NET / MVC / C# și SQL Server.

2014-06-22

Common table expressions and the number of executions

Filed under: common table expression,CTE,optimization,sql server — ---- @ 10:53 PM

A common usage pattern for common table expressions till SQL Server 2012 is to get the previous row values using a combination of ROW_NUMBER, CTE and self-join thus:

Code Snippet
CREATE TABLE dbo.MyTable (
    MyID INT  PRIMARY KEY,
    Filler NCHAR(3500) NOT NULL
);
GO
INSERT  MyTable (MyID, Filler)
VALUES  (11, N'A'), (22, N'B'), (33, N'C'), (44, N'D'), (55, N'E');
GO

– Test #1
WITH MyCTE
AS (
    SELECT  *, ROW_NUMBER()OVER(ORDER BY MyID) AS RowNum
    FROM    dbo.MyTable
)
SELECT  *
FROM    MyCTE crt
LEFT JOIN MyCTE prev ON crt.RowNum=prev.RowNum+1;
GO

In this case, for every row is displayed the previous row values:

Code Snippet
– current row  — — previous row –
MyID Filler RowNum MyID Filler RowNum
—- —— —— —- —— ——
11   A      1      NULL NULL   NULL
22   B      2      11   A      1
33   C      3      22   B      2
44   D      4      33   C      3
55   E      5      44   D      4

Because a common table expression is a local view, the source code is expanded within the source of caller (ex. a INSERT, UPDATE, DELETE or a SELECT statement) and the execution plan for above SELECT statement looks like this:

As you can see from above execution plan, the common table expression (MyCTE) is expanded two times: crt and prev. If we want to see how many time is executed every “instance” of MyCTE then we can inspect the value of Number of Executions property for every operator. Bellow, you can see that for Number of Executions for Clustered Index Scan for crt is 1 and for prev is 5. Basically, in this example, the prev part is executed five times: one time for every row from crt part and overall the MyCTE is executed six times:

 

It’s possible to change this behavior ? Yes, it’s possible and the answer (at least one answer) comes from changing the physical type of JOIN which in this case is LOOP / NESTED LOOPS.

I’ve changed the physical join from LOOP to HASH and MERGE JOIN and the result was that the prev part is executed, in my example, just one time and overall the MyCTE is executed two times:

Code Snippet
– Test #2
WITH MyCTE
AS (
    SELECT  *, ROW_NUMBER()OVER(ORDER BY MyID) AS RowNum
    FROM    dbo.MyTable
)
SELECT  *
FROM    MyCTE crt
LEFT HASH JOIN MyCTE prev ON crt.RowNum=prev.RowNum+1;
GO

– Test #3
WITH MyCTE
AS (
    SELECT  *, ROW_NUMBER()OVER(ORDER BY MyID) AS RowNum
    FROM    dbo.MyTable
)
SELECT  *
FROM    MyCTE crt
LEFT MERGE JOIN MyCTE prev ON crt.RowNum=prev.RowNum+1;
GO

Another aspect is, also, interesting: in this small test, the number of logical reads is smaller for HASH and MERGE JOIN than for LOOP JOIN:

 

Note #1: because of join hints (LOOP, MERGE, HASH) the join order is enforced. Don’t use these hints without proper testing your queries. The usage of these hints without carefully analyzing the execution plans can lead to suboptimal plans.

Note #2: Starting with SQL Server 2012 you can use LAG function to get previous row’s values. See also LEAD function.

2014-06-12

SQL Server: deadlocks

Filed under: concurrency,conference,sql server — ---- @ 7:01 AM

I gave a presentation at .Net Bucharest software developers meeting about deadlocks (resources, locks, blocking, deadlocks, how to simulate a simple deadlock, deadlocks detection, *.xdl – XML deadlock graph, how to minimize deadlocks).

Files: Net_User_Group_Bucharest_2014-06-10.zip

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).

(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:

 

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-08

SQL Server: SARG-able predicates and Scan operators

Filed under: index,optimization,SARG,scan,seek,sql server — ---- @ 12:15 AM

I will give a presentation at .Net Bucharest software developers meeting about SARG-able predicates and those reasons that can cause scans even there are proper indexes. For every example I will present the problem and the solutions.

Files: Net_User_Group_Bucharest_2014-04-08.zip

 

Next Page »

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

Follow

Get every new post delivered to your Inbox.