sql-server-2016

Articles about SQL Server 2016, In-Memory OLTP Database Engine, Windows Azure integration, AlwaysOn Availability Groups, business intellligence, data visualization tools, and more.

Finding the queries that use the most I/O

Running the SQL script given in the following listing will identify the top 20 queries that use the most I/O.

SELECT TOP 20
        DB_NAME(QT.dbid) as database_name
        , QS.total_logical_reads + QS.total_logical_writes as total_io
        , QS.execution_count   
        , SUBSTRING(QT.text, (qs.statement_start_offset/2)+1,
                ((      CASE qs.statement_end_offset
                                WHEN -1 THEN DATALENGTH(QT.text)
                                ELSE qs.statement_end_offset
                        END - QS.statement_start_offset)/2) + 1) as statement_text
        , QT.text as parent_statement_text     
        , O.name
        , O.type_desc
        , QP.query_plan
FROM
        sys.dm_exec_query_stats QS
        CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) QT
        CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) QP
        LEFT JOIN sys.objects O ON O.object_id = QT.objectid
WHERE QT.dbid = DB_ID()
ORDER BY total_io DESC

Find out waiting tasks - sys.dm_os_waiting_tasks

sys.dm_os_waiting_tasks dmv shows all threads that are currently suspended:

SELECT
        DB_NAME(ER.database_id) DatabaseName,
        OT.[session_id],
        OT.[exec_context_id],
        OT.[wait_duration_ms],
        OT.[wait_type],
        OT.[blocking_session_id],
        OT.[resource_description],
        ES.[program_name],
        ST.[text],
        ES.[cpu_time],
        ES.[memory_usage],
        QP.[query_plan]
FROM
        sys.dm_os_waiting_tasks OT
        INNER JOIN sys.dm_exec_sessions ES ON OT.[session_id] = ES.[session_id]
        INNER JOIN sys.dm_exec_requests ER ON ES.[session_id] = ER.[session_id]
        OUTER APPLY sys.dm_exec_sql_text (ER.[sql_handle]) ST
        OUTER APPLY sys.dm_exec_query_plan (ER.[plan_handle]) QP
WHERE ES.[is_user_process] = 1
ORDER BY OT.[session_id], OT.[exec_context_id];
GO

RegEx - Regular expressions for efficient querying in SQL Server

Create function in SQL Server that calls C# method in an assembly via SQL Server CLR.

C# code:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

namespace RegEXHelper
{
    public class RegExFunctions
    {
        /// <summary>
        /// Wrapper over .NET RegEx.Matches
        /// </summary>
        /// <param name="Pattern">The regular expression pattern to match.</param>
        /// <param name="MatchString">The string to search for a match.</param>        
        /// <returns></returns>
        [SqlFunction(Name = "RegexFind", IsPrecise = true, IsDeterministic = true,
            FillRowMethodName = "FillMatchRow",
            TableDefinition = "Match_ID INT, FirstIndex INT, [Length] INT, Value NVARCHAR(MAX), Submatch_ID INT, SubmatchName NVARCHAR(255), SubmatchValue NVARCHAR(MAX), Error NVARCHAR(MAX)")]
        public static IEnumerable RegexFind(SqlString Pattern, [SqlFacet (MaxSize=-1)] SqlString MatchString,
            SqlBoolean Global, SqlBoolean Multiline, SqlBoolean IgnoreCase)
        {
            if (Pattern.IsNull)
                throw new ArgumentNullException("Pattern");

            if (MatchString.IsNull)
                throw new ArgumentNullException("MatchString");

            if (Global.IsNull)
                Global = SqlBoolean.True;

            var result = new List<MatchRecord>();

            RegexOptions options = BuildRegexOptions(Multiline, IgnoreCase);
            Regex regEx = new Regex(Pattern.Value, options);
                       
            MatchCollection matches = regEx.Matches(MatchString.Value);
            Int32 numberOfMatches = Global ? matches.Count : (matches.Count>0)?1:0;

            for (Int32 matchIndex = 0; matchIndex < numberOfMatches; matchIndex++)
            {
                Match match = matches[matchIndex];
                var record = new MatchRecord
                                 {
                                     MatchId = matchIndex + 1,
                                     MatchIndex = match.Index + 1,
                                     MatchLength = match.Length,
                                     MatchValue = match.Value
                                 };
                result.Add(record);

                //Ignore first groups as it is equal with first match
                if (match.Groups.Count <= 1)
                    continue;

                for (Int32 groupIndex = 1; groupIndex < match.Groups.Count; groupIndex++)
                {
                    Group group = match.Groups[groupIndex];
                    record = new MatchRecord
                    {
                        MatchId = matchIndex + 1,
                        MatchIndex = match.Index + 1,
                        MatchLength = match.Length,
                        MatchValue = match.Value,
                        GroupId = groupIndex,
                        GroupName = regEx.GetGroupNames()[groupIndex],
                        GroupValue = group.Value                        
                    };
                       
                    result.Add(record);
                }
            }

            return result;
        }

        /// <summary>
        /// Wrapper over .NET RegEx.IsMatch method
        /// </summary>
        /// <param name="Pattern">The regular expression pattern to match.</param>
        /// <param name="MatchString">The string to search for a match.</param>
        [SqlFunction(Name = "RegexIsMatch", IsPrecise = true, IsDeterministic = true)]
        public static Boolean RegexIsMatch(SqlString Pattern, [SqlFacet(MaxSize = -1)] SqlString MatchString,
            SqlBoolean Multiline, SqlBoolean IgnoreCase)
        {
            if (Pattern.IsNull)
                throw new ArgumentNullException("Pattern");

            if (MatchString.IsNull)
                throw new ArgumentNullException("MatchString");
                           
            RegexOptions options = BuildRegexOptions(Multiline, IgnoreCase);
            Regex regEx = new Regex(Pattern.Value, options);
            return regEx.IsMatch(MatchString.Value);
        }

        /// <summary>
        /// Wrapper over .NET RegEx.Replace method
        /// </summary>
        /// <param name="Pattern">The regular expression pattern to match.</param>
        /// <param name="Replacement">The replacement string.</param>
        /// <param name="MatchString">The string to search for a match.</param>
        /// <param name="Count">The maximum number of times the replacement can occur.</param>
        [SqlFunction(Name = "RegexReplace", IsPrecise = true, IsDeterministic = true)]
        [return: SqlFacet(MaxSize = -1)]
        public static String RegexReplace(SqlString Pattern, SqlString Replacement, [SqlFacet(MaxSize = -1)] SqlString MatchString,
            SqlInt32 Count, SqlBoolean Multiline, SqlBoolean IgnoreCase)
        {
            if (Pattern.IsNull)
                throw new ArgumentNullException("Pattern");

            if (MatchString.IsNull)
                throw new ArgumentNullException("MatchString");

            if (Replacement.IsNull)
                Replacement = String.Empty;            

            if (Count.IsNull)
                Count = SqlInt32.MaxValue;

            RegexOptions options = BuildRegexOptions(Multiline, IgnoreCase);
            Regex regEx = new Regex(Pattern.Value, options);
            return regEx.Replace(MatchString.Value, Replacement.Value, Count.Value);
        }

        /// <summary>
        /// Method used by SQL Server to extract column values based on a row. See FillRowMethodName attribute name in RegexSplit method.
        /// </summary>
        public static void FillMatchRow(Object Row, out SqlInt32 MatchId, out SqlInt32 MatchIndex, out SqlInt32 MatchLength, out SqlString MatchValue,
            out Int32? GroupId, out SqlString GroupName, out SqlString GroupValue, out String Error)
        {
            MatchRecord record = (MatchRecord)Row;
            MatchId = record.MatchId;
            MatchIndex = record.MatchIndex;
            MatchLength = record.MatchLength;
            MatchValue = record.MatchValue;
            GroupId = record.GroupId;
            GroupName = record.GroupName;
            GroupValue = record.GroupValue;
            Error = record.Error;
        }
       
        private static RegexOptions BuildRegexOptions(SqlBoolean Multiline, SqlBoolean IgnoreCase)
        {
            if (Multiline.IsNull)
                Multiline = SqlBoolean.True;

            if (IgnoreCase.IsNull)
                IgnoreCase = SqlBoolean.True;

            RegexOptions options = RegexOptions.CultureInvariant;
            options |= (Multiline) ? RegexOptions.Multiline : RegexOptions.Singleline;
            if (IgnoreCase)
                options |= RegexOptions.IgnoreCase;

            return options;
        }
    }
}

T-SQL code for installing assembly, respectively for the creation of stored procedures and functions:

-- Script for installing RegEXHelper assembly
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'dbo.RegexFind'))
        DROP FUNCTION dbo.RegexFind
GO

IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'dbo.RegexIsMatch'))
        DROP FUNCTION dbo.RegexIsMatch
GO

IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'dbo.RegexReplace'))
        DROP FUNCTION dbo.RegexReplace
GO

IF EXISTS(SELECT 1 FROM sys.assemblies WHERE name = 'RegEXHelper')
        DROP Assembly RegEXHelper
GO

CREATE ASSEMBLY RegExHelper
FROM 'D:\Shared_Temp\RegEXHelper.dll'
WITH PERMISSION_SET = SAFE
GO

-- Create functions and stored procedures in SQL Server
sp_configure 'clr enabled', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

CREATE FUNCTION dbo.RegexFind
(
        @Pattern NVARCHAR(255)
        , @MatchString NVARCHAR(MAX)
        , @Global BIT = 1
        , @Multiline BIT = 1
        , @IgnoreCase BIT = 1
)
RETURNS TABLE
        (
                Match_ID INT,
                FirstIndex INT,
                [Length] INT,
                Value NVARCHAR(2000),
                Submatch_ID INT,        SubmatchName NVARCHAR(255), SubmatchValue NVARCHAR(2000),
                Error NVARCHAR(MAX)
        ) EXTERNAL NAME RegEXHelper.[RegEXHelper.RegExFunctions].RegexFind
GO

CREATE FUNCTION dbo.RegexIsMatch
(
        @Pattern NVARCHAR(255)
        , @MatchString NVARCHAR(MAX)
        , @Multiline BIT = 1
        , @IgnoreCase BIT = 1
)
RETURNS BIT EXTERNAL NAME RegEXHelper.[RegEXHelper.RegExFunctions].RegexIsMatch
GO

CREATE FUNCTION dbo.RegexReplace
(
        @Pattern NVARCHAR(255)
        , @Replacement NVARCHAR(255)
        , @MatchString NVARCHAR(MAX)
        , @Count INT = NULL    
        , @Multiline BIT = 1
        , @IgnoreCase BIT = 1
)
RETURNS NVARCHAR(MAX) EXTERNAL NAME RegEXHelper.[RegEXHelper.RegExFunctions].RegexReplace
GO

List of long running queries in SQL Server

T-SQL script that returns the queries that take the longest time to run:

SELECT TOP 20
        R.session_id, R.status, R.start_time, R.command, Q.text
FROM
        sys.dm_exec_requests R
        CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) Q
--WHERE R.status in ('runnable')
ORDER BY R.start_time

There’s also an interesting script, completely free on http://www.brentozar.com.

Pages

Subscribe to RSS - sql-server-2016