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 can be implemented into an SQL maintenance plan 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. 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.
3. Automatically index the database based on real world usage
   As per the manual Database Indexing script, this implements a version that can be run in the background.
4. Automatically rebuild indexes
   Rebuild the database indexes.

Prerequisites

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

Furthermore parts of the script require access to the Master database (for retrieving indexing metrics), so the executing user account (or the SQL Agent account in the case of a maintenance plan) must have read permissions to that database for the script to work correctly.

Stored Procedure

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.

Parameters

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 can decrease performance of the script. You should not change this setting if you have full transaction logging enabled.
2. MinimumImpactThreshold
   As this version of the indexing script creates the indexes without waiting for any further confirmation the MinimumImpactThreshold parameter becomes more important. It is used to avoid creating indexes that carry too little a benefit and therefore have the potential to do more harm than good (indexes create a larger memory footprint and can slow down inserts). Any calculated impact over 1000 is considered a worthwhile index and is the default value for the parameter, but set the value higher still to further mitigate the small risk of creating an unhelpful index.
3. FillFactor
   The fill factor of the databases indexes. Default is 80 percent.

Script

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

DECLARE @ErrorDaysToKeep int = 0 DECLARE @MinimumImpactThreshold int = 1000 DECLARE @fillfactor int = 80 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 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('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) > @MinimumImpactThreshold 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 impact threshold.') 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.')

Scheduling the 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.