Month: December 2018

Calendar UK

Must be that time of year again :). Adapted from Aaron’s beautiful US calendar script …

-- CalendarUK.sql
use [Dev];

-- initialize period

	DECLARE @StartDate DATE = '20000101', @NumberOfYears INT = 30;

-- prevent set or regional settings from interfering with 
-- interpretation of dates / literals

	SET DATEFIRST 7; -- sunday is the first day of week
	SET DATEFORMAT mdy; -- thats month/day/year
	SET LANGUAGE US_ENGLISH;

	DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);

-- 1. this is just a holding table for intermediate calculations:

	IF OBJECT_ID('tempdb..#cal') IS NOT NULL DROP TABLE #cal
	CREATE TABLE #cal
	(
	  [date]       DATE PRIMARY KEY, 
	  [day]        AS DATEPART(DAY,      [date]),
	  [month]      AS DATEPART(MONTH,    [date]),
	  FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)),
	  [MonthName]  AS DATENAME(MONTH,    [date]),
	  [week]       AS DATEPART(WEEK,     [date]),
	  [ISOweek]    AS DATEPART(ISO_WEEK, [date]),
	  [DayOfWeek]  AS DATEPART(WEEKDAY,  [date]),
	  [quarter]    AS DATEPART(QUARTER,  [date]),
	  [year]       AS DATEPART(YEAR,     [date]),
	  FirstOfYear  AS CONVERT(DATE, DATEADD(YEAR,  DATEDIFF(YEAR,  0, [date]), 0)),
	  Style112     AS CONVERT(CHAR(8),   [date], 112),
	  Style101     AS CONVERT(CHAR(10),  [date], 101)
	);

-- use the catalog views to generate as many rows as we need

	INSERT #cal([date]) 
	SELECT d
	FROM
	(
	  SELECT d = DATEADD(DAY, rn - 1, @StartDate)
	  FROM 
	  (
		SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate)) 
		  rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
		FROM sys.all_objects AS s1
		CROSS JOIN sys.all_objects AS s2
		ORDER BY s1.[object_id]
	  ) AS x
	) AS y;

-- 2. create the real table

	IF OBJECT_ID('dbo.CalendarUK') IS NOT NULL DROP TABLE dbo.CalendarUK
	CREATE TABLE [dbo].[CalendarUK]
	(
	  DateKey             INT         NOT NULL PRIMARY KEY,
	  [Date]              DATE        NOT NULL,
	  [Day]               TINYINT     NOT NULL,
	  DaySuffix           CHAR(2)     NOT NULL,
	  [Weekday]           TINYINT     NOT NULL,
	  WeekDayName         VARCHAR(10) NOT NULL,
	  IsWeekend           BIT         NOT NULL,
	  IsHoliday           BIT         NOT NULL,
	  HolidayText         VARCHAR(64) SPARSE,
	  DOWInMonth          TINYINT     NOT NULL,
	  [DayOfYear]         SMALLINT    NOT NULL,
	  WeekOfMonth         TINYINT     NOT NULL,
	  WeekOfYear          TINYINT     NOT NULL,
	  ISOWeekOfYear       TINYINT     NOT NULL,
	  [Month]             TINYINT     NOT NULL,
	  [MonthName]         VARCHAR(10) NOT NULL,
	  [Quarter]           TINYINT     NOT NULL,
	  QuarterName         VARCHAR(6)  NOT NULL,
	  [Year]              INT         NOT NULL,
	  MMYYYY              CHAR(6)     NOT NULL,
	  MonthYear           CHAR(7)     NOT NULL,
	  FirstDayOfMonth     DATE        NOT NULL,
	  LastDayOfMonth      DATE        NOT NULL,
	  FirstDayOfQuarter   DATE        NOT NULL,
	  LastDayOfQuarter    DATE        NOT NULL,
	  FirstDayOfYear      DATE        NOT NULL,
	  LastDayOfYear       DATE        NOT NULL,
	  FirstDayOfNextMonth DATE        NOT NULL,
	  FirstDayOfNextYear  DATE        NOT NULL
	);
	GO

-- 3 populate the real table from the temp table

	INSERT dbo.CalendarUK WITH (TABLOCKX)
	SELECT
	  DateKey     = CONVERT(INT, Style112),
	  [Date]        = [date],
	  [Day]         = CONVERT(TINYINT, [day]),
	  DaySuffix     = CONVERT(CHAR(2), CASE WHEN [day] / 10 = 1 THEN 'th' ELSE 
					  CASE RIGHT([day], 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd' 
					  WHEN '3' THEN 'rd' ELSE 'th' END END),
	  [Weekday]     = CONVERT(TINYINT, [DayOfWeek]),
	  [WeekDayName] = CONVERT(VARCHAR(10), DATENAME(WEEKDAY, [date])),
	  [IsWeekend]   = CONVERT(BIT, CASE WHEN [DayOfWeek] IN (1,7) THEN 1 ELSE 0 END),
	  [IsHoliday]   = CONVERT(BIT, 0),
	  HolidayText   = CONVERT(VARCHAR(64), NULL),
	  [DOWInMonth]  = CONVERT(TINYINT, ROW_NUMBER() OVER 
					  (PARTITION BY FirstOfMonth, [DayOfWeek] ORDER BY [date])),
	  [DayOfYear]   = CONVERT(SMALLINT, DATEPART(DAYOFYEAR, [date])),
	  WeekOfMonth   = CONVERT(TINYINT, DENSE_RANK() OVER 
					  (PARTITION BY [year], [month] ORDER BY [week])),
	  WeekOfYear    = CONVERT(TINYINT, [week]),
	  ISOWeekOfYear = CONVERT(TINYINT, ISOWeek),
	  [Month]       = CONVERT(TINYINT, [month]),
	  [MonthName]   = CONVERT(VARCHAR(10), [MonthName]),
	  [Quarter]     = CONVERT(TINYINT, [quarter]),
	  QuarterName   = CONVERT(VARCHAR(6), CASE [quarter] WHEN 1 THEN 'First' 
					  WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' WHEN 4 THEN 'Fourth' END), 
	  [Year]        = [year],
	  MMYYYY        = CONVERT(CHAR(6), LEFT(Style101, 2)    + LEFT(Style112, 4)),
	  MonthYear     = CONVERT(CHAR(7), LEFT([MonthName], 3) + LEFT(Style112, 4)),
	  FirstDayOfMonth     = FirstOfMonth,
	  LastDayOfMonth      = MAX([date]) OVER (PARTITION BY [year], [month]),
	  FirstDayOfQuarter   = MIN([date]) OVER (PARTITION BY [year], [quarter]),
	  LastDayOfQuarter    = MAX([date]) OVER (PARTITION BY [year], [quarter]),
	  FirstDayOfYear      = FirstOfYear,
	  LastDayOfYear       = MAX([date]) OVER (PARTITION BY [year]),
	  FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth),
	  FirstDayOfNextYear  = DATEADD(YEAR,  1, FirstOfYear)
	FROM #cal
	OPTION (MAXDOP 1);

-- 4 add holidays

	;WITH x AS 
	(
	  SELECT DateKey, [Date], IsHoliday, HolidayText, FirstDayOfYear,
		DOWInMonth, [MonthName], [WeekDayName], [Day],
		LastDOWInMonth = ROW_NUMBER() OVER 
		(
		  PARTITION BY FirstDayOfMonth, [Weekday] 
		  ORDER BY [Date] DESC
		)
	  FROM dbo.CalendarUK
	)
	UPDATE x SET IsHoliday = 1, HolidayText = CASE
	  WHEN ([Date] = FirstDayOfYear) THEN 'New Years Day'
	  WHEN ([DOWInMonth] = 3 AND [MonthName] = 'April' AND [WeekDayName] = 'Friday') THEN 'Good Friday'                  -- (3rd Monday in January)
	  WHEN ([DOWInMonth] = 1 AND [MonthName] = 'May' AND [WeekDayName] = 'Monday') THEN 'May Day'                        -- (first Monday in May)
	  WHEN ([LastDOWInMonth] = 1 AND [MonthName] = 'May' AND [WeekDayName] = 'Monday') THEN 'May Bank Holiday'           -- (last Monday in May)
	  WHEN ([LastDOWInMonth] = 1 AND [MonthName] = 'August' AND [WeekDayName] = 'Monday') THEN 'August Bank Hoiliday'    -- (last Monday in August)
	  WHEN ([MonthName] = 'December' AND [Day] = 25) THEN 'Christmas Day'
	  WHEN ([MonthName] = 'December' AND [Day] = 26) THEN 'Boxing Day'
	  END
	WHERE -- IsHoliday
	  ([Date] = FirstDayOfYear)
	  OR ([LastDOWInMonth] = 1 AND [MonthName] = 'May' AND [WeekDayName] = 'Monday')
	  OR ([DOWInMonth] = 1     AND [MonthName] = 'May' AND [WeekDayName] = 'Monday')
	  OR ([LastDOWInMonth] = 1 AND [MonthName] = 'August'    AND [WeekDayName] = 'Monday')
	  OR ([MonthName] = 'December' AND [Day] = 25)
	  OR ([MonthName] = 'December' AND [Day] = 26);


-- 5. create a function to calculate easter etc

	IF OBJECT_ID('dbo.GetEasterHolidays') IS NOT NULL DROP FUNCTION dbo.GetEasterHolidays
	GO

	CREATE FUNCTION dbo.GetEasterHolidays(@year INT) 
	RETURNS TABLE
	WITH SCHEMABINDING
	AS 
	RETURN 
	(
	  WITH x AS 
	  (
		SELECT [Date] = CONVERT(DATE, RTRIM(@year) + '0' + RTRIM([Month]) 
			+ RIGHT('0' + RTRIM([Day]),2))
		  FROM (SELECT [Month], [Day] = DaysToSunday + 28 - (31 * ([Month] / 4))
		  FROM (SELECT [Month] = 3 + (DaysToSunday + 40) / 44, DaysToSunday
		  FROM (SELECT DaysToSunday = paschal - ((@year + @year / 4 + paschal - 13) % 7)
		  FROM (SELECT paschal = epact - (epact / 28)
		  FROM (SELECT epact = (24 + 19 * (@year % 19)) % 30) 
			AS epact) AS paschal) AS dts) AS m) AS d
	  )
	  SELECT DATEADD(DAY,-2,[Date]) [Date], 'Good Friday' HolidayName FROM x
		UNION ALL SELECT DATEADD(DAY, 1,[Date]), 'Easter Monday' FROM x
	  );
	GO

-- 6. use the function to insert easter etc

	;WITH x AS 
	(
	  SELECT d.[Date], d.IsHoliday, d.HolidayText, h.HolidayName
		FROM dbo.CalendarUK AS d
		CROSS APPLY dbo.GetEasterHolidays(d.[Year]) AS h
		WHERE d.[Date] = h.[Date]
	)
	UPDATE x SET IsHoliday = 1, HolidayText = HolidayName;

-- 7. show results

	SELECT * 
	FROM dbo.CalendarUK
	WHERE [year] = '2019'
	--WHERE [year] in ('2019', '2020')
	AND (IsHoliday = 1
	OR HolidayText IS NOT NULL)
	--and DateKey = '20181231'

RCSI testing

Here’s some code to create a large number of ghost records.

--rcsi_testing.sql

-- create and populate a test table

CREATE TABLE dbo.demo_table
  (
      ID    INT       NOT NULL    IDENTITY (1, 1),
      C1    CHAR(100) NOT NULL
  );
  GO
   
  INSERT INTO dbo.demo_table (C1)
  SELECT TOP (1000)
         CAST(TEXT AS CHAR(100)) AS C1
  FROM   sys.messages
  WHERE  language_id = 1031;
  GO
    
  CREATE UNIQUE CLUSTERED INDEX cuix_demo_table_Id
  ON dbo.demo_table (Id);
  GO

 
-- start a 1 minute workload
 
  SET NOCOUNT ON;
  GO
  BEGIN TRANSACTION; ---------**********KEY
  GO
  	-- Insert new record into dbo.demo_table
  	DECLARE	@finish_date DATETIME2(0) = DATEADD(MINUTE, 1, GETDATE());
  	WHILE @finish_date >= GETDATE()
  	BEGIN
  		-- wait 10 ms before each new process
  		INSERT INTO dbo.demo_table(C1)
  		SELECT C1
  		FROM   dbo.demo_table
  		WHERE  Id = (SELECT MIN(Id) FROM dbo.demo_table);
    
  		-- Wait 10 ms to delete the first record from the table
  		WAITFOR DELAY '00:00:00:010';
    
  		-- Now select the min record from the table
  		DELETE dbo.demo_table WHERE Id = (SELECT MIN(Id) FROM dbo.demo_table);
  	END
  ROLLBACK TRAN;
  GO

Monitoring RCSI

I created a sql-job to run every 10 minutes to a) save the current ghost count, and b) email me if its a new high!

Step-1 create the table

CREATE TABLE [maint_db].[dbo].[rcsi_monitor] 
	(
	date_time DATETIME, 
	table_name VARCHAR(50), 
	ghost_records BIGINT
	);

If this step succeeded the job would end there. If the step failed (-say- because the table already existed) the job would continue to step-2

Step-2 save the current counts to the table

INSERT INTO [maint_db].[dbo].[rcsi_monitor]

SELECT	GETDATE(),
	OBJECT_NAME(object_id),
	version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, 'sampled')
WHERE version_ghost_record_count > 0;

The SELECT statement above is the only novel thing here, and perhaps the most useful take-away. (Note: the DB_ID() means the current database, so ensure it runs under the right one).

Step-3 send an alert – if the current count is the new HIGH SCORE!

IF 
	(SELECT MAX(version_ghost_record_count) FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, 'sampled'))
>=
	(SELECT ISNULL(MAX(ghost_records), 0) FROM [maint_db].[dbo].[rcsi_monitor])
AND
	(SELECT ISNULL(MAX(ghost_records), 0) FROM [maint_db].[dbo].[rcsi_monitor]) > 0
BEGIN
	RAISERROR ('Too many Ghost! AAAAAaaarrrrrrggggghh!', 16, 1)
	RETURN
END

The RAISERROR and RETURN would force the job to fail, triggering an email via Notifications.

 

Fix sp_BlitzLock

I notice whenever there is corruption in a single extended events deadlock report …

Capture
… sp_BlitzLock would not work at all …

Msg 9411, Level 16, State 1, Procedure sp_BlitzLock, Line 185 [Batch Start Line 12]
XML parsing: line 37, character 166, semicolon expected

My work-around was to replace line 196 …

AS ( SELECT CONVERT(XML, event_data) AS deadlock_xml

… with this …

AS ( SELECT CONVERT(XML, REPLACE(event_data,'&',';')) AS deadlock_xml