Waiting for a job to complete

You know how it is. You need to wait for a job to complete before moving onto the next one… And, just sometimes, you need to do the same with SQL Server.

MSDB

The msdb system database contains various tables for dealing with the SQL Agent and the job scheduler. The ones of particular interest for this problem are:

syssessions

The syssessions table contains a list of the SQLAgent sessions. Unfortunately, there’s no absolute guarantee that the session_id is in ascending order, so we need to pull the top value when sorted by agent_start_date in descending order.

sysjobs

The sysjobs table contains information about each scheduled (and unscheduled) job. We’re only interested in the job_id and job_name fields at the moment.

sysjobactivity

The sysjobactivity table contains data about what jobs are running in the current session and, indeed, can be used as a snapshot of what jobs were running when a previous SQLAgent instance was ended.

Final code

Here follows a quick & dirty query to watch for one job to finish, and then to start the next job:

USE msdb;

DECLARE @JobToWaitFor sysname = 'First Job';
DECLARE @JobToLaunchNext sysname = 'Second Job';

WHILE EXISTS
      (
          SELECT j.name,
                 ja.*
          FROM sysjobs j
              INNER JOIN sysjobactivity ja
                  ON ja.job_id = j.job_id
          WHERE ja.session_id =
          (
              -- can't use max session_id, as not guaranteed to be the most recent agent start date
              SELECT TOP 1 session_id FROM syssessions ORDER BY agent_start_date DESC
          )
                AND ja.start_execution_date IS NOT NULL
                AND ja.stop_execution_date IS NULL
                AND j.name = @JobToWaitFor
      )
BEGIN
    -- delay loop here
    WAITFOR DELAY '00:00:10'; -- tweak this as appropriate to your job length, or desired delay between job finish/start
END;
EXEC sp_start_job @job_name = @JobToLaunchNext;

Yes, there are more elegant / succinct ways to do this – you could achieve similar results by using sp_help_job for example – but using script snippets like this can allow a more complex job workflow than merely “start this job, run this script” – in the past, I’ve written similar code to launch several jobs to run in parallel and then wait for them all to finish before moving onto the next step.

Nested Virtual / Remote Desktops and CTRL+ALT+DELETE

Just a quickie…

You need to change your password, so you use Ctrl+ALT+Delete. That’s fine, as long as you’re physically at the machine.

But if you’re connected to a remote desktop session somewhere, that key combo doesn’t get through. But you can send Ctrl+ALT+Insert to do the same thing.

What if you’re on a remote server and connected through that to another remote server (with a different set of credentials) and you need to send Ctrl+ALT+Del to that? Well, Ctrl+Alt+Del will do your local machine, Ctrl+ALT+Insert will do the top level Virtual machine… Today I learned about Ctrl+ALT+End – that’ll do the one that you’re actually looking at. Hurrah!

Ctrl+Alt+End. I know all this VDI-inside-a-VDI is all a bit Inception-y, but it does the job. Now, if only I could figure out the password complexity rules…

Login Script Generation For Migration or DR

I needed a script to create logins on a second server to match the primary. There are reasons why using, say, the powershell dba tool set isn’t an option (eg the server isn’t built, or the two can’t communicate, or your security team doesn’t allow for the use of this sort of thing), so let’s have a look at what we can do just in T-SQL.

There are three things to look at:

1. Creating Logins from Windows accounts
2. Creating logins that are authenticated by SQL Server
3. Assigning membership of the appropriate server roles

Note that we’re just creating a snapshot of what’s there – we’re not aiming to keep these things in true synchronisation between servers. Note also that this script will only create accounts that don’t exist. If there’s an account already there with the same name, the script generated will not recreate it with new options.

sys.syslogins, sys.server_principals, sys.sql_logins

There are a couple of ways of getting at login information:

sys.syslogins

This is the old way. Try not to use it any more. The sys.syslogins documentation says it will be removed in a future version of Microsoft SQL Server.

sys.server_principals and sys.sql_logins

These are the new(ish) DMVs to use – sys.server_principals contains the bulk of the information needed, and sys.sql_logins one contains a few extra bits that are specific only to SQL Server authentication (passwords, policy flags, etc), but the bulk is inherited from sys.server_principals

Scripting Windows Login Accounts

This is probably the easiest script to write, as it just references sys.server_principals. I’ve written a simple “IF NOT EXISTS” wrapper around the whole thing, to make sure we don’t try to create an account that’s already there.

SELECT  N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(name, '''')
        + N') BEGIN CREATE LOGIN ' + QUOTENAME(name) + N' FROM WINDOWS WITH DEFAULT_LANGUAGE = '
        + default_language_name + N', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) + N' END;' AS SQLQuery
FROM    sys.server_principals
WHERE
        type IN ('U', 'G')
        AND name NOT LIKE 'NT %';

Note that we’re excluding the NT service accounts / default accounts, as there’s no point in generating those, as they’ll already be there on the target anyway.

Scripting SQL Authenticated Accounts

Really? We still have these? Unfortunately, yes – and I can’t see them going away any time soon, unfortunately. So, here we are.

As above, I’m generating the “IF NOT EXISTS” wrapper. This time, we’re querying sys.sql_logins, as it contains only those server principals, and the extra (password / policy) information required by these accounts.

SELECT  N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(sl.name, '''')
        + N') BEGIN CREATE LOGIN ' + QUOTENAME(sl.name) + N' WITH PASSWORD = '
        + CAST(CONVERT(VARCHAR(256), CAST(LOGINPROPERTY(sl.name, 'PasswordHash') AS VARBINARY(256)), 1) AS NVARCHAR(MAX))
        + ' HASHED' + N', SID=' + CONVERT(VARCHAR(256), sl.sid, 1) + N', DEFAULT_LANGUAGE = '
        + sl.default_language_name + N', DEFAULT_DATABASE = ' + QUOTENAME(sl.default_database_name)
        + N', CHECK_EXPIRATION = ' + CASE sl.is_expiration_checked
                                         WHEN 1 THEN
                                             N'ON'
                                     ELSE
                                         N'OFF'
                                     END
        + N', CHECK_POLICY = ' + CASE sl.is_policy_checked
                                     WHEN 1 THEN
                                         N'ON'
                                 ELSE
                                     N'OFF'
                                 END
        + N'; END;' AS SQLQuery
FROM    sys.sql_logins sl
WHERE
        sl.name NOT LIKE '##%'
        AND sl.name NOT LIKE 'sa';

Server Role Memberships

This is the final step – assigning role memberships. For this, we need to use the sys.server_role_members DMV as well as sys.server_principals (twice, once for the role name, once for the member name). That’s the big CTE before the main SELECT statement.

WITH
RoleMembers AS
(
    SELECT          srm.role_principal_id,
                    srm.member_principal_id,
                    r.name AS RoleName,
                    m.name AS MemberName
    FROM
                    sys.server_role_members srm
        INNER JOIN  sys.server_principals   r
                    ON srm.role_principal_id = r.principal_id
        INNER JOIN  sys.server_principals   m
                    ON srm.member_principal_id = m.principal_id
)
SELECT  N'IF EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(MemberName, '''')
        + N') BEGIN ALTER SERVER ROLE ' + RoleName + N' ADD MEMBER ' + +QUOTENAME(MemberName) + N'; END;' AS SQLQuery
FROM    RoleMembers
WHERE
        RoleMembers.member_principal_id IN
        (
            SELECT  principal_id
            FROM    sys.server_principals
            WHERE
                    type IN ('U', 'S')
                    AND name NOT LIKE 'NT %'
                    AND name NOT LIKE 'sa'
                    AND name NOT LIKE '##%'
        );

Wrapping this up

I’ve put all this functionality into a single view that can be queried to return the statements in the order above – logins before roles – so that it can be easily used to generate everything you need. The View definition script is below. Note the extra field (“Ord”) which we won’t query later, but will use to sort the results.

USE [master];
GO

/****** Object:  View [dbo].[ExportLoginQueries]    Script Date: 24/03/2020 16:00:58 ******/
SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID('dbo.ExportLoginQueries', 'V') IS NULL
BEGIN
    EXEC ('CREATE VIEW dbo.ExportLoginQueries AS SELECT 1 AS [one]');
END;
GO

ALTER VIEW [dbo].[ExportLoginQueries]
AS
    WITH
    RoleMembers AS
    (
        SELECT          srm.*,
                        r.name AS RoleName,
                        m.name AS MemberName
        FROM
                        sys.server_role_members srm
            INNER JOIN  sys.server_principals   r
                        ON srm.role_principal_id = r.principal_id
            INNER JOIN  sys.server_principals   m
                        ON srm.member_principal_id = m.principal_id
    )
    SELECT  3                                                                                                 AS Ord,
            N'IF EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(MemberName, '''')
            + N') BEGIN ALTER SERVER ROLE ' + RoleName + N' ADD MEMBER ' + +QUOTENAME(MemberName) + N'; END;' AS SQLQuery
    FROM    RoleMembers
    WHERE
            RoleMembers.member_principal_id IN
            (
                SELECT  principal_id
                FROM    sys.server_principals
                WHERE
                        type IN ('U', 'S')
                        AND name NOT LIKE 'NT %'
                        AND name NOT LIKE 'sa'
                        AND name NOT LIKE '##%'
            )
    UNION ALL
    SELECT  1                                                                                                AS Ord,
            N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(name, '''')
            + N') BEGIN CREATE LOGIN ' + QUOTENAME(name) + N' FROM WINDOWS WITH DEFAULT_LANGUAGE = '
            + default_language_name + N', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) + N' END;' AS SQLQuery
    FROM    sys.server_principals
    WHERE
            type = 'U'
            AND name NOT LIKE 'NT %'
    UNION ALL
    SELECT          2           AS Ord,
                    N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(sl.name, '''')
                    + N') BEGIN CREATE LOGIN ' + QUOTENAME(sl.name) + N' WITH PASSWORD = '
                    + CAST(CONVERT(VARCHAR(256), CAST(LOGINPROPERTY(sl.name, 'PasswordHash') AS VARBINARY(256)), 1) AS NVARCHAR(MAX))
                    + ' HASHED' + N', SID=' + CONVERT(VARCHAR(256), sl.sid, 1) + N', DEFAULT_LANGUAGE = '
                    + sl.default_language_name + N', DEFAULT_DATABASE = ' + QUOTENAME(sl.default_database_name)
                    + N', CHECK_EXPIRATION = ' + CASE sl.is_expiration_checked
                                                     WHEN 1 THEN
                                                         N'ON'
                                                 ELSE
                                                     N'OFF'
                                                 END 
                    + N', CHECK_POLICY = ' + CASE sl.is_policy_checked
                                                 WHEN 1 THEN
                                                     N'ON'
                                             ELSE
                                                 N'OFF'
                                             END 
                    + N'; END;' AS SQLQuery
    FROM
                    sys.sql_logins        sl
    WHERE
                    sl.name NOT LIKE '##%'
                    AND sl.name <> 'sa';
GO

All that needs to be done now is to write something to extract this as & when required. For example, using bcp:

bcp "SELECT SQLQuery FROM dbo.ExportLoginQueries ORDER BY Ord, SQLQuery" queryout "<<PathToBackupDirectory>>\ExportedSQLLogins.sql" -t -c -S <<servername>> -T

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?

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;

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.

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!

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.

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.

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!