Acceleration of work 1s 8.3 file. Automation Tips

The phrase “1C slows down” must have been heard by everyone working with products on the 1C:Enterprise platform. Someone complained about it, someone accepted complaints. In this article, we will try to consider the most common causes of this problem and options for solving it.

Let's turn to the metaphor: before finding out why a person did not go somewhere, it is worth making sure that he has legs to walk. So, let's start with the hardware and network requirements.

If Windows 7 is installed:

If Windows 8 or 10 is installed:

Also remember that there must be at least 2 GB of free disk space, and a network connection must have a speed of at least 100 Mb / s.

It does not make much sense to consider the characteristics of servers in the client-server version, because in this case everything depends on the number of users and the specifics of the tasks that they solve in 1C.

When choosing a configuration for a server, keep the following in mind:

• One worker process of the 1C server consumes an average of 4 GB (not to be confused with a user connection, because one worker process can have as many connections as you specify in the server settings);
• The use of 1C and DBMS (especially MS SQL) on one physical server gives a gain when processing large data arrays (for example, closing a month, calculating a budget according to a model, etc.), but significantly reduces performance during unloaded operations (for example, creating and conducting implementation document, etc.);
• Remember that 1C servers and DBMS must be connected via a channel "thick" from 1 GB;
• Use high-performance disks and do not combine the roles of the 1C server and DBMS with other roles (for example, file, AD, domain controller, etc.).

If, after checking the equipment, 1C still “slows down”

We have a small company, 7 people, and 1C "slows down". We turned to specialists, and they said that only the client-server option would save us. But for us, such a solution is not acceptable, it is too expensive!

Carry out routine maintenance in the database*:

1. Start the database in configurator mode.

2. Select the "Administration" item in the main menu, and in it - "Testing and fixing".

3. Set all the checkboxes as in the picture. Click Run.

*This procedure may take from 15 minutes to an hour, depending on the size of the base and the characteristics of your PC.

If this does not help, then we make a client-server connection, but without additional investments in hardware and software:

1. Choose the least loaded computer in the office from among stationary (not notebook): it must have at least 4 GB of RAM and a network connection of at least 100 Mb / s.

2. Activate IIS (Internet Information Server) on it. For this:

3. Publish your database on this computer. There is available material on this topic on the ITS, or contact a support specialist.

4. On user computers, configure access to the database through a thin client. For this:

Open the launch window 1C.

Choose your working base. This is your base here. Click Edit. Set the switch to the "On the web server" position, specify in the line below it the name or IP address of the server on which IIS was activated, and the name under which the database was published. Press "Next".

Set the "Main Startup Mode" switch to "Thin Client" mode. Click "Finish".

We have a rather big company, but not very big either, 50-60 people. We use the client-server option, but 1C is terribly slow.

In this case, it is recommended to separate the 1C server and the DBMS server into two different servers. When separating, be sure to remember: if they remained on the same physical server, which was simply virtualized, then the disks of these servers must be different - physically different! Also, be sure to set up scheduled tasks on the DBMS server when it comes to MS SQL (more on this is described on the ITS website)

We have a rather big company, more than 100 users. Everything is set up in accordance with the 1C recommendations for this option, but when conducting some documents, 1C “slows down” very much, and sometimes a blocking error occurs. Maybe make a convolution of the base?

A similar situation arises due to the size of a very specific accumulation register or accounting (but more often - accumulation), due to the fact that the register is either not “closed” at all, i.e. there are income movements, but there are no expenditure movements, or the number of measurements by which the balances of the register are calculated is very large. There may even be a mix of the two previous reasons. How to determine which register spoils everything?

We fix the time when documents are being processed slowly, or the time and user who has a blocking error.

Open the registration log.

We find the document we need, at the right time, for the right user with the “Data.Conduct” event type.

We look at the entire transaction block until the moment the transaction was canceled, if there was a blocking error, or we look for the longest change (the time from the previous record is more than a minute).

After that, we make a decision, bearing in mind that it is cheaper to collapse this particular register in any case than the entire database.

We are a very large company, more than 1000 users, thousands of documents a day, our own IT department, a huge fleet of servers, we have optimized requests several times, but 1C slows down. We, apparently, have outgrown 1C, and we need something more powerful.

In the vast majority of such cases, it is not 1C that “slows down”, but the architecture of the solution used. When choosing a new business program, remember that it is cheaper and easier to write your business processes in a program than to remake them for some, especially a very expensive program. Only 1C provides such an opportunity. Therefore, it is better to ask yourself: “How to fix the situation? How to make 1C "fly" on such volumes? Let's take a look at a few treatment options:

• Use the technologies of parallel and asynchronous programming that 1C supports (background tasks and requests in a loop).
• When designing the architecture of the solution, refuse to use accumulation registers and accounting registers in the most "narrow" places.
• When developing a data structure (accumulation and / or information registers), follow the rule: "The fastest table to write and read is a table with one column." What is at stake will become clearer if we look at a typical RAUS mechanism.
• To process large amounts of data, use auxiliary clusters where the same database is connected (but in no case should this be done when working interactively !!!). This will bypass standard 1C locks, which will make it possible to work with the database at almost the same speed as when working directly with SQL tools.

It is worth noting that 1C optimization for holdings and large companies is a topic for a separate, large article, so stay tuned for updates to the materials on our website.

How to speed up work in 1C: Accounting 8.3 (version 3.0) or disable routine and background tasks

2019-01-15T13:28:19+00:00

Those of you who have already managed to switch to the new edition of 1C: Accounting 8.3 (version 3.0) have noticed that it has become slower than the deuce. Some strange slowdowns, endless background tasks several times a day, which no one asked her to perform without our knowledge.

Immediately after the transition, my accountants told me that the new edition of 1C: Accounting 3.0 frankly slows down in comparison with the previous ones! And it's impossible to work.

I began to understand and very soon found out that the main cause of freezes and subsequent user dissatisfaction are routine and background tasks, many of which are enabled by default, although for the vast majority of accountants they are not necessary.

Well, for example, why do we need to run the "Text Extraction" task a hundred times a day if we do not perform a full-text (accountants, don't be scared) search across all the objects in our database.

Or why constantly download exchange rates if we do not have currency transactions or we do them occasionally (and before that we ourselves can click the download rates button).

The same applies to the constant attempt by 1C to connect to the site and check and update the bank classifiers. What for? I myself will press the button to update the classifiers if I do not find the right bank by its BIC.

About how to do it on the points below.

1. Go to the "Administration" section and select the "Maintenance" item in the action panel ():

2. In the window that opens, find and select the "Regular and background tasks" item:

3. Open each job that has the On column. worth a jackdaw.

4. Uncheck "Enabled" and click the "Save and Close" button.

5. Do this with each of the included tasks and enjoy the new edition. In general, in my opinion, it is much better than the deuce.

At the same time, the platform will still turn on some of the scheduled tasks that you have disabled.

The 1C system occupies a dominant position in the automation market for small and medium-sized businesses. If a company has chosen a 1C accounting system, then usually almost all employees work in it, from ordinary specialists to management. Accordingly, the speed of the company's business processes depends on the speed of 1C. If 1C works at an unsatisfactory speed, then this directly affects the work of the entire company and profit.

Actually there is three methods of 1C acceleration:

• Increasing hardware capacity.
• Optimization of operating system and DBMS settings.
• Optimization of code and algorithms in 1C.

The first method requires the purchase of equipment and licenses, the third requires a lot of labor for programmers and, as a result, both ways result in significant financial costs. First of all, you need to pay attention to the program code, since no increase in server capacity can compensate for incorrect code. Any programmer knows that with just a few lines of code it is possible to create a process that will fully load the resources of any server.

If the company is confident in the optimality of the program code, and it is still running slowly, usually the management decides to increase the server capacity. At this point, a logical question arises: what is missing, how much and what needs to be added as a result.

The 1C company gives a rather vague answer to the question of how many resources are needed, we wrote about it earlier in our posts. And so you have to independently conduct experiments and figure out what the performance of 1C depends on. The performance experiments at EFSOL are described below.

When working with 1C 8.2, especially with configurations that use managed forms, a strange fact was noticed: 1C runs faster on a workstation than on a powerful server. Moreover, all the characteristics of the workstation are worse than those of the server.

Table 1 - Configurations on which initial testing was carried out

The workstation shows a performance of 155% more than a 1C server with superior performance. We began to figure out what was the matter and narrow the circle of searches.

Figure 1 - Performance measurements on the workstation by the Gilev test

The first suspicion was that Gilev's test was inadequate. Measurements of opening forms, posting documents, generating reports, etc. using instrumentation tools showed that the Gilev test gives an estimate proportional to the actual speed of work in 1C.

Number and frequency of RAM

An analysis of the information available on the Internet showed that many write about the dependence of 1C performance on the memory frequency. It is from the frequency, and not from the volume. We decided to test this hypothesis, since we have a RAM frequency of 1066 Mhz on the server versus 1333 Mhz on the workstation, and the amount of RAM on the server is already much higher. We decided to put not 1066 Mhz, but 800 Mhz right away in order to make the effect of the dependence of performance on the memory frequency more visible. The result - productivity fell by 12% and amounted to 39.37 units. We installed memory with a frequency of 1333 Mhz instead of 1066 Mhz on the server and got a slight increase in performance - about 11%. Productivity was 19.53 units. Accordingly, it's not about memory, although its frequency gives a small increase.

Figure 2 - Performance measurements on the workstation after lowering the frequency of the RAM

Figure 3 - Performance measurements on the server after increasing the frequency of RAM

Disk subsystem

The next hypothesis was related to the disk subsystem. Two hypotheses immediately arose:

• SSDs are better than SAS drives, even if they are in raid 10.
• iSCSI is slow or not working correctly.

Therefore, a regular SATA disk was installed in the workstation instead of an SSD, and the same was done with the server - the base was placed on a local SATA disk. As a result, performance measurements have not changed in any way. Most likely, this is happening, since there is enough RAM and the disks are practically not used in any way during the test.

CPU

The processors on the server, of course, are more powerful and there are two of them, but the frequency is slightly lower than on the workstation. We decided to check the effect of the processor frequency on performance: there were no processors with a higher frequency at hand for the server, so we lowered the processor frequency on the workstation. We immediately reduced it to 1.6 so that the correlation manifested itself brighter. The test showed that performance dropped significantly, but even with a 1.6 processor, the workstation produced almost 28 units, which is almost 1.5 times more than on the server.

Figure 4 - Performance measurements on a workstation with a 1.6 Ghz processor

video card

There is information on the Internet that a video card can affect the performance of 1C. We tried the workstation's integrated video, an Nvidia NVIDIA® Quadro® 4000 2 Gb DDR5 professional adapter, an old GeForce 16MbSDR graphics card. During the Gilev test, no significant difference was noticed. Perhaps the video card still affects, but in real conditions, when you need to open managed forms, etc.

At the moment, there are two suspicions why the workstation runs faster even with noticeably worse performance:

1. CPU. The type of processor on the workstation is better suited to 1C.
2. Chipset. Other things being equal, our workstation has a newer chipset, which may be the reason.

We plan to purchase the necessary components and continue tests in order to finally find out what the performance of 1C depends on to a greater extent. While the process of approval and procurement is underway, we decided to perform optimization, especially since it costs nothing. The following steps have been identified:

Stage 1. System setup

First, let's make the following settings in the BIOS and operating system:

1. In the server BIOS, disable all settings to save processor power.
2. Select the "Maximum performance" plan in the operating system.
3. The processor is also tuned for maximum performance. This can be done using the PowerSchemeEd utility.

Stage 2. Setting up the SQL server and the 1C:Enterprise server

We make the following changes to the settings of the DBMS server and 1C:Enterprise.

1. Configuring the Shared Memory protocol:
   
   
   • Shared Memory will be enabled only on the platform starting from 1C 8.2.17, on earlier releases Named Pipe will be enabled - somewhat inferior in speed. This technology only works if 1C and MSSQL services are installed on the same physical or virtual server.
2. It is recommended to put the 1C service into debug mode, paradoxically this gives a performance boost. By default, debugging is disabled on the server.
3. SQL server setup:
   
   
   • We only need a server, the rest of the services that belong to it and, perhaps, someone uses them, only slow down the work. We stop and disable such services as: FullText Search (1C has its own full-text search mechanism), Integration Services, etc.
   • Set the maximum amount of memory allocated to the server. This is necessary in order for the sql server to count on this amount and clean up the memory in advance.
   • Set the maximum number of threads (Maximum worker threads) and set the increased server priority (Boost priority).

Stage 3. Setting up a working database

After the DBMS server and 1C:Enterprise are optimized, we proceed to the database settings. If the base has not yet been expanded from the .dt file, and you know its approximate size, then it is better to immediately indicate the initialization size of the primary file with “>=” the size of the base, but this is a matter of taste, it will still grow when deployed. But Auto-increase in size must be specified: approximately 200 MB per database and 50 MB per log, because. default values ​​- growth by 1MB and by 10% slow down the server very much, when it needs to increase the file with every 3rd transaction. It is also better to store the base file and the log file on different physical disks or RAID groups if a RAID array is used, and limit the growth of the log. It is recommended to move the Tempdb file to a high-speed array, since the DBMS accesses it quite often.

Stage 4. Setting up scheduled tasks

Scheduled tasks are created quite simply using the Maintenance Plan in the Management section, using graphical tools, so we will not describe in detail how this is done. Let's dwell on what operations need to be performed to improve performance.

• Indexes should be defragmented and statistics updated on a daily basis. if index fragmentation is > 25%, this will drastically reduce server performance.
• Defragmenting and updating statistics - done quickly and does not require disconnecting users. It is also recommended to do daily.
• Full reindexing - done with a database lock, it is recommended to do it at least once a week. Naturally, after a complete reindexing, indexes are defragmented and statistics are updated immediately.

As a result, with the help of fine-tuning the system, SQL server and working base, we managed to increase productivity by 46%. The measurements were carried out using the 1C instrument and using the Gilev test. The latter showed 25.6 units against 17.53 which were originally.

Brief conclusion

1. The performance of 1C does not depend much on the frequency of the RAM. When a sufficient volume is reached, further memory expansion does not make sense, since it does not lead to an increase in performance.
2. The performance of 1C does not depend on the video card.
3. The performance of 1C does not depend on the disk subsystem, provided that the queue for reading or writing disks is not exceeded. If SATA drives are installed and they have not exceeded the queue, then installing an SSD will not improve performance.
4. The performance is quite dependent on the frequency of the processor.
5. With proper configuration of the operating system and MSSQL server, it is possible to achieve an increase in 1C performance by 40-50% without any material costs.

ATTENTION! A very important point! All measurements were performed on a test base using the Gilev test and 1C instrumentation tools. The behavior of a real database with real users may differ from the results obtained. For example, in the test database, we did not find any dependence of performance on the video card and the amount of RAM. These conclusions are rather doubtful and in real conditions these factors can have a significant impact on performance. When working with configurations that use managed forms, a video card is important and a powerful graphics processor speeds up work in terms of drawing the program interface, visually this is manifested in faster 1C operation.

Is your 1C running slowly? Order IT maintenance of computers and servers by EFSOL specialists with many years of experience or transfer your 1C to a powerful and fault-tolerant 1C virtual server.

System integration. Consulting

In many ways, the optimization of 1C and the speed of work depends on working with locks, queries and indexes. We will try to answer the question “how to speed up the work of 1C” (the question of how to speed up the launch of 1C, we will consider in another article) and avoid user complaints about “long documents”, which inevitably affects business processes.

Part 3. Performance 1C

Locks in 1C 8.3: search and elimination in the code, transfer to managed locks

Locks are part of the ACID mechanism. Consider its concept, presented in the form of a simplified diagram, using the example of SQL SERVER

In automatic mode, locks are managed by the DBMS itself. At the same time, side effects appeared on MS SQL Server, such as blocking of empty tables and border data range (Serializable level), which created additional problems in multi-user work. To solve these problems, 1C has created managed locks.

1C Managed locks

The locking mechanism was moved to the 1C server, and at the DBMS level, isolation was reduced to a minimum. On MS SQL, the isolation level was lowered to Read Committed with the shared lock mechanism on the 8.2 platform and the row versioning mechanism on the 8.3 platform (the so-called Read Committed Snapshot Isolation). More precisely, this is the database property of the same name and two Read Committed modes of operation, depending on this parameter.

With the last isolation level (RCSI), the mechanism allowed reading and writing transactions on the same resources not to intersect on the DBMS server. All the main work was taken over by the 1C blocking service, which determines, based on native metadata, whether or not to start transactions on the DBMS server so that there are no violations of business logic. Problems with blocking empty tables and boundary ranges are a thing of the past.

DBMS	Type of blocking	Transaction isolation level	Reading outside of a transaction
Automatic locks
File Database	tables	Serializable	Dirty read
MS SQL Server	Entries		Dirty read
IBM DB2	Entries	Repetable Read or Serializable	Dirty read
PostgreSQL	tables	Serializable	Consistent reading
Oracle Database	tables	Serializable	Consistent reading
Managed Locks
File Database	tables	Serializable	Dirty read
MS SQL Server 2000	Entries	Read committed	Dirty read
MS SQL Server 2005 and higher		Read Committed Snapshot	Consistent reading
IBM DB2 up to version 9.7	Entries	Read committed	Dirty read
IBM DB2 version 9.7 and higher	Entries	Read committed	Consistent reading
PostgreSQL	Entries	Read committed	Consistent reading
Oracle Database	Entries	Read committed	Consistent reading

In order to find out what lock mode the 1C program database is in, you need to execute the following request from SSMS in the context of the desired database:

Blocking 1C. The user will not wait on locks, 1C will be accelerated if certain rules are followed:

• The duration of transactions should be as short as possible in time. Carrying out lengthy calculations in a transaction in 100% of cases will lead to blocking when working on an OLTP system.
• Excluded are lengthy external operations within a transaction, such as sending and receiving email confirmations, working with the file system, and other additional actions. All operations should be placed in pending short tasks.
• Requests are optimized as much as possible.
• Indexes should only be created as needed to ensure optimal query performance within the application.
• Minimized inclusions in the clustered index of frequently updated columns. Updating the column/s of a clustered index key requires a lock, both on the clustered index and on all non-clustered indexes (because their locator row contains the clustered index key).
• Where possible, a covering index is created and used to reduce data retrieval time.
• The use of the lowest isolation level by transactions, which will require a transition to managed locking mode.

Block diagnostic tools:

• Technology Journal;
• Performance management center from the 1C toolkit;
• Cloud services of Gilev;

Below is an example of system monitoring by the Gilev service. The total duration of blocking is ~15 hours. Over 400 active users. After decision-making and optimization, timeouts are less than a minute, and the number of blocks has been reduced by ~670 times.

It was:

It became:

In a situation where “everything hangs and takes a long time”, and monitoring services are not configured or not used at all, remembering the Pareto principle, you need to focus on the code.

In automatic mode, the presence of locks on the server can be detected using a system procedure in the context of the required database. This stored procedure allows you to determine the mode in which locks work, their status, type, and so on:

Having finalized the procedure for 1C, you can get visual information about what is currently happening on the server, taking into account the specifics of 1C tables:

Fragment 1

//Locks in terms of 1C SELECT * FROM dbo.ReturnLockName1C(DEFAULT,DEFAULT) as t Where TableName1C IS NOT NULL ORDER BY t.Resource

The use of this mechanism allows you to get complete information about the available locks at the current moment. If there are only S-locks in the report, the problem could be a long-running query or queries. To determine the cause and place of their appearance in the code, you can go in different ways: use SQL server DMO objects (but keep in mind that the data from them is reset after the server is restarted) or configure the Data Collector by saving monitoring data in tables for a certain time. The main thing is to get the texts of problem requests.

Using SQL Server DMOs

We display the server start date to understand the relevance of the data. We break the package by reading rating (physical, logical, processor load). In this case, the master data from sys.dm_exec_query_stats is used. The text of the request is translated into 1C terms. If you can understand the context of the call from the text of the request, then it remains to look at the query plan, find problematic operators and understand what can be done.

Fragment 2

//start time SELECT sqlserver_start_time FROM sys.dm_os_sys_info; //Top requests for physical reads SELECT TOP (50) (total_physical_reads) AS Total_physical_reads,

Identifying problem queries as a result of Data Collector collection

With this tool, you can rank data by the required parameters, such as CPU usage, duration, logical I/O, physical reads, which allows you to save complete statistics for further analysis, despite the restart of the SQL server.

After the problem requests are collected by the server without third-party monitoring, you can rank the received data according to the necessary parameters.

Further, by turning on the technological log and specifying in the settings "search by string" and the part of the query that is guaranteed to occur, you can find out where the problematic query came from. If there are several databases on the server or the user name is known, it is worth adding additional fields for the filter in order to reduce the load on the server when collecting the technological log.

An example of a problematic request and a sample of setting up a technological log:

Query optimization as an opportunity to speed up 1C 8.3

The consequences of non-optimal queries can manifest themselves in the form of lengthy posting of documents, excruciatingly long report generation, system freezes, and other unpleasant events.

When working with queries, DO NOT:

• Join tables with subqueries;
• Connect ordinary tables with virtual ones;
• Use logical "OR" in conditions;
• Use subqueries in join conditions;
• Receive data through a dot from fields of a composite type without the "Express" keyword.

When working with queries, you CAN:

• Create indexes on query conditions, join, aggregation and sort fields;
• Virtual tables must be filtered using filter options.

The use of indexes and their impact on the quality of system performance

A lot has been written about indexes, about the need to use them and the impact on the quality of the system. Let's try to understand the intricacies of the "device" of indexes, applications and advantages over conventional tables.

Indexing is an important part of the DBMS core. Missing indices, or vice versa, their excessive number, affect the sampling rate, modification, addition and deletion of data. Let's consider indexing using the example of the most common DBMS from Microsoft.

For a general understanding of how this works, let's look at the details of the device by the data storage mechanism, which we usually represent in the form of a table (for example, Excel).

The unit of physical data storage is a page - an 8 KB module that belongs to only one object (for example, a table or index). The page is the smallest unit for reading and writing. Pages are organized into extents. The extent consists of 8 consecutive pages. Extent pages can belong to one or more objects. If the pages belong to more than one feature, the extent is called a "mixed" extent.

Its contents can be viewed below:

Now that we have an idea of ​​how a disk storage unit is organized, let's talk more about tables and indexes.

By default, if no special T-SQL statements are used, an empty table is created as a "heap" − a simple set of pages and extents. The data on the heap has no logical order. The SQL Server kernel keeps track of which pages and extents belong to a particular object using special system pages called Index Allocation Maps. Every table or index has at least one IAM page, called the "first IAM page".

Thus, after creating a regular table, by default, a chaotic arrangement of data is obtained. You can view the status of a table using the following procedure:

The main indexes used by the 1C platform

Fragment 3

Myths and reality:

Myth one: clustered indexes and a data table are two different entities stored separately from each other.

Myth two: there can be many clustered indexes in one table.

Downloaded the program for DBMS optimization. Created recommended indexes. Sampling speed increased by 50%. Changing and adding data slowed down 7 times.

Clustered (clustered) index

Clustered indexes are a set of pages that sort and store rows of data in tables or views based on their key values, the columns included in the index definition. There is a limit for this kind of indexes of 16 columns and 900 bytes. For each table there is only one clustered index, because data rows can only be sorted in one order. Creating a clustered index occurs by reorganizing the table rather than copying the data, which makes it possible to keep the table as a B-tree.

Fragment 4

SELECT NAME, TYPE, TYPE_DESC FROM sys.indexes WHERE object_id = OBJECT_ID("TraceData")

Non-Clustered Index

Nonclustered indexes have a structure separate from data rows. A non-clustered index contains the values ​​of the clustered index key, and each entry contains the key of the clustered index (not RID, because 1C tables do not use heaps, with rare exceptions).

You can add non-key columns to the leaf level of a non-clustered index and bypass the existing limit on index keys (900 bytes and 16 key columns) by running fully indexed queries.

After adding a non-clustered index, the data was copied, and another object appeared:

Fragment 5

SELECT NAME, TYPE, TYPE_DESC FROM sys.indexes WHERE object_id = OBJECT_ID("TraceData")

Scheme of the clustered index after getting it from the heap in the form of a balanced tree:

Schema of a non-clustered index obtained from a clustered table (note that the row locator column has a clustered index key):

Impact of indexes on query performance

The query optimizer uses the index to search the key columns of the index, finds where the requested rows are stored, and extracts matching rows from there. An index lookup is much faster than a table lookup because, unlike a table, the index often contains fewer columns per row, and the rows are sorted in order.

Creating a lot of indexes leads to the fact that the fetch speed increases, and the write speed during modification decreases significantly. To solve this problem, first of all, you need to delete unnecessary indexes or block them without deleting them first, which will allow you to simply enable them, if such a need arises.

Note that the clustered index must never be blocked, because this will close access to the table data. This only applies to indexes that you have created yourself through T-SQL. The reason for creating indexes using T-SQL, bypassing 1C:Enterprise, is primarily due to the limited capabilities of the 1C platform in terms of index manipulation and inclusion of additional fields in the created / received index.

T-SQL statement that performs the action to lock the index:

//Lock a single index on the table -ALTER INDEX _Reference22_ByPredefinedIDNotUniq ON _Reference22 DISABLE; //Include the desired index -ALTER INDEX _Reference22_ByPredefinedIDNotUniq ON _Reference22 REBUILD;

In addition to the steps above, it is important to create a filegroup on a physical disk that does not contain the current database files and move non-clustered indexes there. This will speed up data modification by parallelizing their recording.

Defining required or redundant indexes to speed up query execution

By default, 1C creates a certain basic set of indexes. Often, they are simply not enough. SQL Server has mechanisms that make it possible to understand, based on the workload, how necessary the existing indexes are.

The Database Engine Tuning Advisor analyzes databases and makes recommendations to optimize query performance. It can be used to select and create optimal index sets without having an expert level understanding of database structure or the internal processes of SQL Server. The Database Engine Tuning Advisor allows you to perform the following tasks:

• Troubleshooting the performance of a particular problematic query;
• Setting up a large set of queries on one or more databases.

DMOs (dynamic management objects), which include dynamic management views and dynamic management functions. For example, a T-SQL statement can retrieve all indexes that have not been used since the server was last started.

Fragment 6

WITH vl as (SELECT OBJECT_NAME(I.object_id) AS objectname, I.name AS indexname, I.index_id AS indexid FROM sys.indexes AS I INNER JOIN sys.objects AS O ON O.object_id = I.object_id WHERE I.object_id > 100 AND I.type_desc = "NONCLUSTERED" AND I.index_id NOT IN (SELECT S.index_id FROM sys.dm_db_index_usage_stats AS S WHERE S.object_id=I.object_id AND I.index_id=S.index_id AND database_id = DB_ID("Database_id '))) SELECT objectname,T1.NameTable1C, indexid, indexname FROM vl OUTER APPLY dbo.ReturnTableName1C(objectname) as T1 ORDER BY objectname, indexname;

An instruction with which you can create the necessary indexes recommended by the DBMS engine:

Fragment 7

SELECT T1.NameTable1C as Table_Name_1C, "CREATE INDEX " + " ON "
The query optimizer detects the need to create a missing index during query execution plan generation. It saves this information in XML ShowPlan. Because If the query plans are hashed and the instructions persist (until the next server restart), they can be retrieved, processed, and prepared to create the necessary indexes for any execution plan in the cache. It is worth paying attention to the frequency of query execution: the higher it is, the more relevant are the results of the query execution and, accordingly, the collected indicators. If the request was executed once, its results are not so indicative.

Fragment 8

CROSS APPLY query_plan.nodes('//StmtSimple") AS stmt(stmt_xml) WHERE stmt_xml.exist("QueryPlan/MissingIndexes") = 1) SELECT TOP 30 DatabaseName as Database_Name, TableName as Table_Name, T1.NameTable1C as Table_1CName, equality_columns as compare_columns, include_columns as columns_to_include,

Fragment 9

USE [DatabaseName] GO CREATE NONCLUSTERED INDEX ON .[_Document497] ([_Fld12771_TYPE],[_Fld12771_RTRef]) INCLUDE ([_Date_Time],[_Fld12771_RRRef],[_Fld12782RRef],[_Fld12784]) GO Some features of indexing by aggregate fields and sorting fields.

Creating an index on the columns specified in the ORDER BY clause helps the query optimizer quickly organize the result set because the column values ​​are pre-sorted in the index. The internal implementation of the GROUP BY mechanism also sorts the column values ​​first to quickly group the required data.

When using typical recommendations, it is worth checking the result before and after optimization. Let's give an example of using the logical union "OR" and its alternative (to eliminate the problem with standard recommendations) - a technique for changing a query through the "JOIN ALL" syntax.

The 1C request itself with "OR":

SELECT Code, Name, Link FROM Directory.Contractors AS Contractors WHERE Contractors.Code = "000000004" OR Contractors.Code = "0074853" OR Contractors.Code = "000000024" OR Contractors.Code = "009679294" OR Contractors.Code = " 0074742" OR Contractors.Code = "000000104";

Query modification with "JOIN ALL":

SELECT Code, Name, Link FROM Directory.Counterparties AS Counterparties WHERE Counterparties.Code = "000000004" CONNECT ALL SELECT Code, Name, Link FROM Directory.Counterparties AS Counterparties WHERE Counterparties.Code = "0074853" COMBINE ALL SELECT Code, Name, Link FROM Directory.Counterparties AS Counterparties WHERE Counterparties.Code = "000000024" UNITE ALL SELECT Code, Name, Link FROM Directory.Counterparties AS Counterparties WHERE

Actual query plan (for ease of display and performance comparison, queries are intercepted and executed in SSMS):

In this case, after optimization, performance dropped by half due to repeated use of the Key Lookup operator, which is always followed by the Nested Loops operator. Therefore, using a query optimization scheme, you should measure the target time before and after using improvements. This example is shown for the purpose of “trust but verify”, as there may be inconsistency between typical recommendations and practical tasks.

We often get asked questions like:

• because of what the server 1C slows down?
• computer with 1C works very slowly
• client 1C is terribly slow

Sometimes, as a solution to the issue, we offer our clients a server for 1C for rent without brakes, with a choice of server configuration and operating system, you can configure the server online on our partner's website, following the link https://1cloud.ru chapter Services, chapter Virtual server.

What to do and how to win it, and so on in order:

Clients work very slowly with the server version of 1C

In addition to the slow work of 1C, there is also slow work with network files. The problem occurs during normal operation and with RDP

to solve this, after each installation of the Seven or the 2008 server, I always run

netsh int tcp set global autotuning=disabled

netsh int tcp set global autotuninglevel=disabled

netsh int tcp set global rss=disabled chimney=disabled

and the network works without problems

sometimes the best is:

netsh interface tcp set global autotuning= HighlyRestricted

here is what the setup looks like

Configure Antivirus or Windows Firewall

How to configure the Anti-Virus or Windows firewall for the operation of the 1C server (a bundle from the 1C Server: Enterprise and MS SQL 2008, for example).

Add rules:

• If the SQL server accepts connections on the standard TCP port 1433, then we allow it.
• If the SQL port is dynamic, you must allow connections to the %ProgramFiles%\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe application.
• Server 1C works on ports 1541, cluster 1540 and range 1560-1591. For completely mystical reasons, sometimes such a list of open ports still does not allow connections to the server. To make it work for sure, allow the range 1540-1591.

Server / Computer Performance Tuning

In order for the computer to work with maximum performance, you need to configure it for this:

1. BIOS settings

• In the server BIOS, disable all settings to save processor power.
• If there is "C1E" & be sure to DISCONNECT!!
• For some not very parallel tasks, it is also recommended to turn off hyperthreading in the bios
• In some cases (especially for HP!) you need to go into the server's BIOS and turn OFF the items there, in the name of which there are EIST, Intel SpeedStep and C1E.
• Instead, you need to find in the same place the items related to the processor, in the name of which there is Turbo Boost, and ENABLE them.
• If the BIOS has a general indication of the power saving mode & enable it in the maximum performance mode (it can also be called "aggressive")

2. Scheme settings in the operating system - High performance

Servers with Intel Sandy Bridge architecture can dynamically change processor frequencies.

Sometimes the solution to the problem of slow operation of the 1C server is outdated or broken equipment, in this case we offer customers a server for 1C for rent without brakes, with a choice of server configuration and operating system, you can find it on our partner's website, by the link https://1cloud.ru section Services, section Virtual server.

If you have any questions please contact:

• call +7-812-385-55-66 in St. Petersburg
• write to the address
• Leave an application on our website on the page "Online application"