Microsoft SQL Server Maintenance and Optimisation
Maintenance and optimisation of a Microsoft Access database is simple: Do a Compress and Compact once a week. Monitor Table file sizes and monitor response times.
Things are very much different when using a full-blown relational database such as Microsoft SQL Server. Regular maintenance tasks and monitoring are needed to ensure that the SQL Server database performs efficiently and high availability is maintained.
SQL Server Database maintenance and tuning is a never ending chore. As much as possible should be automated.
The Maintenance Plan Wizard can be used to create basic database maintenance procedures. Care must be taken – the Maintenance Plan Wizard does not perform all necessary maintenance tasks and it can impair performance.
Here is a checklist of the Database Maintenance and Tuning Tasks:
SQL Server Maintenance: Physical File Fragmentation
SQL Server does its best to minimise file fragmentation, but …
- Ensure there is no fragmentation before creating new database and log files.
- Set MDF and LDF sizes instead of letting Autogrowth resize files. If you need to use Autogrowth, choose a fixed amount that won't grow often.
- Defragment Disks used for backups.
- Monitor MDF and LDF file growth, and manually resize them.
- Shrinking MDF and LDF files may cause fragmentation problems.
SQL Server Maintenance: Database and Log Files
MDF and LDF files should be located on their own disks, separate from the Operating System. This will reduce I/O contention, improve performance, and reduce fragmentation. For best SQL Server performance, use a dedicated Server and lots of memory.
SQL Server Maintenance: TEMPDB database
Set Autogrowth to use a fixed amount large enough to avoid growth spikes. If TEMPDB is very active, locate it on its own disk.
SQL Server Maintenance: MSDB database
The MSDB database can grow large storing data like Backup and Restore history, SQL Server Agent Job history, Maintenance Plan history. This database should be regularly cleaned out.
SQL Server Maintenance: Indexes
SQL Server performance greatly depends on the optimisation of the Indexes. Use the Index Tuning Wizard to get recommendations on Indexing. Note that the Indexing requirements may change over time, as volumes and the mix change.
The tasks are:
- Identify and add missing Indexes
- Identify and remove unused Indexes
- Identify and remove duplicate Indexes
- Identify and reduce index fragmentation
- Rebuild the indexes
SQL Server Maintenance: Statistics
SQL Server maintains statistics on indexes which are used by the query optimiser to produce query plans. These statistics must be up-to-date, to ensure that the query optimiser produces the a query plan based on the best information.
SQL Server Maintenance: Data Corruption Detection
To identify data corruption, ensure that "checksum" is turned on for the database. Run DBCC CHECKDB as often as practical.
SQL Server Maintenance: Database and Log File Protection
It is essential to have a fully tested Disaster Recovery plan in place.
Production databases should use the Full Recovery model. Use RESTORE WITH VERIFYONLY to help verify backup integrity. Perform Full Backups daily on all system and user production databases.
Restore backups frequently to verify that they can be restored. Enforce an appropriate data retention policy. Store backups securely off-site.
SQL Server Maintenance: Monitoring
- Monitor Free Space – should have 20% or more free space
- Monitor SQL Server Logs and OS Event Logs
- Monitor Jobs
- Performance Monitor Alerts
- Scheduled Job failure Alerts
- Monitor Blocking and Deadlocks
- Monitor Performance
SQL Server Maintenance: Transact-SQL Performance
Transact-SQL code has a great potential for impinging on performance.
- The code should return minimal data
- Hints should be used in queries
- Stored procedures should be used whenever possible.
- Use SET NOCOUNT ON
- Keep transactions as short as possible
- Minimise the Opening and Closing of connections