Scheduled operations at the DBMS level for MS SQL Server

Publications: Scheduled operations at the DBMS level for MS SQL Server
Instruction to execute the scheduled operations at the DBMS level.
Information can be applied for the client-server variant of 1С:Enterprise 8 when using DBMS MS SQL Server.

General information

One of the most common reasons for non-optimal system operation is incorrect and ill-timed execution of the scheduled operations at the DBMS level. It is especially important to execute these scheduled operations in the large information systems that operate under significant load and service simultaneously a large number of users. The specifics of these systems is that the usual actions executed by DBMS automatically (base on the settings) are not sufficient for the effective work.

If in the operating system there are some symptoms of the performance problems, it should be checked that all the recommended scheduled operations are correctly configured in the system and regularly executed at the DBMS level.

Execution of the scheduled procedures must be automated. To automate these operations, it is recommended to use the built-in tools of MS SQL Server: Maintenance Plan. There are also the other ways to execute these procedures. In this article for each scheduled procedure an example is given for its adjustment using Maintenance Plan for MS SQL Server 2012.

For MS SQL Server it is recommended to execute the following scheduled operations:

  • Update of statistics
  • Cleanup of procedural cache
  • Defragmentation of indexes
  • Re-indexation of database tables

It is recommended to regularly control the timeliness and correctness of execution for these scheduled procedures.

Update of statistics

MS SQL Server builds a query plan based on the statistical information about distribution of values in the indexes and tables. the statistical information is collected based on the part (sample) of data and automatically updated when changing these data. sometimes it is not sufficient in order that MS SQL Server steadily built the optimal plan of the execution of all queries.

In this case, the occurrence of the query performance problems is possible. Meanwhile, the characteristic signs of non-optimal work (non-optimal operation) are observed in the query plans.

In order to ensure the most correct work of MS SQL Server optimizer, it is recommended to regularly update the statistics of MS SQL database.

To update the statistics of all database tables, it is required to execute the following SQL query:

exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'

The update of statistics does not block the tables and will not interfere with the work of other users. The statistics can be updated as often as necessary. It should be noted that the load on DBMS server during update of statistics will increase, that may unfavourably affect the overall performance.

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

The above query update the statistics for all database tables. In the actually operating system the different tables require different rate of statistics update. Analysing the query plans, it is possible to determine which tables are most in need for frequent update of statistics and configure two (or more) different scheduled procedures: for frequently updated tables and for al other tables. This approach will allow significantly reducing the time to update the statistics and the influence of statistics update process on the system operation as a whole.

Adjustment of automated statistics update (MS SQL 2012)

Run MS SQL Server Management Studio and connect to the DBMS server. Open the folder Management and create a new maintenance plan:

Publications: Scheduled operations at the DBMS level for MS SQL Server

Create sub-plan (Add Subplan) and name it «Update statistics». Add to it the task Update Statistics Task from taskbar:

Publications: Scheduled operations at the DBMS level for MS SQL Server

Adjust the schedule of statistics update. it is recommended to update statistics at least once a day. If necessary, the frequency of statistics update can be increased.

Configure the task parameters. To do this, you should click two times on the task in the right bottom angle of the window. In the opened form specify the name of database (or several databases) for which the update of statistics will be executed. In addition, you can specify for which tables you want to update the statistics (if it is known exactly which tables must be specified, set the value All).

The update of statistics must be performed with enabled option Full Scan.

Publications: Scheduled operations at the DBMS level for MS SQL Server

Save created plan. When coming the time specified in the schedule, the update of statistics will be launched automatically.

Cleanup of procedural cache

MS SQL Server optimizer caches the query plans for their reuse. It is done in order to save the time consumed for query compilation in case when such query was already executed and its plan is known.

It is possible a situation when MS SQL Server, focusing on the outdated statistical information, will build non-optimal query plan. This plan will be saved in the procedural cache and use when re-calling the same query. If you updated the statistics, but did not cleaned the procedural cache, then SQL Server may select an old (non-optimal) query plan from cache instead of building the new (more optimal) plan.

Thus, it is recommended always after the statistics update to clean up the content of procedural cache.

To clean up the procedural cache of MS SQL Server, it is required to execute the following SQL query:

DBCC FREEPROCCACHE

This query should be executed directly after the update of statistics. thereafter, the frequency of its execution must coincide with the frequency of statistics update.

Setting the cleanup of procedural cache (MS SQL 2012)

Since the procedural cache must be cleaned during each update of statistics, it is recommended to add this operation to the created subplan «Update statistics». to do this, it is required to open subplan and add to its scheme the task Execute T-SQL Statement Task. Then, it is required to connect the task Update Statistics Task using an arrow with a new task.

Publications: Scheduled operations at the DBMS level for MS SQL Server

In the text of created task Execute T-SQL Statement Task it is required to specify the query «DBCC FREEPROCCACHE»:

Publications: Scheduled operations at the DBMS level for MS SQL Server

Defragmentation of indexes

During the intensive work with the database tables, the effect of index fragmentation arises which can lead to the decrease in the efficiency of queries.

it is recommended to defragment regularly the indexes. To defragment al the indexes of all database tables, it is required to use the following SQL query(substituting preliminary the name of base):

sp_msforeachtable N'DBCC INDEXDEFRAG (, ''?'')'

Defragmentation of indexes does not block the tables and will not interfere with the work of other users, but creates the additional load on the SQL Server. the optimal frequency of execution for this scheduled procedure must be selected according to the system load as a whole and to the effect obtained from the defragmentation. It is recommended to execute the defragmentation of indexes at least once a week.

An execution of defragmentation is possible for one and more tables, not for all the database tables.

Adjustment of index defragmentation (MS SQL 2012)

in the previously created maintenance plan create a new subplan with the name «Indexes defragment». Add to it the task Reorganize Index Task:

Publications: Scheduled operations at the DBMS level for MS SQL Server

Set the schedule of execution for the task of indexes defragmentation. it is recommended to execute the task at least once a week, and in case of high data changeability in the base even for often – up to once a day.

Configure the task specifying the database (or several databases) and selecting the necessary tables. If it is not known exactly which tables should be specified, set the value All.

Publications: Scheduled operations at the DBMS level for MS SQL Server

Re-indexation of database tables

Re-indexation of tables includes the complete reconstruction of database table indexes that leads to the significant optimization of their work. It is recommended to execute the regular re-indexation of database tables. To do re-indexation of all database tables, it is required to execute the following SQL query:

sp_msforeachtable N'DBCC DBREINDEX (''?'')'

Re-indexation of tables blocks them for the whole time of its work that may significantly affect the user work. Due to this, it is recommended to execute re-indexation during the minimal system load.

After re-indexation, it is not necessary to do defragmentation of indexes.

Adjustment of tables re-indexation (MS SQL 2012)

In the previously created maintenance plan create a new subplan with the name «Reindexing». Add to it the task Rebuild Index Task:

Publications: Scheduled operations at the DBMS level for MS SQL Server

Specify the schedule of execution for this task of tables re-indexation. it is recommended to execute the task during the minimal system load, at least once a week.

Configure the task specifying the database (or several databases) and selecting the necessary tables. If it is not known exactly which tables should be specified, set the value All.

Publications: Scheduled operations at the DBMS level for MS SQL Server

Execution control of scheduled procedures at the DBMS level

it is required to regularly control an execution of scheduled procedures at the DBMS level. Below there is an example of execution control for maintenance plan for MS SQL Server 2012.

Open created maintenance plan and select from the context menu item «View History»:

Publications: Scheduled operations at the DBMS level for MS SQL Server

The window will be opened with the execution protocol of all specified scheduled procedures.

Publications: Scheduled operations at the DBMS level for MS SQL Server

Successfully completed task and the tasks performed with errors will be marked with corresponding icons. For the tasks performed with errors the detailed information about the error is available.

[Total: 0    Average: 0/5]

Leave a Reply

Your email address will not be published. Required fields are marked *