Yet another SQL Server enthusiast

2016-06-05

Trolling Ora

Filed under: Uncategorized — ---- @ 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 — ---- @ 1:11 AM

ITdevConnect

ITDC16

2015-06-22

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

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

2015-06-21

When a query plan goes bad

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

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

2015-05-08

How to find execution plans with warnings and missing indexes

Filed under: execution plan,index,sql server 2012,sql server 2014,xquery — ---- @ 11:34 PM

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 — ---- @ 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 — ---- @ 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 — ---- @ 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 — ---- @ 10:58 PM

Fun.With.SQL.Profiler.Don't

Next Page »

Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.