About Maintenance Plans – grooming SQL Server

Hello all,

I admit I’m not a big fan of Maintenance Plans (the feature that goes by that name) mainly because of its “do or die” approach. So, it was not by chance that I wrote and keep updating the AdaptiveIndexDefrag procedure. Good maintenance (or lack thereof) may be the difference between good and bad performance. Following up on that train of though, I’ve been meaning to share 6 scripts that I always use to automate maintenance tasks in SQL Server. In a couple minutes I have deployed a comprehensive set of SQL Agent jobs that will get the work done.

So, the first script I execute is “0_database_server_options”. This will:

• Change the Errorlog file limitation from 8 to 15. Always nice to have information available when troubleshooting.
• Change a few sys.configurations such as “Backup compression default”, enable “remote admin connections” and “Optimize for Ad Hoc workloads”. A couple of notes on this step:
• About Backup Compression…
  Fact 1: backup compression significantly increases CPU usage.
  Fact 2: Backup compression is not only a space saving feature, but because a compressed backup is smaller than an uncompressed backup of the same data, compressing a backup typically requires less device I/O and therefore usually increases backup speed significantly. Hence, restore operations will also share from this reduced I/O.
  Also, with compressed backups as default, it is possible to issue backup commands with compression to databases that will not benefit from it, but may even be detrimental in such cases. Therefore, examples such as TDE-enabled databases can be backed up using the WITH NO_COMPRESSION notation, while omitting any compression related notation on a server that has backup compression enabled at the server option will compress backups.
  If the server is not CPU bound, and if most of the databases in your server benefit from this feature, making backup compression a default for all backup operations will generate smaller backups, therefore less backup related I/O.
• About Optimize for Ad-Hoc workloads…
  While the Optimize for Ad-Hoc workloads server option effectively adds a small delay in a second execution, the benefits outweigh the cost in an OLTP environment: if a query is executed countless times, the query plan only gets stored in cache on the second execution which is probably not a major drawback, but if not, memory is saved by not caching query plans that won't get used.
  With this option enabled, when a query executes the first time, only the query hash (or stub) is stored in cache. If the same plan is reused, then it is deemed fit for storing the entire plan in cache.
  Enable the Optimize for Ad-Hoc workloads  server option, if most of the workload on the server is mostly made of ad-hoc queries in an OLTP server.
• Change the model data and log files to autogrow 100MB. Obviously this is a generalization, but still more up to date than the default.
• Remove AUTO_CLOSE and AUTO_SHRINK database options from all databases.
• Change whatever page verify setting to CHECKSUM.

Second, and if there is no other monitoring system, I use the script “1_DatabaseMail_Config”. Just edit the script and enter the proper account information in the configuration variables near the top and run it. Replace with the information for your account.This script will:

• Create a Database Mail profile. Usually there is a distribution list for the DBAs, so I‘m keen on using that address.
• Create an operator using that Database Mail profile.

Third, I deploy the AdaptiveIndexDefrag procedure, usually in MSDB unless there is already a DBA database.

Fourth, I use the script “3_job_AdaptiveIndexDefrag”. This will create a daily job for the AdaptiveIndexDefrag procedure, named “Daily Index Defrag”. It will also notify the previously created operator on the job outcome. The job will always:

• Find some of the most common (default) names for SharePoint databases (step “Sharepoint DB Exceptions”), to add the to the permanent exclusion list, if not already there. SharePoint grooms its own databases, so we should exclude them from any other automated maintenance task. If the AdaptiveIndexDefrag procedure was NOT created in MSDB, simply replace all references to MSDB for whatever database name you chose.
• Execute the AdaptiveIndexDefrag procedure (step “Daily Index Defrag”).
  Note that in this job there are a few differences from the default values for the procedure. We are doing online index rebuild, updating all statistics, Sorting in tempDB (assuming tempDB is properly sized) and restricting maxDOP to 4.
• Purge all historic log data for the index defrag executions using default 90 days (step “Purge Log”).

Fifth, I use the script “4_job_AdaptiveCycleErrorlog”. As the name suggests, this creates a job named “Daily Cycle Errorlog”. Runs daily, but will only cycle the Errorlog when its size is over 20MB or its age over 15 days.

Sixth, using the script “5_job_Maintenance”, create the job “Weekly Maintenance”, and a view in MSDB that allows to quickly check the output for each job step, XML formatted so that it’s easier to view. This job will:

• Execute DBCC CheckDB on all online, read-write user databases. As a best practice, we recommend doing integrity checks at least on a weekly basis. On large databases and for more frequent checks, consider using the PHYSICAL_ONLY parameter. If you need to exclude some database from this step, simply edit the following statement to add your exclusions, either by name or database_id.

SELECT database_id FROM sys.databases WHERE is_read_only = 0 AND state = 0 AND database_id <> 2

to something like

SELECT database_id FROM sys.databases WHERE is_read_only = 0 AND state= 0

AND database_id <> 2 AND database_id NOT IN (somedbid1, somedbid2, somedbid3)

• Execute DBCC UPDATEUSAGE which corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. On this, although the best practice is to run routinely only if the database undergoes frequent Data Definition Language (DDL) modifications, such as CREATE, ALTER, or DROP statements, I find that running this on a weekly basis on all online, read-write user databases under 4GB in size does not pose an issue, and so I run it.
• Executes sp_createstats with indexonly option on all online, read-write user databases. This creates statistics only on columns that are part of an existing index, and are not the first column in any index definition. By creating single-column statistics we increase the number of histograms, which can improve cardinality estimates, query plans, and query performance.
• Clean all MSDB job history older than 30 days. Change this value if you have different requirements.
• Clean maintenance plans txt reports older than 30 days. This is only needed if you still have some specific maintenance plan you wish to keep for some reason.

Seventh, and final step (although optional), I use the script "6_Agent_Alerts" to create SQL Agent based alerts for important error messages, when there are no other monitoring tools to warn the DBA team of important events in SQL Server. The alerts cover the following error codes and severities:

• Severity 10 - Error(s) 825, 833, 855, 856, 3452, 3619, 17179, 17883, 17884, 17887, 17888, 17890 and 28036
• Severity 16 - Error(s) 2508, 2511, 3271, 5228, 5229, 5242, 5243, 5250, 5901, 17130 and 17300
• Severity 17 - Error(s) 802, 845, 1101, 1105, 1121, 1214 and 9002
• Severity 19 - Error(s) 701
• Severity 20 - Error(s) 3624
• Severity 21 - Error(s) 605
• Severity 22 - Error(s) 5180 and 8966
• Severity 23 - Error(s) 5572 and 9100
• Severity 24 - Error(s) 823, 824 and 832

Having these (or other) scripts to deploy on your SQL Servers saves you the trouble of implementing Maintenance Plan objects, and gives you much better control and flexibility on what is happening on your maintenance cycles. After these have been running on a regular basis, you will know that your SQL Server is being proactively managed on what are the most common maintenance tasks. Of course all the logs should be reviewed regularly so that issues can be corrected.

Hope you find this useful.

Here are the scripts:

2_AdaptiveIndexDefrag

Change log:

Until next time!

Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.