Yet another SQL Server enthusiast

2015-06-22

SELECT {d ‘2015-06-20’} [RoSQL Presentation]

Filed under: sql server — sa @ 6:58 AM

2015-06-21

When a query plan goes bad

Filed under: sql server — sa @ 9:03 PM

Source: http://dbareactions.com/post/122027631923/when-a-query-plan-goes-bad

2015-04-23

SQL Server: autocommit transactions vs. explicit transactions. A simple test.

Hypothesis

Encapsulation of several SQL DML statements like INSERT, UPDATE, DELETE, MERGE in a single transaction can be beneficial for performance compared with approach which doesn’t uses explicit transactions.

Test

To test above hypothesis I have created a small table named dbo.SalesOrder and two batches that insert 100.000 rows (per batch):

  • First batch uses autocommit transactions (in this context, for every INSERT statement SQL Server will create a transaction that will be committed or rolled back when statement execution completes).
  • Second batch uses explicit transactions to encapsulate every five INSERT statements in a single transaction.
Code Snippet
CREATE TABLE dbo.SalesOrder (
    SalesOrderID    INT IDENTITY(1,1) PRIMARY KEY,
    OrderDate        DATE NOT NULL,
    FillerColumn    VARCHAR(100) NOT NULL
);
GO

TRUNCATE TABLE dbo.SalesOrder
GO

— First batch
DECLARE @i INT = 1;
WHILE @i <= 100000
BEGIN
    INSERT    dbo.SalesOrder (OrderDate, FillerColumn)
    VALUES    (GETDATE(), REPLICATE('*', 100));

    SET @i += 1;
END;
GO

— Second batch
DECLARE @i INT = 0;
BEGIN TRANSACTION;

WHILE @i < 100000
BEGIN
    INSERT    dbo.SalesOrder (OrderDate, FillerColumn)
    VALUES    (GETDATE(), REPLICATE('*', 100));

    SET @i += 1;
    IF @i % 5 = 0
    BEGIN
        COMMIT TRANSACTION;
        BEGIN TRANSACTION;
    END
END;
IF @@TRANCOUNT > 0
    COMMIT
GO

Test’s results

To measure the performance of every batch I have used two performance counters:

  • Log Bytes Flushed/sec
  • Log Flushes/sec (number of I/O operations per second)

TX.performance.AutoCommit.vs.Explicit.Transactions. As you can see in this simple test, encapsulation of several DML statement in a single transaction increased the volume of data written to disk / second (Log Bytes Flushed/sec) while the number of physical write operations into database log (Log Flushes/sec) and the execution time decreased.

Note #1: the main reasons for using transactions should be the ACID properties but the way we manage the D(durability) can have significant impact on database performance.

Note #2: this test is simple. Wasn’t my goal to do a full performance test. You should do your own tests.

2015-04-15

XQuery: Comparison operators for equality

Filed under: sql server,xml,xquery — sa @ 7:06 AM

SQL Server supports a subset of the XQuery language. One of features implemented within SQL Server XQuery are comparison operators. There are two kinds of comparison operators:

  1. Operators for sequences comparison (=, !=, <, <=, >, >=) and
  2. Operators for singletons comparison (named also scalar values; eq, ne, lt, le, gt, ge).

Two operators are available for equality comparison:  = and eq .

First operator (=) can be used to compare sequences and / or singleton values / operands. Second operator (eq) can be used only with singleton values / operands. When two sequences are compared and both sequences have at least one common value the result is true, otherwise (there is no common value) the result is false.

Example 1: sequences and = operator
Code Snippet
PRINT 'Test #1:';
DECLARE @x XML = N'';

SELECT '(10, 20, 30) = 10' AS XQuery,    @x.query('(10, 20, 30) = 10') AS Result
UNION ALL
SELECT '(10, 20, 30) = 100',            @x.query('(10, 20, 30) = 100')
UNION ALL
SELECT '(10, 20, 30) = (10)',            @x.query('(10, 20, 30) = (10)')
UNION ALL
SELECT '(10, 20, 30) = (10,30)',        @x.query('(10, 20, 30) = (10, 30)')
UNION ALL
SELECT '(10, 20, 30) = (10, 20, 30, 40)',@x.query('(10, 20, 30) = (10, 20, 30, 40)')
UNION ALL
SELECT '(10, 20, 30) = (25, 40)',        @x.query('(10, 20, 30) = (25, 40)')
/*
Output:

Test #1:
XQuery                          Result    Comment
——————————- ——    ——
(10, 20, 30) = 10               true    seq. vs value
(10, 20, 30) = 100              false    seq. vs value
(10, 20, 30) = (10)             true    seq. vs seq.
(10, 20, 30) = (10,30)          true    seq. vs seq.
(10, 20, 30) = (10, 20, 30, 40) true    seq. vs seq.
(10, 20, 30) = (25, 40)         false    seq. vs seq.
*/

Example 2: singleton values and = operator
Code Snippet
PRINT 'Test #2:';
SELECT '10 = 10'XQuery,    @x.query('10 = 10') AS Result
UNION ALL
SELECT '10 = 20',        @x.query('10 = 20')
/*
Output:

Test #2:
XQuery  Result
——- ——
10 = 10 true
10 = 20 false
*/

Example 3: sequences and eq operator
Code Snippet
PRINT 'Test #3:';
GO
DECLARE @x XML = N'';
SELECT '(10, 20, 30) eq 10'XQuery,            @x.query('(10, 20, 30) eq 10') AS Result
GO
/*
Output:

Test #3:
Msg 2389, Level 16, State 1, Line 15
XQuery [query()]: 'eq' requires a singleton (or empty sequence), found operand of type 'xs:integer +'
*/

Example 4 singleton values and eq operator
Code Snippet
DECLARE @x XML = N'';
SELECT '(10, 20, 30)[1] eq 10 'XQuery,    @x.query('(10, 20, 30)[1] eq 10') AS Result
UNION ALL
SELECT '(10, 20, 30)[1] eq 100',        @x.query('(10, 20, 30)[1] eq 100')
UNION ALL
SELECT '(10, 20, 30)[2] eq 10 ',        @x.query('(10, 20, 30)[2] eq 10')
UNION ALL
SELECT '(10, 20, 30)[2] eq 100',        @x.query('(10, 20, 30)[2] eq 100')
GO
/*
Output:

Test #4:
XQuery                 Result
———————- ——
(10, 20, 30)[1] eq 10  true
(10, 20, 30)[1] eq 100 false
(10, 20, 30)[2] eq 10  false
(10, 20, 30)[2] eq 100 false
*/

2015-04-09

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

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

Solution:

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

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

Results:

XML.Element.Full.Path

2015-03-29

How to compute number of weekends between two dates

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

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

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

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

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

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

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

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

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

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

2015-03-16

How to see variable values in SQL Profiler ?

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

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

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

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

— Test
EXEC dbo.DoSomethingWithA 123
GO

SQL Profiler output:

Raiserror.Sql.Profiler.Variavle.Values

Note: this solution should be used only for debugging.

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

2014-09-28

Simple solution to optimize bulk insert operations

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

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

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

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

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

I’ve used following scripts:

  1. First test:

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

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

Target table was created using the next script:

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

To import data I’ve used an SSIS package:

ssis.dataflowtask

Bellow are my results:

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

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

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

2014-07-08

sp_DeadlockAnalyzer v01

Filed under: concurrency,deadlock,sp_DeadlockAnalyzer,sql server — sa @ 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:

sp_DeadlockAnalyzer.Example

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 — sa @ 11:34 PM

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

Next Page »

Create a free website or blog at WordPress.com.