Making SQL service plans for 1C: Enterprise 8.x

After another request to tell how to make a plan for servicing the sql-bases used by 1C: Enterprise , I decided to share the experience with everyone at once.
Why this is necessary - if the sql does not serve the database, then its meaning is lost altogether. The main tool is indexes and they should be kept up to date. I did not meet any dogmas not in practice, not in the net, not in courses in 1C itself, and therefore I share my experience.

Often the base works in “normal” conditions. What is meant by this:

• SQL Server is well-powered, i.e. the amount of RAM provided by the SQL server to work, choose from the calculation of 70% of the size of all mdf database files.
• The processor is not loaded more than 50% within 90% of the time.
• There is enough space on the disks (in particular, the temp.db base is used for sorting, 1C uses it in general for all of its livelihoods, so you should take care of the disk space with this base in advance).
• Database recovery mode is “Simple”. (It is empirically found that a large ldf file slows down 1c-ku, and the possibility of recovery from a log file is very doubtful).

It is also worth considering a few nuances:

• When using the Standard edition of SQL, if the index is completely rebuilt, all users will be disconnected from the database, therefore it is worth considering this when deciding whether to run the Weekly maintenance plan (the plan will be described below).
• It should be borne in mind that the 1C server also consumes memory, especially if thin clients or web services are used.
• SQL itself is better to limit the maximum amount of RAM in the server settings, so that when it reaches a critical mass, it would start to clear unnecessary data from RAM beforehand. And in order to grow not to drive the entire server into a stupor.

It is rational under normal conditions to use 2 Weekly maintenance plans (once a week) and Daily (on the remaining 6 days of the week).

Weekly

By service plan items:

1. Rebuilding index. The meaning of the task is to remove all existing indexes and install new ones. (roughly speaking, the inventory and the arrangement of everything in order).
   As parameters:
   
   • The choice of the target database (this will be in almost all tasks, because I will not pay attention to this parameter further within this article).
   • The object in which we select "Tables and Views."
   • Free space options - if the hard disk is small, you can select the “default” option, but I recommend using the “Change free space on page” option, the recommended value is 20%. This will leave a stock of free pages, and will allow longer indexes to keep up to date. ATTENTION: Increases the size of the database.
   • Sort results in tempdb. I think it is not necessary to explain, but I want to warn, at this time tempdb, will grow very much, although sorting in it is intended to speed up the process, be careful, keep a space.
   • Save the index in the "online" mode - a chip available for the enterprise version of SQL. Allows you to re-index without disconnecting clients.
   !!! ATTENTION!!! In the Standard version, when reindexing, clients are disconnected from the database for the duration of this step.
   Settings example

   
2. Update statistics. The task of collecting information about the status of indices in the database. (In general, it is not very relevant after reindexing, but still I do).
   Options:
   
   • An object. All the same tables and views as for rebuilding the index.
   • Refresh. Here we update all the statistics.
   • View Type - Full View.
   Thus, we update the statistics for the entire database.
   Settings example

   
3. Execute T-SQL statement. This execution of an arbitrary command in the SQL language, in particular, interests us

   dbcc proccache 

   As the name implies - clearing the cache.
   Example

   
4. Check the integrity of the database. There seems to be no explanation - make sure that nothing is broken. In the parameters "include indexes" in the test, not for nothing that was rebuilt.
   Settings example

   
5. Backup database. Here we need to talk more, in view of many features. It is better to study this item separately independently in other manuals, the format of this article does not provide for an in-depth study of backup.
   But I want to warn about a couple of nuances:
   
   • SQL does not know how to clean its container, so if you add backups to a file (it is called “Backup Device”), you will eventually clog up all the free space.
   • SQL remembers their backups, because by making one-time backup handles (for example, take the database to another location, or to deploy for a test to another database from the backup), the next “difference” will be counted from it. In order to prevent this, you need to tick "Backup only". There is no such item in the backup task. In general, in the weekly plan I recommend to use the full type of backup.
   • And it would be good to check the copy, let it sleep better.
   • Compression, in general, can be used, but be careful, then differential should also be compressed.
   Settings example

   
6. Clearing the log.
   
   • Backup log and restore.
   • SQL Server Agent Job Log
   • Service plan log.
   I clean everything. As the name implies, cleans events in the SQL log. I believe that events older than 4 weeks are unlikely to interest me, because if there is a problem, then report it within a month.
   Settings example

   
7. Operator notification. Fad again for self-study. But as the name implies, for reporting problems during the implementation of the plan.

')

Daily

Speaking separately does not make sense. Almost everything is similar to Weekly.
The difference in the first task is “Index reorganization”. Tasks are different in that the reorganization is trying to rectify the existing indices, and does not do everything from scratch. The greater the fragmentation, the more often it will cost to run. But in normal conditions, once a day is enough to keep the index up to date until the next rebuilding.You can also use differential backups.

That's all. I repeat, I did not see dogmas in this moment, this version was developed and tested by me. Actually for bases from 6 to 100 GB in size.

I wish you fast and reliable work.
PS Due to the fact that I am not a full-fledged DBA, perhaps my comments are very superficial, I’m happy to read the comments in the comments and the PM.