SQL Server Performance and Maintenance
Objective
This document is about :
- The different management and optimization tasks to put in place, for system and application data, in order to maintain the SQL Server base.
- The tools provided by SQL Server to monitor components performance.
SQL Server Maintenance
A maintenance plan allows to plan different tasks to enhance the way your instances work. The following tasks can be part of this plan.
|
|
|
|---|---|
|
Check Database Integrity |
Control and verify data and database index pages consistency. |
|
Reorganize Index and update statistics |
Defragment and compact view and base table indexes. This operation allows to enhance index browsing performance. NOTE : A defragmented index ensures that more data pages can be read. More IO and RAM are required. |
|
Rebuild Index |
Reorganise data and index pages by rebuilding (deleting and re-creating) indexes. NOTE : This task enhance index browsing and searching performance. It optimize data distribution and free space on index pages as well, ensuring speed to the database future expansion. |
|
Update Statistics |
Make sure the query optimizer works with updated information regarding data distribution in tables. NOTE : This task allows the optimizer to better access data. |
|
Full Backup |
Save the full database. It is necessary to specify the source database, the destination of files, disks or other options such as the overwriting option. NOTE : This task includes backups cleaning as well. |
|
Differential Backup |
Perform a database differential backup. Only the changes occurred since the last full backup will be saved. |
|
Transaction Log Backup |
Save the log content since the last transaction log backup or full backup. |
|
Maintenance Clean |
Remove backup historic and the agent tasks. NOTE : This task is essential in a maintenance plan. |
Source : https://msdn.microsoft.com/en-ca/library/ms187658(v=sql.120).aspx
SQL Server Performance
The following tools are provided by SQL Server for components monitoring.
|
|
|
|---|---|
|
SQL Server Management Studio - Activity Monitor |
Monitor resources use. This includes the number of pages in progress to the buffer manager, which allows to monitor the server performance and activity with predefined objects and counters. |
|
SQL Server Management Studio - Execution Plan |
Provide real-time statistics on the query execution plan. NOTE : These execution statistics are very useful to solve query performance issues. |
|
Monitor an instance of the database engine. The Profiler is a graphical interface to SQL Trace. NOTE : It is possible to capture and save each event data in a file or in a table for further analysis. |
|
|
Error Log |
Solve issues linked to SQL Server as it contains exclusive informations regarding the events occuring in SQL Server. |
|
Stored Procedures (sp_who, sp_lock) |
Provide many monitoring and administrative tasks (connections, locks). |
|
Database Console Commands |
Allow to control performance statistics and a database logical and physical consistency. |
|
Integrated functions (Transaction Log Backup) |
Display real-time statistics regarding the SQL Server activity, since the server was launch. These statistics are stored in predefined SQL Server counters (@@variable). |
|
Trace flags |
Display informations regarding a specific activity occuring on the server. NOTE : Trace flags allow to identify problems or causes that can impact performance (deadlocks). |
Source : https://msdn.microsoft.com/en-ca/library/hh213686(v=sql.120).aspx
