Yet another SQL Server enthusiast

2015-05-08

How to find execution plans with warnings and missing indexes

Starting with SQL Server 2005, execution plans include information about missing indexes and staring with SQL Server 2012 execution plans could include information regarding few warnings (ex. implicit conversions, no join predicates, ). Because both types of information are included in [estimated] execution plans and these plans are stored as XML data we can use following queries that find execution plans with warnings and with missing indexes for TOP(100) queries sorted by average logical reads:

Code Snippet
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;

IF OBJECT_ID('tempdb.dbo.#QueryPlans') IS NOT NULL
    DROP TABLE #QueryPlans;

SELECT    pa.*,
        SUBSTRING(st.[text], (qs.statement_start_offset/2) + 1, 100) AS statement_text,

        qs.execution_count, qs.creation_time, qs.last_execution_time,

        min_elapsed_time    = qs.min_elapsed_time,
        avg_elapsed_time    = CONVERT(NUMERIC(38, 4), qs.total_elapsed_time * 1. / qs.execution_count),
        max_elapsed_time    = qs.max_elapsed_time,

        min_worker_time        = qs.min_worker_time,
        avg_worker_time        = CONVERT(NUMERIC(38, 4), qs.total_worker_time * 1. / qs.execution_count),
        max_worker_time        = qs.max_worker_time,

        min_logical_reads    = qs.min_logical_reads,
        avg_logical_reads    = CONVERT(NUMERIC(38, 4), qs.total_logical_reads * 1. / qs.execution_count),
        max_logical_reads    = qs.max_logical_reads,

        min_logical_writes    = qs.min_logical_writes,
        avg_logical_writes    = CONVERT(NUMERIC(38, 4), qs.total_logical_writes * 1. / qs.execution_count),
        max_logical_writes    = qs.max_logical_writes,

        min_clr_time        = qs.min_clr_time,
        avg_clr_time        = CONVERT(NUMERIC(38, 4), qs.total_clr_time * 1. / qs.execution_count),
        max_clr_time        = qs.max_clr_time,

        min_rows            = qs.min_rows,
        avg_rows            = CONVERT(NUMERIC(38, 4), qs.total_rows * 1. / qs.execution_count),
        max_rows            = qs.max_rows,

        qs.[sql_handle], qs.plan_handle, qs.query_hash, qs.query_plan_hash
INTO    #QueryPlans
FROM    sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) st
CROSS APPLY (
    SELECT    database_name = DB_NAME(rpvt.[dbid]), object_name = OBJECT_NAME(rpvt.objectid, rpvt.[dbid])
    FROM (
        SELECT    xpa.attribute, int_value = CONVERT(INT, xpa.value)
        FROM    sys.dm_exec_plan_attributes(qs.plan_handle) xpa
        WHERE    xpa.attribute IN (N'dbid', N'objectid')
    ) spvt
    PIVOT( MAX(spvt.int_value) FOR spvt.attribute IN ([dbid], [objectid])) rpvt
) pa
OPTION(RECOMPILE);

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'
)
SELECT    TOP(100)
        XmlMissingIndexes    = qp.query_plan.query('//MissingIndexes'),
        XmlWarnings            = qp.query_plan.query('//Warnings'),
        XmlExecutionPlan    = qp.query_plan,
        t.*
FROM    #QueryPlans t
OUTER APPLY sys.dm_exec_query_plan(t.plan_handle) qp
WHERE    qp.query_plan.exist('//Warnings') = 1
OR        qp.query_plan.exist('//MissingIndexes') = 1
ORDER BY t.avg_logical_reads DESC — You may change default sort criteria thus: avg_elapsed_time, avg_worker_time, avg_logical_writes, etc.
OPTION(RECOMPILE)

Results:

SQL Server Execution Plans - Warnings - Missing Indexes

You may change default sort criteria to ORDER BY avg_elapsed_time /  avg_worker_time / another column from #QueryPlans table.

Download script

2015-05-01

eBooks from SQLCAT

Filed under: Uncategorized — sa @ 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-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-04-07

Fun with SQL Profiler and Tunning template

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

Fun.With.SQL.Profiler.Don't

2015-03-29

How to compute number of weekends between two dates

Filed under: sql server — 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.

2015-03-07

ITDevConnect 2015 / April 25 – 26

Filed under: Uncategorized — sa @ 10:18 PM

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

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

2015-03-01

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

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

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

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

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

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

Code Snippet
DECLARE    @d DATETIME = GETDATE()

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

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

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

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

Nullable.Columns.Query.Execution.Plan

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

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

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

Mandatory.Columns.Query.Execution.Plan

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

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

TLTR

When next predicates

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

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

« Previous PageNext Page »

Create a free website or blog at WordPress.com.