Departure of a SQL Server Administrator – What to Check?

A SQL Server administrator has left. But what does that actually mean? What do we need to check to make sure that things will continue to work after their Active Directory account is disabled?

Some things to check are:

  • SQL Database items
    • Database ownership
    • SQL Scheduled Task ownership
    • Server role membership
    • Database role membership
    • object ownership
  • Windows items
    • Windows Scheduled Tasks
    • Ownership / login ID for Windows Services
    • Local Group Membership
    • File Share Ownership

A few things to note:

  • This collection of script merely identifies issues
  • Don’t blindly implement fixes without proper testing and without a rollback – you may run into trouble
  • This collection is not complete

SQL Server

Database ownership

Ideally, databases would be owned by some low-level non-sa account which is disabled, and solely exists to avoid these problems. This is not an ideal world, so we need to check for databases owned by our departing administrator:

DECLARE @LeaverPrincipalName sysname = 'YourCorp\Hugh.Splitter';
SELECT  name,
        state_desc
FROM    sys.databases
WHERE   owner_sid =
(
    SELECT  TOP (1) sid FROM sys.server_principals WHERE name = @LeaverPrincipalName ORDER BY   name
);

Transferring database ownership should be relatively straight-forward and risk-free. Except there are certain applications that depend on the database being owned by a sysadmin, or by a particular account (sa), or by their own service account, rather than checking for membership of the db_owner role… So make sure you test your changes. You’ll need to use the ALTER AUTHORIZATION statement, as follows:

ALTER AUTHORIZATION ON DATABASE::<<DBName>> TO <<NewUserName>>;

SQL Scheduled Task Ownership

When a SQL Server agent job runs a T-SQL job step, that step is executed as the owner, rather than as the SQL Agent account or the sa account. From the documentation linked just above:

Transact-SQL job steps use the EXECUTE AS command to set the security context to the owner of the Job.

So, we need to make sure that our leaver’s account isn’t the owner of any SQL Agent jobs:

SELECT  name
FROM    msdb.dbo.sysjobs
WHERE   owner_sid =
(
    SELECT  TOP (1) sid FROM sys.server_principals WHERE name = @LeaverPrincipalName ORDER BY   name
);

Which is all well and good, except there is the possibility for it to be set to use a proxy account, so we’ll need a more complex query:

SELECT              JobName   = j.name,
                    js.step_id,
                    js.step_name,
                    JobOwner  = SUSER_SNAME(owner_sid),
                    ProxyName = p.name
FROM
                    msdb.dbo.sysjobsteps js
    INNER JOIN      msdb.dbo.sysjobs     j
                    ON js.job_id = j.job_id
    LEFT OUTER JOIN msdb.dbo.sysproxies  AS p
                    ON js.proxy_id = p.proxy_id
WHERE
                    SUSER_SNAME(owner_sid) = @LeaverPrincipalName
                    OR  p.name = @LeaverPrincipalName
ORDER BY
                    j.name,
                    step_id;

There’s a handy-looking sp for migrating job ownership en masse – sp_manage_jobs_by_login.

Server Role Membership

What server roles did the user have? This might be helpful when replicating the user’s permissions when transferring job roles to another user – except this should all be done through active directory group membership, amirite?

Assuming, though, that you’re working somewhere where granting permissions to individuals is the done thing, then you’ll want to check the results of the following:

SELECT          sprole.name   AS [SQL Server Role],
                spmember.name AS [Member Name]
FROM
                sys.server_role_members srm
    INNER JOIN  sys.server_principals   sprole
                ON sprole.principal_id = srm.role_principal_id
    INNER JOIN  sys.server_principals   spmember
                ON spmember.principal_id = srm.member_principal_id
WHERE           spmember.name = @LeaverPrincipalName;

Database Role Membership

Similarly, you may need/want to check for membership of database roles across all databases on your server:

IF OBJECT_ID('tempdb.dbo.#DBRoleMembers', 'U') IS NOT NULL
BEGIN
    DROP TABLE #DBRoleMembers;
END;

CREATE TABLE #DBRoleMembers (DB sysname, DBRoleName sysname, MemberName sysname);

SELECT  @sql
    = N'
         USE [?];
         
         INSERT INTO #DBRoleMembers
         SELECT DB_NAME(),
                dprole.name,
                dpmember.name
         FROM sys.database_role_members drm
             inner join sys.database_principals dprole
                 ON dprole.principal_id = drm.role_principal_id
             INNER JOIN sys.database_principals dpmember
                 ON dpmember.principal_id = drm.member_principal_id
         WHERE dpmember.name = ' + QUOTENAME(@LeaverPrincipalName, '''');

DECLARE @sqlouter NVARCHAR(MAX) = N'master.sys.sp_msforeachdb @command1';
DECLARE @sqlparams NVARCHAR(MAX) = N'@command1 nvarchar(max)';

EXEC sp_executesql @sqlouter, @sqlparams, @sql;

SELECT  * FROM  #DBRoleMembers ORDER BY DB, DBRoleName, MemberName;

Object Ownership

It might be worth checking for ownership of individual objects within a database, as you won’t be able to delete the user from the database until ownership of objects has been transferred to another user.

SELECT  @sql
    = N'
CREATE TABLE #ownedobjects (DBName sysname, ObjName sysname, ObjType VARCHAR(60));
exec sp_msforeachdb ''INSERT INTO #ownedobjects SELECT ''''?'''', name, type_desc FROM [?].[sys].[objects] WHERE principal_id = (SELECT sid FROM sys.server_principals WHERE name = '''
      + QUOTENAME(@LeaverPrincipalName, '''') + N''')''
select * from #ownedobjects';

--SELECT  @sql;
EXEC (@sql);

As with databases, use the ALTER AUTHORIZATION statement to fix this.

Windows

For the Windows-based things, we’re going to drop into PowerShell

Windows Scheduled Tasks

Windows Scheduled tasks will fail when the owner account is disabled / deleted. Or, indeed, the password is changed or expired – but you don’t run jobs as a user account, do you? Oh, you do?

$username = 'YourCorp\Hugh.Splitter'
 
Write-Host "Checking ownership of Windows Scheduled Tasks"
get-scheduledtask | where-object {$_.Author -eq $username}

Oops. You might want to check those.

Windows Service Login

And, of course, you don’t use user accounts to run windows services, do you? Let’s check.

Write-Host "Checking Login for Windows Services" 
get-wmiobject win32_service  | where-object {$_.StartName -eq $username } 

Local Group Membership

It might also be worth checking to see which groups the user is in on a particular server (we’ll leave the active directory stuff to the AD team)

Write-Host "Checking Local Group Membership"
 
get-localgroup | foreach-object -process { 
if ( $(get-localgroupmember $_ | where-object {$_.name -eq $username} ) -ne $null )
{
write-host "User $username is a member of $_.name group"
}
}

File Share Ownership

What about file shares? Does your leaver own any of those? I’m not sure if it makes a difference, but it’s worth identifying these in case anyone needs to change the permissions later…

write-host "Checking File Share Ownership"
(get-smbshare | where-object{$_.Path -ne $null -and $_.Path -ne ""} | get-acl | where-object {$_.Owner -eq $username}).PSPath | Convert-Path

Just to unpick this one a little – the first filter is to remove items that don’t have a path, such as the IPC$ share; and the Convert-Path is there to transform the PSPath value of, say, Microsoft.PowerShell.Core\FileSystem::D:\Backups to a more normal-looking D:\Backups

Still to do

  • I should probably think about file ownership as well – in case anything depends on that.
  • And maybe running this all into a single script that can run against all servers / a list of servers

The big question, though – what else needs to be checked?

Finally

Do you have the passwords / know where the passwords are stored and have access to that system?

Posted in Uncategorized | Tagged , , , , , | 1 Comment

Upgrading SQL Server using Availability Groups – Checklist

This is a checklist put together and followed for an upgrade of a pair of physical SQL Server 2012 servers which hosted a single availability group of several terabytes of data with minimal downtime.

The availability group was configured with synchronous commit and automatic failover.

List of Steps

  • If possible, stop any ETL processes that put data into the affected databases – less traffic here means less traffic to catch up with when you resume the AG HADR.
  • Make sure all AGs hosted by the servers are on the same server
  • Put servers, clusters, listeners, everything into Maintenance Mode in SCOM / SQLMonitor / Solarwinds / whatever other monitoring system you use.
  • Disable Transaction Log backup jobs on all servers hosting the Availability Group(s)
  • Take Log backups
  • Configure the Availability Group(s) to manual failover
  • Suspend data movement for each database on the secondary server
  • Backup the system databases on the secondary server
  • Upgrade the secondary server and reboot if required (it might not be required, but it is recommended to do this anyway)
  • Resume data movement on the secondary, and allow the servers to synchronise.
  • Failover the availability group(s) to the upgraded server with no data loss
  • Check that data movement is suspended to the new secondary / unupgraded server due to version incompatibility
  • Upgrade other server and then reboot
  • Resume data movement on secondary, and allow to synchronise
  • (optional) failover AG with no data loss to test all is OK (this isn’t really required, but can be useful for extra peace of mind)
  • Reconfigure availability group(s) back to automatic failover (if that’s how it was)
  • Re-enable transaction log backup jobs
  • Take servers out of maintenance mode in SCOM/SQLMonitor/Solarwinds/Whatever

A couple of useful SQL queries

Suspend / Resume HADR for all databases on the server

This is the suspend version – change suspend to resume and rerun later

DECLARE @sql VARCHAR(MAX) = '';
SELECT @sql = @sql + 'ALTER DATABASE ' + QUOTENAME(DB_NAME(hars.database_id)) + ' SET HADR SUSPEND;' + CHAR(10) + CHAR(13)
FROM       sys.dm_hadr_database_replica_states AS hars
INNER JOIN sys.dm_hadr_availability_group_states AS hags
        ON hags.group_id = hars.group_id
WHERE hars.is_local = 1
ORDER BY DB_NAME(hars.database_id);
PRINT @sql; -- optional
EXEC (@sql);

Check health of replicas in the AG (run on primary)

SELECT ag.name,
ar.replica_server_name,
hars.is_local,
hars.role_desc,
hars.operational_state_desc,
hars.connected_state_desc,
hars.recovery_health_desc,
hars.synchronization_health_desc,
hars.last_connect_error_number,
hars.last_connect_error_description,
hars.last_connect_error_timestamp,
ag.failure_condition_level,
ag.health_check_timeout,
ar.replica_server_name,
ar.owner_sid,
ar.endpoint_url,
ar.availability_mode_desc,
ar.failover_mode_desc,
ar.session_timeout,
ar.primary_role_allow_connections_desc,
ar.secondary_role_allow_connections_desc,
ar.backup_priority,
ag.automated_backup_preference_desc,
ar.read_only_routing_url
FROM sys.dm_hadr_availability_replica_states AS hars
INNER JOIN sys.availability_groups AS ag
ON ag.group_id = hars.group_id
INNER JOIN sys.availability_replicas AS ar
ON ar.group_id = ag.group_id
AND ar.replica_id = hars.replica_id;

Monitor progress of catchup/synchronisation (run on primary)

SELECT ag.name,
adc.database_name,
ar.replica_server_name,
hdrs.is_local,
hdrs.synchronization_state,
hdrs.synchronization_state_desc,
hdrs.is_commit_participant,
hdrs.synchronization_health_desc,
hdrs.database_state_desc,
hdrs.is_suspended,
hdrs.suspend_reason,
hdrs.suspend_reason_desc,
hdrs.last_sent_time,
hdrs.last_received_time,
hdrs.last_hardened_time,
hdrs.last_redone_time,
hdrs.log_send_queue_size,
hdrs.log_send_rate,
hdrs.redo_queue_size,
hdrs.redo_rate,
hdrs.filestream_send_rate,
CEILING(hdrs.redo_queue_size / hdrs.redo_rate) / 60.0 AS est_redo_completion_minutes, -- approximately
hdrs.last_commit_time,
hdrs.low_water_mark_for_ghosts,
hdrs.secondary_lag_seconds -- for SQL 2016 and above
FROM sys.dm_hadr_database_replica_states AS hdrs
INNER JOIN sys.availability_groups AS ag
ON ag.group_id = hdrs.group_id
INNER JOIN sys.availability_databases_cluster AS adc
ON adc.group_database_id = hdrs.group_database_id
INNER JOIN sys.availability_replicas AS ar
ON ar.replica_id = hdrs.replica_id
WHERE hdrs.log_send_queue_size <> 0
OR hdrs.redo_queue_size <> 0;
Posted in Uncategorized | Tagged , , , , , , | 3 Comments

SQL Server Configuration on Windows Server Core Edition

You can’t run SQL Server Configuration Manager on a Windows Core server – no UI.

You can’t run SQL Server Configuration Manager on another server and direct it to point to your server running Windows Core.

But what you can do is run Computer Management from your full-fat server, and connect that to your Core server to do stuff there. And one of the things you can do is run the SQL configuration tools…

How has it taken me so long to find that out? Oh yes – I hardly ever *see* a Windows server core edition installation. Probably in part because of this sort of thing. Heigh ho.

Posted in Uncategorized | Tagged , | 2 Comments

Postcode Data and the Geography of a Drunkard’s Walk in SQL Server

I was thinking about postcode data, as it might come in handy for some reporting / planning / analysis work later.

There’s a set of scripts to create tables and populate with postcode data in SQL Server, which was my starting point.

A note for those from outside the UK – the UK postcode system breaks down into a few letters and numbers, generally of the form AAXX XAA (where A is a letter, and X is a digit). The first part is known as the outbound code – the letters are (generally) related to a major city in the area – NE for Newcastle, LS for Leeds, OX for Oxford, for examples); the one or two digits that follow represent an area in that town or, in more rural areas, this could be a “post town” – for example the IP postcode area. Here, the IP represents Ipswich, in Suffolk, but the whole postcode area covers most of Suffolk as well as some parts of Norfolk…

The code for that is relatively straightforward:

DECLARE @Line VARCHAR(MAX);

SELECT  @Line = STUFF(
                (
                    SELECT      ', ' + CAST(lng AS VARCHAR(20)) + ' ' + CAST(lat AS VARCHAR(20))
                    FROM        dbo.outcodepostcodes
                    WHERE       outcode LIKE 'IP%' and lat <> 0
                    ORDER BY    CAST(STUFF(outcode, 1, 2, '') AS INT)
                    FOR XML PATH('')
                ),
                1,
                2,
                ''
                     );

SELECT  geography::Parse('LINESTRING(' + @line + ')');

Something to note – the lat <> 0 bit – The UK has “non-geographic” postcodes as well as the normal ones. These, obviously, have no positional information, and, in this data source, that’s represented by 0 values in the lat and lng fields.
Also, I’ve switched to the Mercator projection. If you use the default (equirectangular) projection option, it’s going to look wrong unless the data you’re mapping is very close to the equator.

Now, knowing most of the above, I wondered what it would look like if you drew a line from the center of each postcode outbound code to the next one for each post town. In numerical order. It turns out that the IP code looks like this:

A bit of a drunkard’s walk… and the other areas are similar, and don’t really look much like anything. Until you come to put them all on one result set:

Looks familiar?

UK Postcode Map Query Code

You want the code for this, because of course you do…

DECLARE @outcodemap TABLE (outcode VARCHAR(9), map VARCHAR(MAX));

DECLARE @oc AS VARCHAR(9);
DECLARE @map VARCHAR(MAX);

DECLARE o CURSOR FORWARD_ONLY READ_ONLY FOR
    SELECT  DISTINCT
            LEFT(outcode, CASE WHEN outcode LIKE '[A-Z][0-9]%' THEN 1 ELSE 2 end)
    FROM    dbo.outcodepostcodes
    WHERE
            lat <> 0
            AND
            (
                outcode LIKE '[A-Z][A-Z][0-9]'
                OR  outcode LIKE '[A-Z][A-Z][0-9][0-9]'
                OR  outcode LIKE '[A-Z][0-9]'
                OR  outcode LIKE '[A-Z][0-9][0-9]'
            );

OPEN o;

FETCH NEXT FROM o
INTO @oc;

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT  @map = STUFF(
                   (
                       SELECT   ', ' + CAST(lng AS VARCHAR(20)) + ' ' + CAST(lat AS VARCHAR(20))
                       FROM     dbo.outcodepostcodes
                       WHERE
                                lat <> 0
                                AND (outcode LIKE @oc + '[0-9]' OR outcode LIKE @oc + '[0-9][0-9]')                                
                       ORDER BY CASE
                                    WHEN outcode LIKE '[A-Z][0-9]%' THEN
                                        CAST(STUFF(outcode, 1, 1, '') AS INT)
                                ELSE
                                    CAST(STUFF(outcode, 1, 2, '') AS INT)
                                END
                       FOR XML PATH('')
                   ),
                   1,
                   2,
                   ''
                        );

    INSERT INTO @outcodemap (outcode, map) VALUES ( @oc, @map );

    FETCH NEXT FROM o
    INTO @oc;
END;

CLOSE o;
DEALLOCATE o;

SELECT  outcode,
        CASE
            WHEN CHARINDEX(',', map) > 0 THEN
                geography::Parse('LINESTRING(' + map + ')')
            WHEN DATALENGTH(TRIM(map)) > 1 THEN
                geography::Parse('POINT(' + map + ')') -- just in case...
        END
FROM    @outcodemap;

Again, there are a couple of things to note – the code could probably do with being tidied up, and I’m ignoring those odd postcodes which have AAXA or AAXXA as the outbound. Yeah, the UK postcode system is kinda messed up in places!

Posted in Uncategorized | Tagged , , , | Leave a comment

Wrapping up CPU Load Graphs into a Stored Procedure

After the fun of the last few days, I thought it might be a better idea if I wrapped this all up into a single stored procedure. So I did that.

Parameters

I have implemented the following parameters:

  • @ShowCPUAsLines (BIT, default 1) – show line graphs rather than polygons. Set to 0 if you want polygons
  • @ShowDeadlock (BIT, default 1) – show Deadlock information. Set to 0 to suppress
  • @DeadlockBlobRadius (TINYINT, default 1) – radius of deadlock circles on the graph. If zero, then will be displayed as a point rather than as a circle, but that’s pretty much unreadable…
  • @OutputDeadlockTable (BIT, default 1) – outputs the deadlock information table used internally. Note that this will return information about all the deadlocks that have been identified from the logs, not just the ones shown
  • @ShowEndBar (BIT, default 1) – if you don’t like that “Here’s where we are now” bar, set to zero
  • @EndBarWidth (TINYINT, default 1) – width of that now / end bar. If zero, then a line is drawn rather than a bar

The code

IF OBJECT_ID('dbo.PerformanceGraph', 'P') IS NULL
BEGIN
    EXEC ('CREATE PROCEDURE dbo.PerformanceGraph AS BEGIN SELECT 1 END');
END;
GO


ALTER PROCEDURE PerformanceGraph
    @ShowCPUAsLines      BIT     = 1,
    @ShowDeadlock        BIT     = 1,
    @DeadlockBlobRadius  TINYINT = 1,
    @OutputDeadlockTable BIT     = 1,
    @ShowEndBar          BIT     = 1,
    @EndBarWidth         TINYINT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ts_now BIGINT =
            (
                SELECT      TOP (1)
                            cpu_ticks / (cpu_ticks / ms_ticks)
                FROM        sys.dm_os_sys_info WITH (NOLOCK)
                ORDER BY    cpu_ticks DESC
            );
    DECLARE @CPUSQLOut   AS VARCHAR(8000),
            @CPUIdleOut  AS VARCHAR(8000),
            @CPUOtherOut AS VARCHAR(8000);
    DECLARE @CPURecords AS INT;
    DECLARE @DeadlockPoints  AS VARCHAR(8000),
            @DeadlockBlobs   AS VARCHAR(8000),
            @DeadlockRecords AS INT;

    DECLARE @OutputGeometry TABLE (ShapeInfo GEOMETRY, Caption VARCHAR(20));

    WITH
    CPULoad AS
    (
        SELECT      TOP (256)
                    y.SQLProcessUtilization                              AS [SQL Server Process CPU Utilization],
                    y.SystemIdle                                         AS [System Idle Process],
                    100 - (y.SystemIdle + y.SQLProcessUtilization)       AS [Other Process CPU Utilization],
                    DATEADD(ms, -1 * (@ts_now - y.timestamp), GETDATE()) AS [Event Time]
        FROM
                    (
                        SELECT  x.record.value('(./Record/@id)[1]', 'int')                                                   AS record_id,
                                x.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')         AS SystemIdle,
                                x.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
                                x.timestamp
                        FROM
                                (
                                    SELECT  timestamp,
                                            CONVERT(XML, record) AS record
                                    FROM    sys.dm_os_ring_buffers WITH (NOLOCK)
                                    WHERE
                                            ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
                                            AND record LIKE N'%<SystemHealth>%'
                                ) AS x
                    ) AS y
        ORDER BY    y.timestamp DESC
    )
    SELECT  @CPUSQLOut
                        = STUFF(
                          (
                              SELECT        ','
                                            + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) - COUNT(*) OVER (PARTITION BY 1) AS VARCHAR(10))
                                            + ' ' + CAST(CPULoad.[SQL Server Process CPU Utilization] AS VARCHAR(20))
                              FROM          CPULoad
                              ORDER BY      CPULoad.[Event Time]
                              FOR XML PATH('')
                          ),
                          1,
                          1,
                          ''
                               ),
            @CPUIdleOut
                        = STUFF(
                          (
                              SELECT    ','
                                        + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) - COUNT(*) OVER (PARTITION BY 1) AS VARCHAR(10))
                                        + ' ' + CAST(CPULoad.[System Idle Process] AS VARCHAR(20))
                              FROM      CPULoad
                              ORDER BY  CPULoad.[Event Time]
                              FOR XML PATH('')
                          ),
                          1,
                          1,
                          ''
                               ),
            @CPUOtherOut
                        = STUFF(
                          (
                              SELECT    ','
                                        + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time])
                                               - COUNT(*) OVER (PARTITION BY 1) AS VARCHAR(10)) + ' '
                                        + CAST(CPULoad.[Other Process CPU Utilization] AS VARCHAR(20))
                              FROM      CPULoad
                              ORDER BY  CPULoad.[Event Time]
                              FOR XML PATH('')
                          ),
                          1,
                          1,
                          ''
                               ),
            @CPURecords = (SELECT   COUNT(*) FROM   CPULoad);

    IF @ShowCPUAsLines = 1
    BEGIN
        INSERT INTO @OutputGeometry (ShapeInfo, Caption)
        VALUES
        (geometry::Parse('LINESTRING(' + @CPUSQLOut + ')'), 'CPU-SQL'),
        (geometry::Parse('LINESTRING(' + @CPUOtherOut + ')'), 'CPU-Other'),
        (geometry::Parse('LINESTRING(' + @CPUIdleOut + ')'), 'CPU-Idle');
    END;
    ELSE
    BEGIN
        INSERT INTO @OutputGeometry (ShapeInfo, Caption)
        VALUES
        (geometry::STGeomFromText(
                                     'POLYGON((' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0, ' + @CPUSQLOut + ', '
                                     + '0 0, ' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0 ))',
                                     0
                                 ), 'CPU-SQL'),
        (geometry::STGeomFromText(
                                     'POLYGON((' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0, ' + @CPUOtherOut + ', '
                                     + '0 0, ' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0 ))',
                                     0
                                 ), 'CPU-Other'),
        (geometry::STGeomFromText(
                                     'POLYGON((' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0, ' + @CPUIdleOut + ', '
                                     + '0 0, ' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0 ))',
                                     0
                                 ), 'CPU-Idle');
    END;

    DECLARE @XMLDeadlocks TABLE ([XML] XML, UTCTime DATETIME, LocalTime DATETIME, MinutesAgo INT);

    IF @ShowDeadlock = 1
    BEGIN;
        WITH
        xmlsource AS
        (
            SELECT  CONVERT(XML, event_data) AS deadlock_xml
            FROM    sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
        ),
        xmldates AS
        (
            SELECT  xmlsource.deadlock_xml,
                    xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') AS deadlock_UTCtime, -- this is UTC.
                    DATEADD(
                               mi,
                               DATEDIFF(mi, GETUTCDATE(), GETDATE()),
                               xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime')
                           )                                                           AS deadlock_LocalTime
            FROM    xmlsource
            WHERE   xmlsource.deadlock_xml.value('(/event/@name)[1]', 'VARCHAR(256)') = 'xml_deadlock_report'
        )
        INSERT INTO @XMLDeadlocks ([XML], UTCTime, LocalTime, MinutesAgo)
        SELECT  xmldates.deadlock_xml,
                xmldates.deadlock_UTCtime,
                xmldates.deadlock_LocalTime,
                DATEDIFF(MINUTE, xmldates.deadlock_LocalTime, GETDATE()) AS MinutesAgo
        FROM    xmldates;

        SELECT  @DeadlockPoints  = STUFF(
                                   (
                                       SELECT   DISTINCT
                                                ', (' + CAST((0 - xml.MinutesAgo) AS CHAR(5)) + ' 0)'
                                       FROM     @XMLDeadlocks xml
                                       WHERE    xml.MinutesAgo <= @CPURecords
                                       FOR XML PATH('')
                                   ),
                                   1,
                                   2,
                                   ''
                                        ),
                @DeadlockBlobs
                                 = STUFF(
                                   (
-- SQL Prompt formatting off
                                       SELECT       DISTINCT ', CURVEPOLYGON(CIRCULARSTRING('
                                                    + CAST((0-xml.MinutesAgo) + @DeadlockBlobRadius AS CHAR(5)) + ' 0, ' 
                                                    + CAST((0-xml.MinutesAgo) AS CHAR(5)) + ' ' + CAST(@DeadlockBlobRadius AS CHAR(5)) + ', '
                                                    + CAST((0-xml.MinutesAgo) - @DeadlockBlobRadius AS CHAR(5)) + ' 0, '
                                                    + CAST((0-xml.MinutesAgo) AS CHAR(5)) + ' ' + CAST((0 - @DeadlockBlobRadius) AS CHAR(5)) + ', '
                                                    + CAST((0-xml.MinutesAgo) + @DeadlockBlobRadius AS CHAR(5)) + ' 0'
                                                    + '))'
-- SQL Prompt formatting on
                                       FROM     @XMLDeadlocks xml
                                       WHERE    xml.MinutesAgo <= @CPURecords
                                       FOR XML PATH('')
                                   ),
                                   1,
                                   2,
                                   ''
                                        ),
                @DeadlockRecords =
        (
            SELECT  COUNT(*) FROM   @XMLDeadlocks xml WHERE xml.MinutesAgo <= @CPURecords
        );

        IF @DeadlockBlobRadius = 0
        BEGIN -- show points
            INSERT INTO @OutputGeometry (ShapeInfo, Caption)
            VALUES
            (   geometry::Parse('MULTIPOINT(' + @DeadlockPoints + ')'), -- ShapeInfo - geometry
                'Deadlocks'                                             -- Caption - varchar(20)
                );
        END;
        ELSE
        BEGIN -- show blobs
            INSERT INTO @OutputGeometry (ShapeInfo, Caption)
            VALUES
            (   geometry::Parse('GEOMETRYCOLLECTION(' + @DeadlockBlobs + ')'), -- ShapeInfo - geometry
                'Deadlocks'                                                 -- Caption - varchar(20)
                );
        END;

        IF @OutputDeadlockTable = 1
        BEGIN
            SELECT  UTCTime, LocalTime, MinutesAgo, XML FROM @XMLDeadlocks xd ORDER BY  xd.MinutesAgo;
        END;
    END;

    --End Bar
    IF @ShowEndBar = 1
    BEGIN
        IF @EndBarWidth = 0
        BEGIN
            INSERT INTO @OutputGeometry (ShapeInfo, Caption)
            VALUES
            (   geometry::Parse('LINESTRING(0 0,0 100)'), -- ShapeInfo - geometry
                'Now'                                   -- Caption - varchar(100)
                );
        END;
        ELSE
        BEGIN
            INSERT INTO @OutputGeometry (ShapeInfo, Caption)
            VALUES
            (   geometry::Parse('POLYGON((0 0,0 100, ' + CAST(@EndBarWidth AS CHAR(3)) + ' 100, '
                                + CAST(@EndBarWidth AS CHAR(3)) + ' 0, 0 0))'
                               ),   -- ShapeInfo - geometry
                'Now'               -- Caption - varchar(100)
                );
        END;
    END;

    -- Final Output
    SELECT  ShapeInfo, Caption FROM @OutputGeometry;
END;
GO

What now?

There may be other functionality that can be added to this – but I can’t think of anything right now.

But I probably won’t be doing any more on this in the near future anyway, as my Redgate SQL Monitor licence has arrived, so I can get back to doing things properly.

Posted in Uncategorized | Tagged , , , , , , | Leave a comment

CPU Load Graphs & Deadlocks 2 – Tweaks

Following on from the earlier post CPU Load Graphs and Deadlocks

Bug fix

Oops. Of course, I wasn’t expecting this system to have multiple deadlocks in the same minute, but, if you do have that situation and you use the code I posted earlier, you won’t see the duplicates as the polygons are implemented in such a way that overlaps / intersections are not shown – see examples on Simple-Talk’s Introduction to Spatial Data. So I’ve tweaked the statement to only return DISTINCT values of MinutesAgo.

Axis Shift

Previously, the x axis (time) was running from 0 to 256 – where the 256 value represented the most recent data. Although this preserved the left-to-right timeline feel, the actual numbers felt wrong, so I’ve moved the x axis so zero is at the right, rather than the left, and the x axis is properly showing “minutes ago”.

I’ve also added a bar at the right hand end as a more visual cue to “this is where we are now”

The new code:

DECLARE @ts_now BIGINT =
        (
            SELECT      TOP (1)
                        cpu_ticks / (cpu_ticks / ms_ticks)
            FROM        sys.dm_os_sys_info WITH (NOLOCK)
            ORDER BY    cpu_ticks DESC
        );
DECLARE @CPUSQLOut   AS VARCHAR(8000),
        @CPUIdleOut  AS VARCHAR(8000),
        @CPUOtherOut AS VARCHAR(8000);
DECLARE @CPURecords AS INT;
DECLARE @DeadlockPoints     AS VARCHAR(8000),
        @DeadlockBlobs      AS VARCHAR(8000),
        @DeadlockBlobRadius AS INT          = 1,
        @DeadlockRecords    AS INT;

WITH
CPULoad AS
(
    SELECT      TOP (256)
                y.SQLProcessUtilization                              AS [SQL Server Process CPU Utilization],
                y.SystemIdle                                         AS [System Idle Process],
                100 - (y.SystemIdle + y.SQLProcessUtilization)       AS [Other Process CPU Utilization],
                DATEADD(ms, -1 * (@ts_now - y.timestamp), GETDATE()) AS [Event Time]
    FROM
                (
                    SELECT  x.record.value('(./Record/@id)[1]', 'int')                                                   AS record_id,
                            x.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')         AS SystemIdle,
                            x.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
                            x.timestamp
                    FROM
                            (
                                SELECT  timestamp,
                                        CONVERT(XML, record) AS record
                                FROM    sys.dm_os_ring_buffers WITH (NOLOCK)
                                WHERE
                                        ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
                                        AND record LIKE N'%<SystemHealth>%'
                            ) AS x
                ) AS y
    ORDER BY    y.timestamp DESC
)
SELECT  @CPUSQLOut
                    = STUFF(
                      (
                          SELECT        ','
                                        + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) - COUNT(*) OVER (PARTITION BY 1) AS VARCHAR(10))
                                        + ' ' + CAST(CPULoad.[SQL Server Process CPU Utilization] AS VARCHAR(20))
                          FROM          CPULoad
                          ORDER BY      CPULoad.[Event Time]
                          FOR XML PATH('')
                      ),
                      1,
                      1,
                      ''
                           ),
        @CPUIdleOut
                    = STUFF(
                      (
                          SELECT    ','
                                    + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) - COUNT(*) OVER (PARTITION BY 1) AS VARCHAR(10))
                                    + ' ' + CAST(CPULoad.[System Idle Process] AS VARCHAR(20))
                          FROM      CPULoad
                          ORDER BY  CPULoad.[Event Time]
                          FOR XML PATH('')
                      ),
                      1,
                      1,
                      ''
                           ),
        @CPUOtherOut
                    = STUFF(
                      (
                          SELECT    ','
                                    + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) - COUNT(*) OVER (PARTITION BY 1) AS VARCHAR(10))
                                    + ' ' + CAST(CPULoad.[Other Process CPU Utilization] AS VARCHAR(20))
                          FROM      CPULoad
                          ORDER BY  CPULoad.[Event Time]
                          FOR XML PATH('')
                      ),
                      1,
                      1,
                      ''
                           ),
        @CPURecords = (SELECT   COUNT(*) FROM   CPULoad);

WITH
xmlsource AS
(
    SELECT  CONVERT(XML, event_data) AS deadlock_xml
    FROM    sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
),
xmldates AS
(
    SELECT  xmlsource.deadlock_xml,
            xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') AS deadlock_UTCtime, -- this is UTC.  Need converting to local.
            DATEADD(
                       mi,
                       DATEDIFF(mi, GETUTCDATE(), GETDATE()),
                       xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime')
                   )                                                           AS deadlock_LocalTime
    FROM    xmlsource
    WHERE   xmlsource.deadlock_xml.value('(/event/@name)[1]', 'VARCHAR(256)') = 'xml_deadlock_report'
),
xml AS
(
    SELECT  DISTINCT -- <== That's the bug fix, right there.  That one word.  Oh yeah.
            DATEDIFF(MINUTE, xmldates.deadlock_LocalTime, GETDATE()) AS MinutesAgo
    FROM    xmldates
)

SELECT  @DeadlockPoints  = STUFF(
                           (
                               SELECT       ', (' + CAST((0 - xml.MinutesAgo) AS CHAR(5)) + ' 0)'
                               FROM         xml
                               WHERE        xml.MinutesAgo <= @CPURecords
                               ORDER BY     xml.MinutesAgo
                               FOR XML PATH('')
                           ),
                           1,
                           2,
                           ''
                                ),
        @DeadlockBlobs
                         = STUFF(
                           (
-- SQL Prompt formatting off
                               SELECT       ', CURVEPOLYGON(CIRCULARSTRING('
                                            + CAST((0-xml.MinutesAgo) + @DeadlockBlobRadius AS CHAR(5)) + ' 0, ' 
                                            + CAST((0-xml.MinutesAgo) AS CHAR(5)) + ' ' + CAST(@DeadlockBlobRadius AS CHAR(5)) + ', '
                                            + CAST((0-xml.MinutesAgo) - @DeadlockBlobRadius AS CHAR(5)) + ' 0, '
                                            + CAST((0-xml.MinutesAgo) AS CHAR(5)) + ' ' + CAST((0 - @DeadlockBlobRadius) AS CHAR(5)) + ', '
                                            + CAST((0-xml.MinutesAgo) + @DeadlockBlobRadius AS CHAR(5)) + ' 0'
                                            + '))'
-- SQL Prompt formatting on
                               FROM         xml
                               WHERE        xml.MinutesAgo <= @CPURecords
                               ORDER BY     xml.MinutesAgo
                               FOR XML PATH('')
                           ),
                           1,
                           2,
                           ''
                                ),
        @DeadlockRecords = (SELECT  COUNT(*) FROM   xml WHERE   xml.MinutesAgo <= @CPURecords);

SELECT  geometry::STGeomFromText('LINESTRING(' + @CPUSQLOut + ')', 0) AS CPULoad, 'CPU-SQL' AS Caption
UNION ALL
SELECT  geometry::STGeomFromText('LINESTRING(' + @CPUOtherOut + ')', 0) AS CPULoad,
        'CPU-Other'                                                     AS Caption
UNION ALL
SELECT  geometry::STGeomFromText('LINESTRING(' + @CPUIdleOut + ')', 0) AS CPULoad,
        'CPU-Idle'                                                     AS Caption
--UNION ALL
--SELECT  geometry::STGeomFromText('MULTIPOINT(' + @DeadlockPoints + ')', 0) AS Deadlocks,
--        'Deadlocks'                                                        AS Caption
UNION ALL
SELECT  geometry::Parse('GEOMETRYCOLLECTION (' + @DeadlockBlobs + ')') AS DeadlockBlobs,
        'Deadlocks'                                                    AS caption
UNION ALL
SELECT  geometry::Parse('POLYGON ((0 0, 2 0, 2 100, 0 100, 0 0))') AS NowBar, 'NOW' AS caption; -- NEW: "Now" bar

And the output:

And, for comparison, without that DISTINCT:

Like I said, oops… Oh well, at least I spotted it early.

Posted in Uncategorized | Tagged , , , , , | Leave a comment

CPU Load Graphs with Deadlocks

Continuing on disappearing down the rabbit hole… Send help! Or coffee!

Deadlocks

I’m always being asked, sometimes being asked, somebody asked me once “what about the deadlocks? Can you show them on your CPU load graphs?”

Oh, go on.

First, find your deadlock information. Fortunately(?), the system I’m working against here experiences them regularly. I just need to find the information and figure out how to add it to this monstrosity.

Rummaging around in the source code for sp_BlitzIndex, there’s a useful-looking chunk of code that extracts some deadlock information:

        /*Grab the initial set of XML to parse*/
        SET @d = CONVERT(VARCHAR(40), GETDATE(), 109);
        RAISERROR('Grab the initial set of XML to parse at %s', 0, 1, @d) WITH NOWAIT;
        WITH xml
        AS ( SELECT CONVERT(XML, event_data) AS deadlock_xml
             FROM   sys.fn_xe_file_target_read_file(@EventSessionPath, NULL, NULL, NULL) ) -- parameter defaults to 'system_health*.xel'
        SELECT TOP ( @Top ) ISNULL(xml.deadlock_xml, '') AS deadlock_xml
        INTO   #deadlock_data
        FROM   xml
        LEFT JOIN #t AS t
        ON 1 = 1
        WHERE  xml.deadlock_xml.value('(/event/@name)[1]', 'VARCHAR(256)') = 'xml_deadlock_report'
        AND    xml.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') >= @StartDate
        AND    xml.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') < @EndDate
                ORDER BY xml.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') DESC
                OPTION ( RECOMPILE );

It’s a bit more than I need/want for this exercise, but it’s a quick clue to where to look…

After a bit of fiddling, we have the following chunk of code that reads the system_health file and pulls out deadlock records, the date/time of the deadlock, and converts that date/time from UTC into local time

WITH
xmlsource AS
(
    SELECT  CONVERT(XML, event_data) AS deadlock_xml
    FROM    sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
),
xmldates AS
(
    SELECT  xmlsource.deadlock_xml,
            xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') AS deadlock_UTCtime, -- this is UTC.  Need converting to local.
            DATEADD(
                       mi,
                       DATEDIFF(mi, GETUTCDATE(), GETDATE()),
                       xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime')
                   )                                                           AS deadlock_LocalTime
    FROM    xmlsource
    WHERE   xmlsource.deadlock_xml.value('(/event/@name)[1]', 'VARCHAR(256)') = 'xml_deadlock_report'
)
SELECT  * FROM  xmldates;

That gives us the following resultset that we can work with:

However, because we’re plotting a graph in SQL, it can only handle numbers, not dates. Given the CPU information we’ve been dealing with is a per-minute record, we need to do a bit of date arithmetic

WITH
xmlsource AS
(
    SELECT  CONVERT(XML, event_data) AS deadlock_xml
    FROM    sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
),
xmldates AS
(
    SELECT  xmlsource.deadlock_xml,
            xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') AS deadlock_UTCtime, -- this is UTC.  Need converting to local.
            DATEADD(
                       mi,
                       DATEDIFF(mi, GETUTCDATE(), GETDATE()),
                       xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime')
                   )                                                           AS deadlock_LocalTime
    FROM    xmlsource
    WHERE   xmlsource.deadlock_xml.value('(/event/@name)[1]', 'VARCHAR(256)') = 'xml_deadlock_report'
),
xml AS
(
    SELECT  xmldates.deadlock_xml,
            xmldates.deadlock_UTCtime,
            xmldates.deadlock_LocalTime,
            DATEDIFF(MINUTE, xmldates.deadlock_LocalTime, GETDATE()) AS MinutesAgo
    FROM    xmldates
)
SELECT  * FROM  xml;

That gives us a useful column, “MinutesAgo”, that we can use when plotting points:

and we can only get just over 4 hours of data there, we can do some other work to cut this resultset down further when we build the geometry strings.

Lines and Points

Adding our new code to our old CPU load lines code, and creating the deadlocks as a set of points to plot, gives us this code:

DECLARE @ts_now BIGINT =
        (
            SELECT      TOP (1)
                        cpu_ticks / (cpu_ticks / ms_ticks)
            FROM        sys.dm_os_sys_info WITH (NOLOCK)
            ORDER BY    cpu_ticks DESC
        );
DECLARE @CPUSQLOut   AS VARCHAR(8000),
        @CPUIdleOut  AS VARCHAR(8000),
        @CPUOtherOut AS VARCHAR(8000);
DECLARE @CPURecords AS INT;
DECLARE @DeadlockPoints  AS VARCHAR(8000),
        @DeadlockRecords AS INT;

WITH
CPULoad AS
(
    SELECT      TOP (256)
                y.SQLProcessUtilization                              AS [SQL Server Process CPU Utilization],
                y.SystemIdle                                         AS [System Idle Process],
                100 - (y.SystemIdle + y.SQLProcessUtilization)       AS [Other Process CPU Utilization],
                DATEADD(ms, -1 * (@ts_now - y.timestamp), GETDATE()) AS [Event Time]
    FROM
                (
                    SELECT  x.record.value('(./Record/@id)[1]', 'int')                                                   AS record_id,
                            x.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')         AS SystemIdle,
                            x.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
                            x.timestamp
                    FROM
                            (
                                SELECT  timestamp,
                                        CONVERT(XML, record) AS record
                                FROM    sys.dm_os_ring_buffers WITH (NOLOCK)
                                WHERE
                                        ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
                                        AND record LIKE N'%<SystemHealth>%'
                            ) AS x
                ) AS y
    ORDER BY    y.timestamp DESC
)
SELECT  @CPUSQLOut
                    = STUFF(
                      (
                          SELECT        ',' + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) AS VARCHAR(10)) + ' '
                                        + CAST(CPULoad.[SQL Server Process CPU Utilization] AS VARCHAR(20))
                          FROM          CPULoad
                          ORDER BY      CPULoad.[Event Time]
                          FOR XML PATH('')
                      ),
                      1,
                      1,
                      ''
                           ),
        @CPUIdleOut
                    = STUFF(
                      (
                          SELECT    ',' + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) AS VARCHAR(10)) + ' '
                                    + CAST(CPULoad.[System Idle Process] AS VARCHAR(20))
                          FROM      CPULoad
                          ORDER BY  CPULoad.[Event Time]
                          FOR XML PATH('')
                      ),
                      1,
                      1,
                      ''
                           ),
        @CPUOtherOut
                    = STUFF(
                      (
                          SELECT    ',' + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) AS VARCHAR(10)) + ' '
                                    + CAST(CPULoad.[Other Process CPU Utilization] AS VARCHAR(20))
                          FROM      CPULoad
                          ORDER BY  CPULoad.[Event Time]
                          FOR XML PATH('')
                      ),
                      1,
                      1,
                      ''
                           ),
        @CPURecords = (SELECT   COUNT(*) FROM   CPULoad);

WITH
xmlsource AS
(
    SELECT  CONVERT(XML, event_data) AS deadlock_xml
    FROM    sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
),
xmldates AS
(
    SELECT  xmlsource.deadlock_xml,
            xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') AS deadlock_UTCtime, -- this is UTC.  Need converting to local.
            DATEADD(
                       mi,
                       DATEDIFF(mi, GETUTCDATE(), GETDATE()),
                       xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime')
                   )                                                           AS deadlock_LocalTime
    FROM    xmlsource
    WHERE   xmlsource.deadlock_xml.value('(/event/@name)[1]', 'VARCHAR(256)') = 'xml_deadlock_report'
),
xml AS
(
    SELECT  xmldates.deadlock_xml,
            xmldates.deadlock_UTCtime,
            xmldates.deadlock_LocalTime,
            DATEDIFF(MINUTE, xmldates.deadlock_LocalTime, GETDATE()) AS MinutesAgo
    FROM    xmldates
)
SELECT  @DeadlockPoints  = STUFF(
                           (
                               SELECT       ', (' + CAST((@CPURecords - xml.MinutesAgo) AS CHAR(5)) + ' 0)'
                               FROM         xml
                               WHERE        xml.MinutesAgo <= @CPURecords
                               ORDER BY     xml.MinutesAgo
                               FOR XML PATH('')
                           ),
                           1,
                           2,
                           ''
                                ),
        @DeadlockRecords = (SELECT  COUNT(*) FROM   xml WHERE   xml.MinutesAgo <= @CPURecords);

SELECT  geometry::STGeomFromText('LINESTRING(' + @CPUSQLOut + ')', 0) AS CPULoad,        'CPU-SQL'   AS Caption
UNION ALL
SELECT  geometry::STGeomFromText('LINESTRING(' + @CPUOtherOut + ')', 0) AS CPULoad,      'CPU-Other' AS Caption
UNION ALL
SELECT  geometry::STGeomFromText('LINESTRING(' + @CPUIdleOut + ')', 0) AS CPULoad,       'CPU-Idle'  AS Caption
UNION ALL
SELECT  geometry::STGeomFromText('MULTIPOINT(' + @DeadlockPoints + ')', 0) AS Deadlocks, 'Deadlocks' AS Caption;

And the results:

Am I going blind? Or have they disappeared? Using the zoom, we still can’t really see them…

Not really surprising. A line of no width and no length – never going to be easy to see…

Lines and Blobs

Instead of using MULTIPOINT, let’s have a go with some filled circles. This is using the CURVEPOLYGON / CIRCULARSTRING combination (if you omit the CIRCULARSTRING bit you end up with, in this case, little diamonds…). The code below has a few extra bits thrown in – the blobradius is configurable, for example.

DECLARE @ts_now BIGINT =
        (
            SELECT      TOP (1)
                        cpu_ticks / (cpu_ticks / ms_ticks)
            FROM        sys.dm_os_sys_info WITH (NOLOCK)
            ORDER BY    cpu_ticks DESC
        );
DECLARE @CPUSQLOut   AS VARCHAR(8000),
        @CPUIdleOut  AS VARCHAR(8000),
        @CPUOtherOut AS VARCHAR(8000);
DECLARE @CPURecords AS INT;
DECLARE @DeadlockBlobs      AS VARCHAR(8000),
        @DeadlockBlobRadius AS INT          = 1, -- seems to be enough
        @DeadlockRecords    AS INT;              -- not used, but might come in handy later

WITH
CPULoad AS
(
    SELECT      TOP (256)
                y.SQLProcessUtilization                              AS [SQL Server Process CPU Utilization],
                y.SystemIdle                                         AS [System Idle Process],
                100 - (y.SystemIdle + y.SQLProcessUtilization)       AS [Other Process CPU Utilization],
                DATEADD(ms, -1 * (@ts_now - y.timestamp), GETDATE()) AS [Event Time]
    FROM
                (
                    SELECT  x.record.value('(./Record/@id)[1]', 'int')                                                   AS record_id,
                            x.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')         AS SystemIdle,
                            x.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
                            x.timestamp
                    FROM
                            (
                                SELECT  timestamp,
                                        CONVERT(XML, record) AS record
                                FROM    sys.dm_os_ring_buffers WITH (NOLOCK)
                                WHERE
                                        ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
                                        AND record LIKE N'%<SystemHealth>%'
                            ) AS x
                ) AS y
    ORDER BY    y.timestamp DESC
)
SELECT  @CPUSQLOut
                    = STUFF(
                      (
                          SELECT        ',' + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) AS VARCHAR(10)) + ' '
                                        + CAST(CPULoad.[SQL Server Process CPU Utilization] AS VARCHAR(20))
                          FROM          CPULoad
                          ORDER BY      CPULoad.[Event Time]
                          FOR XML PATH('')
                      ),
                      1,
                      1,
                      ''
                           ),
        @CPUIdleOut
                    = STUFF(
                      (
                          SELECT    ',' + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) AS VARCHAR(10)) + ' '
                                    + CAST(CPULoad.[System Idle Process] AS VARCHAR(20))
                          FROM      CPULoad
                          ORDER BY  CPULoad.[Event Time]
                          FOR XML PATH('')
                      ),
                      1,
                      1,
                      ''
                           ),
        @CPUOtherOut
                    = STUFF(
                      (
                          SELECT    ',' + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) AS VARCHAR(10)) + ' '
                                    + CAST(CPULoad.[Other Process CPU Utilization] AS VARCHAR(20))
                          FROM      CPULoad
                          ORDER BY  CPULoad.[Event Time]
                          FOR XML PATH('')
                      ),
                      1,
                      1,
                      ''
                           ),
        @CPURecords = (SELECT   COUNT(*) FROM   CPULoad);

WITH
xmlsource AS
(
    SELECT  CONVERT(XML, event_data) AS deadlock_xml
    FROM    sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
),
xmldates AS
(
    SELECT  xmlsource.deadlock_xml,
            xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') AS deadlock_UTCtime, -- this is UTC.  Need to convert to local.
            DATEADD(
                       mi,
                       DATEDIFF(mi, GETUTCDATE(), GETDATE()),
                       xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime')
                   )                                                           AS deadlock_LocalTime
    FROM    xmlsource
    WHERE   xmlsource.deadlock_xml.value('(/event/@name)[1]', 'VARCHAR(256)') = 'xml_deadlock_report'
),
xml AS
(
    SELECT  DATEDIFF(MINUTE, xmldates.deadlock_LocalTime, GETDATE()) AS MinutesAgo FROM xmldates  -- only interested in the minutes
)
SELECT  
        @DeadlockBlobs
                         = STUFF(
                           (
-- SQL Prompt formatting off
                               SELECT       ', CURVEPOLYGON(CIRCULARSTRING('
                                            + CAST((@CPURecords - xml.MinutesAgo) + @DeadlockBlobRadius AS CHAR(5)) + ' 0, ' 
                                            + CAST((@CPURecords - xml.MinutesAgo) AS CHAR(5)) + ' ' + CAST(@DeadlockBlobRadius AS CHAR(5)) + ', '
                                            + CAST((@CPURecords - xml.MinutesAgo) - @DeadlockBlobRadius AS CHAR(5)) + ' 0, '
                                            + CAST((@CPURecords - xml.MinutesAgo) AS CHAR(5)) + ' ' + CAST((0 - @DeadlockBlobRadius) AS CHAR(5)) + ', '
                                            + CAST((@CPURecords - xml.MinutesAgo) + @DeadlockBlobRadius AS CHAR(5)) + ' 0'
                                            + '))'
-- SQL Prompt formatting on
                               FROM         xml
                               WHERE        xml.MinutesAgo <= @CPURecords
                               ORDER BY     xml.MinutesAgo
                               FOR XML PATH('')
                           ),
                           1,
                           2,
                           ''
                                ),
        @DeadlockRecords = (SELECT  COUNT(*) FROM   xml WHERE   xml.MinutesAgo <= @CPURecords);

SELECT  geometry::STGeomFromText('LINESTRING(' + @CPUSQLOut + ')', 0) AS CPULoad, 'CPU-SQL' AS Caption
UNION ALL
SELECT  geometry::STGeomFromText('LINESTRING(' + @CPUOtherOut + ')', 0) AS CPULoad,
        'CPU-Other'                                                     AS Caption
UNION ALL
SELECT  geometry::STGeomFromText('LINESTRING(' + @CPUIdleOut + ')', 0) AS CPULoad,
        'CPU-Idle'                                                     AS Caption
UNION ALL
SELECT  geometry::Parse('GEOMETRYCOLLECTION (' + @DeadlockBlobs + ')') AS DeadlockBlobs,
        'Deadlocks'                                                    AS caption;

Run it

That looks better

I probably ought to clean up the code a bit, change the x-axis, and a few other odds and sods, before I take this much further, but this already proving useful, even if it’s only to give me some fun learning the Geometry stuff!

Posted in Uncategorized | Tagged , , , , , | 1 Comment