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. Automatically index the database based on real world usage
   As per the manual Database Indexing script, this script implements a version that can be run in the background.

Unfortunately, TMSi will not have permission to create or modify a maintenance plan on your SQL Server; this task must be delegated to your IT department. In addition parts of the script require access to the Master database (for retrieving indexing metrics), so the SQL Agent service account must have read permissions to that database for the script to work correctly. This is the default configuration for SQL Server.

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 (e.g. for backing up) it's fine to add this script to the same plan.

Script

DECLARE @ErrorDaysToTrim int = 14 DECLARE @MinimumImpactThreshold int = 1000 DELETE FROM ELMAH_Error WHERE TimeUtc > dateadd(day, -@ErrorDaysToTrim, getdate()) 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 + ')', '') 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

Parameters

As this version of the 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 little benefit and therefore may 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 to further mitigate the small risk of creating an unhelpful index.