Area of ​​special attention – Maximum Degree of Parallelism option. About interesting things from the world of IT, instructions and reviews Sql maximum degree of parallelism

Optimization of 1C work. Setting up MS SQL server

Enable Database instant file initialization

• Database creation
• Add files, logs, or data to an existing database
• Increasing the size of an existing file (including auto-grow operations)
• Restoring a database or filegroup

To enable the setting:

1. On the computer where the backup file will be created, open the Local Security Policy application (secpol.msc).
2. Expand the Local Policies node in the left pane, and then click Assign user rights.
3. In the right pane, double-click Perform volume maintenance tasks.
4. Click the “Add” button for a user or group and add here the user under which the MS SQL Server is running.
5. Click the Apply button.

Enable the Lock pages in memory option

This setting controls which accounts can store data in RAM so that the system does not send pages of data to virtual memory on disk, which can improve performance.

To enable the setting:

1. From the Start menu, select Run. In the Open field, enter gpedit.msc.
2. In the Local Group Policy Editor console, expand Computer Configuration, then Windows Configuration.
3. Expand Security Settings and Local Policies.
4. Select the User Rights Assignment folder.
5. The policies will be shown in the details panel.
6. In this panel, double-click the Lock pages in memory option.
7. In the Local Security Option - Memory Pages Lock dialog box, select Add a user or group.
8. In the Select: Users, Service Accounts or Groups dialog box, add the account under which you run the MS SQL Server service.
9. For the changes to take effect, reboot the server or log in as the user under which you run MS SQL Server.

Disable DFSS for disks.

The Dynamic Fair Share Scheduling mechanism is responsible for balancing and distributing hardware resources between users. Sometimes its operation can negatively affect the performance of 1C. To disable it for disks only, you need to:

1. Find the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\TSFairShare\Disk branch in the registry
2. Set the EnableFairShare parameter value to 0

Disable data compression for directories containing database files.

When compression is enabled, the OS will try to additionally process files during modification, which will slow down the recording process itself, but will save space.

To disable compression of files in a directory, you must:

1. Open directory properties
2. On the General tab, click Other
3. Uncheck the “Compress” contents flag to save disk space.

Set the Max degree of parallelism parameter to 1.

This parameter determines how many threads one request can be executed in. By default, the parameter is 0, which means that the server itself selects the number of threads. For databases with a typical 1C load, it is recommended to set this parameter to 1, because in most cases this will have a positive effect on query performance.

To configure the parameter you need to:

1. Open server properties and select the Advanced tab
2. Set the parameter value to one.

Limit the maximum memory size of MS SQL Server.

Memory for MS SQL Server = Memory for everything – Memory for OS – Memory for 1C server

For example, the server has 64 GB of RAM installed, you need to understand how much memory to allocate to the DBMS server so that it is enough for the 1C server.

For normal operation of the OS, in most cases 4 GB is more than enough, usually 2-3 GB.

To determine how much memory a 1C server requires, you need to look at how much memory the processes of a server cluster occupy at the height of the working day. These processes are ragent, rmngr and rphost; these processes are discussed in detail in the section dedicated to the server cluster. Data should be taken precisely during the period of peak work activity, when the maximum number of users is working in the database. Having received this data, you need to add 1 GB to it - in case you start “heavy” operations in 1C.

To set the maximum amount of memory used by MS SQL Server, you must:

1. Launch Management Studio and connect to the desired server
2. Open server properties and select the Memory tab
3. Specify the value of the Maximum server memory size parameter.

Enable the Boost SQL Server priority flag.

This flag allows you to increase the priority of the MS SQL Server process over other processes.

It makes sense to enable the flag only if the 1C server is not installed on the computer with the DBMS server.

To set the flag you need to:

1. Launch Management Studio and connect to the desired server
2. Open server properties and select the Processors tab
3. Enable the “Boost SQL Server priority” flag and click OK.

Set the auto grow size of database files.

Autogrow allows you to specify the amount by which the size of the database file will be increased when it is full. If you set the auto-expansion size too small, then the file will expand too often, which will take time. It is recommended to set the value from 512 MB to 5 GB.

1. Launch Management Studio and connect to the desired server
2. Opposite each file in the Auto-increase column, put the required value

This setting will only apply to the selected database. If you want this setting to apply to all databases, you need to perform the same steps for the model service database. After this, all newly created databases will have the same settings as the model database.

Separate mdf data files and ldf log files onto different physical disks.

In this case, work with files can proceed not sequentially, but almost in parallel, which increases the speed of disk operations. SSD drives are best suited for these purposes.

To transfer files you need:

1. Launch Management Studio and connect to the desired server
2. Open the properties of the desired database and select the Files tab
3. Remember file names and locations
4. Detach the database by selecting Tasks – Detach through the context menu
5. Check the Delete connections checkbox and click OK
6. Open File Explorer and move the data file and log file to the desired media
7. In Management Studio, open the server context menu and select Attach database
8. Click the Add button and specify the mdf file from the new disk
9. In the lower database information window, in the line with the log file, you need to specify the new path to the transaction log file and click OK.

It is no secret that when considering the problems of configuring a SQL server related to increasing productivity, most IT specialists opt for increasing hardware. But is this always justified? Have all server configuration methods already been used? It is known that working with configuration parameters and changing their default values ​​can improve the performance and other characteristics of a given system. Among these SQL configuration options, there is one option that has many questions associated with it, this option is Max degree of parallelism (DOP) - so we’ll talk about it.

The Maximum Degree of Parallelism (DOP) option determines the number of threads onto which SQL Server can parallelize a query and indicates the number of server processors used. This parameter has a default value of 0 – the maximum degree of parallelism. For example, if you have 24 cores, then the value of ‘max degree of parallelism’ will be equal to 24 and the optimizer, if it deems it necessary, can use all processors to execute one instruction, that is, the request will be parallelized into 24 threads. This is good for most cases, but not for everyone. Also, it is not always good to use the default value of this parameter. Configuring this parameter may be necessary, for example, in the following situation: let's say we have an application into which all employees enter information about daily transactions, and, at a certain period of time, each of the users runs a query that builds a report on all the user's transactions for a certain period of time. Naturally, if the time period is long, this request will take a long time to complete and, with DOP installed by default, will occupy all available processors, which will naturally affect the work of other users. Therefore, by changing the DOP value, we can increase the SQL server response time for other users without changing the query itself.
MS recommends setting the value as follows:

Setting the parameter to TSQL entirely for the server:

EXEC sp_configure "max degree of parallelism", 4; reconfigure

You can also set this value for a specific TSQL query:

USE AdventureWorks2008R2 ; GO SELECT ProductID, OrderQty, SUM(LineTotal) AS TotalFROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty OPTION (MAXDOP 2); GO

In this example, the maxdop hint changes the default value of the max degree of parallelism parameter to 2. You can view the current setting like this:

EXEC sp_configure "Show Advanced",1; RECONFIGURE; EXEC sp_configure "max degree of parallelism"

Now let's see how this value affects the speed of query execution. In order for the test query written above to be executed for a longer time, we will add another select to it. The request will take the following form:

< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty

On my test machine, the 'max degree of parallelism' value is set to 0. MSSQL is running on a machine with a 4-core processor. I conducted a series of experiments with different MAXDOP values: equal to 1 – without query parallelization; equal to 2 - using only 2 cores; equal to 4 – using all and no hint to determine the option that uses the default sequel. In order to get execution statistics, you need to include the SET STATISTICS TIME ON option in the query, and also enable the display query plan button in Management studio. To average the results, I ran each query in a loop 3 times. The results can be seen below:

SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total FROM Sales.SalesOrderDetail dt, (SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty OPTION (MAXDOP 1); SQL Server Execution Times: CPU time = 45942 ms, elapsed time = 46118 ms. SQL Server Execution Times: CPU time = 45926 ms, elapsed time = 46006 ms. SQL Server Execution Times: CPU time = 45506 ms, elapsed time = 45653 ms.

The query plan shows that when the hint was installed (MAXDOP 1), the query was executed without parallelization. Average query execution time 45925.66 ms

SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total FROM Sales.SalesOrderDetail dt, (SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty OPTION (MAXDOP 2); SQL Server Execution Times: CPU time = 51684 ms, elapsed time = 28983 ms. SQL Server Execution Times: CPU time = 51060 ms, elapsed time = 26165 ms. SQL Server Execution Times: CPU time = 50903 ms, elapsed time = 26015 ms.

When installing the hint (MAXDOP 2), the request was executed in parallel on 2 cpu, this can be seen in the Number of Execution in the query execution plan. Average query execution time 27054.33 ms

SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total FROM Sales.SalesOrderDetail dt, (SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty OPTION (MAXDOP 4); SQL Server Execution Times: CPU time = 82275 ms, elapsed time = 23133 ms. SQL Server Execution Times: CPU time = 83788 ms, elapsed time = 23846 ms. SQL Server Execution Times: CPU time = 53571 ms, elapsed time = 27227 ms.

When installing the hint (MAXDOP 4), the request was executed in parallel on 4 cpu. Average query execution time 24735.33 ms

SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total FROM Sales.SalesOrderDetail dt, (SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty SQL Server Execution Times: CPU time = 85816 ms, elapsed time = 23190 ms. SQL Server Execution Times: CPU time = 85800 ms, elapsed time = 23307 ms. SQL Server Execution Times: CPU time = 58515 ms, elapsed time = 26575 ms.

the request was executed in parallel, also 4 cpu. Average query execution time 24357.33ms

links: http://support.microsoft.com/kb/2023536

In this short note, I would like to talk a little about the intricacies of parallelism settings in Microsoft SQL Server. Many of you have long been aware of the Max Degree od Parallelism option, which has been present in SQL Server for a very long time. By default, it is set to 0, which means that SQL Server itself will choose the optimal degree of parallelism, that is, the number of processors/threads used to execute one instruction. Now I won’t stop and discuss at what value it is better to set this option - this is a topic for a separate note. I'll just look at how the value of this option affects the execution of queries. For example, in the figure below, this option is set to 1, which means that parallel plans for all queries are disabled by default.

This option is also available for viewing using the following T-SQL command:

Indeed, any query plan will be sequential by default. For example:

However, the developer and any user still have the opportunity to influence this by using hints. To do this, you just need to specify the desired degree of parallelism, and the desired query plan is generated, for example:

And if we look at this query through the sys.dm_exec_query_profiles view, we will see that it is actually executed in 10 threads.

Thus, there remains a secret hole in the system that developers and users can use to “speed up” (here I put it in quotes on purpose, because a high degree of parallelism does not always lead to a decrease in query execution time) their queries by increasing the degree of parallelism . But, in this way, they can simply “kill” the server by running many uncontrolled parallel requests at the same time. What can we do about this? This is where Resource Governor comes to our aid, a very powerful and completely underrated system that allows you to very flexibly distribute resources between different user groups. Again, I will not dwell now on how it works and what capabilities it has. I will just go into detail about how its concurrency limit settings affect it. Let's first take a look at the default settings:

Again we see that by default the option is set to 0 and the decision about choosing the maximum degree is left to SQL Server. Now let's see what happens if I change this value to 5. Attention, under no circumstances make such settings on a real system, because I haven't even defined the classification function for Resource Governor and am changing the default group. But for testing and understanding how everything works specifically now in my example, this is enough. So I limit the maximum degree of parallelism for everyone to 5 threads. Let me remind you that the option Max Degree of Parallelism, which we looked at earlier is still set to the value 1. If we now look at the execution plan of our initial query, then by default it will be sequential, and with the maxdop 10 option it will be parallel. But, if we run a parallel plan, we will see something interesting.

Now our request is executed in only 5 threads, despite the fact that the option maxdop it has a value of 10. And, if you specify the maxdop 4 option for the request, it will be executed in 4 threads (the option in Resource Governor is set to 5). In this case the hint maxdop less than the Resource Governor setting, so no additional limitation is imposed. I will no longer give an example of this.

Thus, Resource Governor is a more powerful tool that actually limits the maximum degree of parallelism for queries, and this degree can be set differently for different user groups. In this case, the option Max Degree of Parallelism still continues to work and makes its contribution (or slightly confuses administrators, developers and users when it works in conjunction with Resource Governor). Further, the options for setting the values ​​of these 2 parameters are limited only by your imagination, but it is important to remember only two things: Max Degree of Parallelism and hint maxdop for a request, it affects which plan will be generated, the maximum number of threads that will be possible for this request, and the Resource Governor further limits the request from above during execution.

Max degree of parallelism (DOP) is an additional SQL Server configuration option that has been the subject of many questions and publications. In this blog post, the author hopes to provide some clarity on what this option does and how it should be used.
First, the author would like to clear up any doubt that the option listed sets how many processors SQL Server can use when serving multiple connections (or users) - it does not! If SQL Server has access to four idle processors, and it is configured to use all four processors, it will use all four processors, regardless of the maximum degree of parallelism.
So what does this option do? This option sets the maximum number of processors that SQL Server can use for a single query. If a query to SQL Server must return a large amount of data (many records), it sometimes makes sense to parallelize it, breaking it into several small queries, each of which will return its own subset of rows. Thus, SQL Server can use multiple processors, and therefore, on multiprocessor systems, a large number of records of an entire query can potentially be returned faster than on a single-processor system.
There are many criteria that must be considered before SQL Server invokes "Intra Query Parallelism" (splitting a query into multiple threads), and there is no point in detailing them here. You can find them in BOL by searching for "Degree of parallelism". It says that the decision to parallelize is based on the availability of memory to the processor and, especially, on the availability of the processors themselves.
So why should we consider using this option - because leaving it at its default value (SQL Server makes its own parallelization decisions) can sometimes have undesirable effects. These effects look something like this:

Paralleled queries run slower.

Query execution times can become non-deterministic, which can annoy users. Execution times may change because:

The query may sometimes parallelize and sometimes not.

A request can be blocked by a parallel request if the processors were previously overloaded with work.

Before we continue, the author would like to point out that there is no particular need to dive into the internal organization of parallelism. If you are interested in this, you can read the article "Parallel Query Processing" in Books on Line, which describes this information in more detail. The author believes that there are only two important things to know about the internal organization of concurrency:

Parallel queries may spawn more threads than specified in the "Max degree of parallelism" option. DOP 4 can spawn more than twelve threads, four for querying and additional threads used for sorts, streams, aggregates and assemblies, etc.

Paralleling requests can cause different SPIDs to wait with the wait type CXPACKET or 0X0200. This can be used to find those SPIDs that are in a waiting state during parallel operations and have a waittype in sysprocesses: CXPACKET. To make this task easier, the author suggests using the stored procedure available on his blog: track_waitstats.

And so “The query may be slower when parallelized” why?

If the system has very low throughput of disk subsystems, then when analyzing a request, its decomposition may take longer than without parallelism.

There may be data skew or blocking of data ranges for the processor caused by another process used in parallel and launched later, etc.

If there is no index on the predicate, resulting in a table scan. Parallel operation within a query can hide the fact that the query would have completed much faster with a sequential execution plan and the correct index.

From all this, it follows a recommendation to check the execution of the request without parallelism (DOP=1), this will help identify possible problems.
The effects of parallelism mentioned above should naturally lead you to believe that the internal mechanics of query parallelization are not suitable for use in OLTP applications. These are applications for which changing query execution times can be annoying to users and for which a server serving many concurrent users is unlikely to choose a parallel execution plan due to the inherent processor workload profile of these applications.
Therefore, if you are going to use parallelism, then most likely you will need it for data retrieval tasks (data warehouse), decision support or reporting systems, where there are not many queries, but they are quite heavy and are executed on a powerful server with a large amount of RAM. memory.
If you decide to use parallelism, what value should you set for DOP? A good practice for this mechanism is that if you have 8 processors then set DOP = 4 and this will most likely be the optimal setting. However, there is no guarantee that it will work this way. The only way to be sure is to test different values ​​for DOP. In addition to this, the author wanted to offer his empirical advice to never set this number to more than half the number of processors that are available. If the author had fewer than six processors, he would set DOP to 1, which simply disables parallelization. He might make an exception if he had a database that only supports a single user process (some data retrieval technologies or reporting tasks), in which case, as an exception, it would be possible to set DOP to 0 (the default value), which allows SQL Server itself decides whether to parallelize a query.
Before finishing the article, the author wanted to caution you that parallel index creation depends on the number you set for DOP. This means that you may want to change it while indexes are being created or recreated to improve the performance of this operation, and of course you can use the MAXDOP hint in the query, which allows you to override the value set in the configuration and can be used during off-peak hours .
Finally, your query may slow down when parallelized due to errors, so make sure your server has the latest service pack installed.

The "max degree of parallelism" parameter specifies the maximum number of threads that SQL Server can parallelize a query onto. By default, this parameter is zero, which means the server's number of processors is used. For example, if you have 24 cores, the actual value of "max degree of parallelism" will be 24 and the optimizer, if it deems it necessary, can parallelize the query into 24 threads. In general this is good, but not always. Also, it is not always good to use the default value of this parameter.

Now let's look at why this is not good. I will give one example from my practice. There is a query that, in theory, should use a certain index, and at first this happens. A query is launched that searches the index and returns the necessary data. Everything is fine. Then, as the database grows, more and more records are added to the table, and at a certain point the optimizer realizes that it is possible to execute the query faster: “Why should I perform an index search if I have 24 cores? This means I can scan the clustered index in 24 threads and get the data I need faster!” For this particular query this is good - it runs faster. But it’s bad for everyone else, because... they are forced to wait for processor resources to be allocated to them. And in systems with a large number of simultaneously executing queries, such parallelization is more likely to be bad than good. And instead of improving productivity, it only gets worse. Until recently, I solved this problem by setting the MAXDOP hint in storages I didn’t like. But now I found a specific Microsoft recommendation and applied it on my servers. Recommendations for choosing the optimal value for "max degree of parallelism" are here:Recommendations and Guidelines for "max degree of parallelism" configuration option . I quote this recommendation:

For SQL Server 2008 R2, SQL Server 2008 and SQL Server 2005 servers, use the following guideline: a. For servers that have eight or less processors, use the following configuration where N equals the number of processors: max degree of parallelism = 0 to N. b. For servers that use more than eight processors, use the following configuration: max degree of parallelism = 8. c. For servers that have NUMA configured, max degree of parallelism should not exceed the number of CPUs that are assigned to each NUMA node with the max value capped to 8. This will increase the likelihood of all parallel threads of a query to be located within a NUMA Node and avoid costly remote node data look ups. d. For servers that have hyper-threading enabled, the max degree of parallelism value should not exceed the number of physical processors.

It follows that for systems with more than 8 processors, it is recommended to set “max degree of parallelism” = 8. This is followed by another explanation, which says that 8 is a general recommendation. And in systems where the number of simultaneously executing requests is small, it makes sense to set a larger value, and in systems with a large number of competing requests, it makes sense to set a smaller value. And when choosing a specific parameter, you need to look at its impact on the system and test it on specific requests.