Difference between revisions of "Database Maintenance Script"
(Created page with "=Automatic/background indexing= It's considered bad practice to create indexes automatically as they should theoretically be individually verified by a DBA first. That said there is nothing technically stopping you from generating and immediately applying the suggested indexes from within the same script, which the script below will attempt to do. As such this version of the script is compatible with maintenance plans and the like and could even be run on a schedule. As...") |
|||
| Line 1: | Line 1: | ||
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: | |||
<ol> | |||
<li><strong>Trim the ELMAH error table</strong><br> | |||
By default the ELMAH error logging system logs and stores every application and server error indefinitely, which can become a waste of disk space.</li> | |||
<li><strong>Automatically index the database based on real world usage</strong><br> | |||
As per the manual [[Database Indexing]] script, this script implements a version that can be run in the background.</li> | |||
</ol> | |||
Unfortunately, TMSi will not have permission to create or modify a maintenance plan on your SQL Server; this task <strong>must</strong> 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. | |||
=Script= | |||
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. | 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. | ||
Revision as of 09:01, 16 September 2022
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:
- 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. - 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.
Script
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.
DECLARE @MinimumImpactThreshold int = 1000 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