Database Maintenance Script

In common with most database driven applications there is some routine maintenance that should be performed periodically on the PTS 5 database to keep it working optimally. As such we have developed a script that should be implemented into an SQL maintenance plan (though it can also be run ad-hoc), that will perform the following tasks:

1. Trim the ELMAH error table
   By default the ELMAH error logging system logs and stores every application and server error indefinitely, which can become a waste of disk space.
2. Fix broken scanning sequence references
   In older versions of PTS 5, in limited circumstances, it was possible to delete an activity that was part of the scanning sequence for a prescription type, which would cause an error when the prescription type was subsequently booked in.
3. Adjust erroneous prescription start times
   Runs the script to retroactively fix the issue discussed here. Has no effect on later versions of PTS where this issue was permanently addressed.
4. Index the database based on SQL Server's missing indexes report
5. Drop existing indexes that are deemed to be slowing down updates more than they are speeding up reads
6. Rebuild indexes
7. Reports the amount of prescriptions that are waiting for collection or delivery
   If this number is suspiciously large, it can represent a configuration issue (described here) that will eventually lead to performance degradation.

It's not uncommon for a TMSi support technician to recommend that the script be executed to clear up various issues. If so, please be sure to send them the output from the Messages tab.

A word on automatic index management

Manual index management is always the better option than any sort of automatic process. However as PTS is endlessly customisable and ultimately hits the database with what technically amount to dynamic queries, it's not possible to predict the correct index configuration before the system is used in anger. As such, while we offer this facility to automate index management, if a DBA is willing to commit to analysing missing indexes and monitoring their performance over time, that is considered the more correct approach.

If a DBA is willing to commit to this task the maintenance script can be easily modified to remove the indexing section. The section is the latter part of the script and is headed with "--Index management--". Simply remove everything below this line to opt out of automatic index management.

Prerequisites

The view server state permission is granted in Securables.

The executing user account (or the SQL Agent account in the case of a maintenance plan) must have the view server state permission for the script to work correctly. This permission allows the script to retrieve database metrics and therefore allows it to suggest and apply suitable indexes.

You can grant the view server state permission in Security > Logins > [right-click the user account] > Properties > Securables > View server state > Grant.

Furthermore, TMSi are very unlikely to have permission to create or modify a maintenance plan on your SQL Server, so this task should be delegated to a DBA.

Stored procedure details

Starting with PTS v5.0.3.9, the maintenance script is pre-installed as a stored procedure named MaintenanceScript. A DBA will be able to execute it for you or add it to a maintenance plan.

Whether or not you have the stored procedure installed already, you can run the script below to install and/or update it.

Parameters

All parameters are optional and the default values are fine unless you have an edge case.

1. ErrorDaysToKeep
   This specifies the number of days to retain in the ELMAH error log. This defaults to 0, meaning no errors are kept and the table is truncated instead. Anything other than zero will significantly increase the amount of time it takes for the script to complete. You should also not change this setting if you have full transaction logging enabled.
2. MinimumNewIndexImpact
   Based on SQL Server's missing indexes report. The missing index impact is calculated as the sum of user seeks and user reads, multiplied by the average user impact. If this value is determined to be greater than the MinimumNewIndexImpact parameter, the index is applied. Default is 1000.
3. MaximumBadIndexScore
   Based on SQL Server's index usage stats. The score is calculated as the total user updates divided by the sum of all user seeks, scans and lookups; for example a score of 5 (the default) indicates that there have been five times more updates than there were total seeks, scans and lookups. If this value is determined to be greater than the MaximumBadIndexScore parameter, the index is dropped.
4. FillFactor
   The fill factor of the databases indexes. Default is 80 percent.

Script

This script is subject to change without notice and more tasks may be added in the future.

It is designed to create/update the stored procedure, and then execute said stored procedure immediately afterward, meaning the script both keeps the stored procedure updated and also performs the tasks involved.

IF OBJECT_ID('MaintenanceScript', 'P') IS NOT NULL
	DROP PROCEDURE MaintenanceScript
GO

CREATE PROCEDURE [dbo].[MaintenanceScript] @ErrorDaysToKeep INT = 0
	,@MinimumNewIndexImpact INT = 1000
	,@MaximumBadIndexScore INT = 1000
	,@fillfactor INT = 80
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @WFC INT

	SELECT @WFC = (
			SELECT COUNT(Prescriptions.PrescriptionID)
			FROM [Prescriptions]
			WHERE [Status] = 4
			)

	PRINT ('There are ' + CAST(@WFC AS VARCHAR) + ' prescriptions waiting for collection.')

	IF @WFC > 1000
	BEGIN
		PRINT ('You appear to have "Record Collection" enabled for one or more of your prescription types, but it''s possible that collection is not always being recorded, which can lead to performance and other issues.')
		PRINT ('If necessary this script will mark them all as collected using the current date and time (consult with a PTS admin first):')
		PRINT ('UPDATE [Prescriptions] SET [Status] = CASE WHEN (SELECT CAST(PrescriptionTypes.RecordDelivery AS bit) FROM PrescriptionTypes WHERE PrescriptionTypes.PrescriptionTypeID = Prescriptions.PrescriptionTypeID) = 1 THEN 5 ELSE 6 END, [Collected] = GETDATE(), [CollectedBy] = (SELECT [Id] FROM [AspNetUsers] WHERE [UserName] = ''ADMIN'') WHERE [Status] = 4 AND [Completed] < DATEADD(dd, -28, GETDATE())')
		PRINT ('You should consider whether you need to turn off "Record Collection".')
	END

	DECLARE @WFD INT

	SELECT @WFD = (
			SELECT COUNT(Prescriptions.PrescriptionID)
			FROM [Prescriptions]
			WHERE [Status] = 6
			)

	PRINT ('There are ' + CAST(@WFD AS VARCHAR) + ' prescriptions waiting for delivery.')

	IF @WFD > 1000
	BEGIN
		PRINT ('You appear to have "Record Delivery" enabled for one or more of your prescription types, but it''s possible that delivery is not always being recorded, which can lead to performance and other issues.')
		PRINT ('If necessary this script will mark them all as delivered using the current date and time (consult with a PTS admin first):')
		PRINT ('UPDATE [Prescriptions] SET [Status] = 7, [Delivered] = GETDATE(), [DeliveredBy] = (SELECT [Id] FROM [AspNetUsers] WHERE [UserName] = ''ADMIN'') WHERE [Status] = 6 AND [Completed] < DATEADD(dd,-28,GETDATE())')
		PRINT ('You should consider whether you need to turn off "Record Delivery".')
	END

	IF @ErrorDaysToKeep = 0
	BEGIN
		PRINT ('Truncating Elmah table.')

		TRUNCATE TABLE ELMAH_Error

		PRINT ('Truncated.')
	END
	ELSE
	BEGIN
		PRINT ('Deleting from Elmah table.')

		DELETE
		FROM ELMAH_Error
		WHERE TimeUtc < dateadd(day, - @ErrorDaysToKeep, getdate())

		PRINT ('Deleted from Elmah table.')
	END

	PRINT ('Fixing broken scanning sequence references.')

	DELETE
	FROM PrescriptionTypeScanningSequences
	WHERE PrescriptionTypeScanningSequences.ActivityId IN (
			SELECT PrescriptionTypeScanningSequences.ActivityId
			FROM PrescriptionTypeScanningSequences
			LEFT OUTER JOIN Activities ON PrescriptionTypeScanningSequences.ActivityId = Activities.ActivityID
			WHERE Activities.ActivityID IS NULL
			)

	PRINT ('Fixing erroneous start times.')

	UPDATE Prescriptions
	SET [Started] = [Created]
		,[Requested] = [Created]
		,[TargetCompletionTime] = DATEADD(ss, DATEDIFF(ss, [Started], [TargetCompletionTime]), [Created])
		,[TargetDeliveryTime] = DATEADD(ss, DATEDIFF(ss, [Started], [TargetDeliveryTime]), [Created])
	WHERE (
			[created] > [Started]
			OR [created] > Requested
			)
		AND DATEDIFF(ss, [Started], [Created]) > 60
		AND [Requested] = [Started]

	PRINT ('Clearing devices table.')

	TRUNCATE TABLE [Devices]

	--Index management--
	PRINT ('--Index Management--')
	PRINT ('Dropping bad indexes.')

	DECLARE @DropStatement VARCHAR(max)

	DECLARE index_cursor CURSOR
	FOR
	SELECT 'DROP INDEX IF EXISTS [' + IX.name + '] ON [' + OBJECT_NAME(IX.OBJECT_ID) + '] -- SCORE: ' + CAST((IXUS.user_updates / (IXUS.user_seeks + IXUS.user_scans + IXUS.user_lookups + 1)) AS VARCHAR)
	FROM sys.indexes IX
	INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id
		AND IXUS.OBJECT_ID = IX.OBJECT_ID
	INNER JOIN sys.dm_db_partition_stats PS ON PS.object_id = IX.object_id
	WHERE OBJECTPROPERTY(IX.OBJECT_ID, 'IsUserTable') = 1
		AND IX.name LIKE '%IX%'
		AND ((IXUS.user_updates / (IXUS.user_seeks + IXUS.user_scans + IXUS.user_lookups + 1))) > @MaximumBadIndexScore
	GROUP BY OBJECT_NAME(IX.OBJECT_ID)
		,IX.name
		,IX.type_desc
		,IXUS.user_seeks
		,IXUS.user_scans
		,IXUS.user_lookups
		,IXUS.user_updates
		,IXUS.last_user_seek
		,IXUS.last_user_scan
		,IXUS.last_user_lookup
		,IXUS.last_user_update
	ORDER BY IXUS.user_updates DESC

	OPEN index_cursor

	FETCH NEXT
	FROM index_cursor
	INTO @DropStatement

	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT (@DropStatement)

		EXEC (@DropStatement)

		PRINT ('Index dropped.')

		FETCH NEXT
		FROM index_cursor
		INTO @DropStatement
	END

	CLOSE index_cursor

	DEALLOCATE index_cursor

	IF @DropStatement IS NULL
	BEGIN
		PRINT ('No bad indexes within threshold.')
	END

	PRINT ('Generating create index statements.')

	DECLARE @CreateStatement VARCHAR(max)

	DECLARE db_cursor CURSOR
	FOR
	SELECT 'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') + CASE 
			WHEN dm_mid.equality_columns IS NOT NULL
				AND dm_mid.inequality_columns IS NOT NULL
				THEN '_'
			ELSE ''
			END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + '_' + CONVERT(VARCHAR(255), NEWID()) + ']' + ' ON ' + dm_mid.statement + ' (' + ISNULL(dm_mid.equality_columns, '') + CASE 
			WHEN dm_mid.equality_columns IS NOT NULL
				AND dm_mid.inequality_columns IS NOT NULL
				THEN ','
			ELSE ''
			END + ISNULL(dm_mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + dm_mid.included_columns + ')', '') + ' --IMPACT: ' + CAST(dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans) AS VARCHAR)
	FROM sys.dm_db_missing_index_groups dm_mig
	INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle
	WHERE dm_mid.database_ID = DB_ID()
		AND dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans) > @MinimumNewIndexImpact

	OPEN db_cursor

	FETCH NEXT
	FROM db_cursor
	INTO @CreateStatement

	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT (@CreateStatement)

		EXEC (@CreateStatement)

		PRINT ('Index created successfully.')

		FETCH NEXT
		FROM db_cursor
		INTO @CreateStatement
	END

	CLOSE db_cursor

	DEALLOCATE db_cursor

	IF @CreateStatement IS NULL
	BEGIN
		PRINT ('No missing indexes within threshold. If you are suspicious that an underlying error might be preventing this from finding any indexes, try accessing the SQL Performance Dashboard as this user. Error messages may be more apparent in there.')
	END

	PRINT ('Rebuilding existing indexes')

	DECLARE @Table VARCHAR(255)
	DECLARE @cmd NVARCHAR(500)

	DECLARE TableCursor CURSOR
	FOR
	SELECT TABLE_SCHEMA + '.' + TABLE_NAME
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_TYPE = 'BASE TABLE'

	OPEN TableCursor

	FETCH NEXT
	FROM TableCursor
	INTO @Table

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3), @FillFactor) + ')'

		PRINT (@cmd)

		EXEC (@cmd)

		FETCH NEXT
		FROM TableCursor
		INTO @Table
	END

	CLOSE TableCursor

	DEALLOCATE TableCursor

	PRINT ('Indexes rebuilt.')

	SELECT 'The maintenance script has finished executing. Please see the output from the Messages tab.'
END
GO

EXEC [dbo].[MaintenanceScript]

Running the stored procedure

A DBA will be able to execute the script for you using the SQL Server Management Studio UI, or by using this script if the stored procedure is installed:

EXEC [dbo].[MaintenanceScript] GO

Running it in a maintenance plan

The maintenance plan should be scheduled to run out of hours as there is a marginal performance impact while it is executing. If you already have a PTS maintenance plan in place, such as for backing up, it's fine to add this script to the same plan - but have the backup task occur first.

Ideally the script should be set to run once per week but this is flexible. Running it too infrequently can expose you to the problems the script aims to mitigate.

To configure the maintenance plan to execute the maintenance script, add an "Execute T-SQL Statement" task to it from the toolbox, and set it up to execute the stored procedure.