Visual Basic Programmer

All about SQL Server

SQL Server Maintenance and Optimisation

Maintenance and optimisation of a Microsoft Access database is simple: Do a Compress and Compare 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 tuninng 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:

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.

Database and Log File Management

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.

TEMPDB database Maintenance

Set Autogrowth to use a fixed amount large enough to avoid growth spikes. If TEMPDB is very active, locate it on its own disk.

MSDB database Maintenance

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.

Index Maintenance

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

Statistics Maintenance

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.

Data Corruption Detection

To identify data corruption, ensure that "checksum" is turned on for the database. Run DBCC CHECKDB as often as practical.

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.

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

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