Routine operations for MS SQL Server

Routine operations for MS SQL Server

Tuesday, August 9th, 2011

One of the most common causes of non-optimum operation of the system is incorrect or untimely execution of scheduled operations on the DBMS level. It is especially important to carry out these routine procedures in large information systems, which operate under considerable stress and maintain a large number of simultaneous users. The specificity of these systems is that, normal activities are performed automatically (based on configuration).

Performing routine procedures should be automated. To automate these operations, it is recommended to use built-in MS SQL Server: Maintenance Plan.

For MS SQL Server, it is recommended to perform routine operations:

  1. Update statistics
  2. Clearing procedure cache
  3. Defragmenting indexes
  4. Re-indexing the database tables


1. Update statistics

MS SQL Server builds a query plan based on statistical information about the distribution of values in the indexes and tables. Statistical information is collected on the basis of the (sample) data and it is automatically updated when you change the data. Sometimes this is not enough that MS SQL Server steadily built the most optimal execution plan for all queries.

In order to ensure proper operation of the optimizer as MS SQL Server is recommended to regularly update the database statistics MS SQL.

The optimal frequency of updating statistics depends on the size and nature of the load on the system and is determined experimentally. It is recommended to update statistics at least once in a day.

Set the schedule for updating statistics. It is recommended to update statistics at least once in a day. If necessary, refresh the statistics.

2. Clearing procedure of cache

MS SQL Server optimizer caches query plans for the redo. This is done in order to save time that is spent on compiling the query, if the same query is executed and its plan is known.

It is possible, in which MS SQL Server, focusing on outdated statistics, construct sub-optimal query plan. This plan will be saved in the procedure of cache and used in re-calling the same query.

Thus, you should always update statistics after the procedure to clear the contents of the cache.

3. Defragmenting indexes

With heavy use of tables in a database index fragmentation, an effect that can lead to a decrease in the efficiency of queries.

It is recommended to run defragment regularly.

Defragmenting indexes does not lock tables, and will not interfere with other users, but creates an additional burden on the SQL Server. The optimal frequency of performing this routine procedures should be chosen according to the load on the system and the effects obtained from defragmentation. It is recommended to defragment the indexes at least once a day.

4. Re-indexing the database tables

Reindexing table includes the total rebuilding indexes of the database tables, which leads to a substantial optimization of their work. We recommend that you should regularly re-index the database tables.

Reindexing table locks them in career, that could significantly affect the user experience. In connection with this re-indexing, it is recommended to perform during the minimum load of the system.

Control of the routine procedures at the DBMS level

Need to regularly monitor the implementation of regulatory procedures at the level of the DBMS.

Author Name – Pravin Ganore
Company – ESDS Software Solution Pvt. Ltd.

Comments are closed.