"Go" SQL Server degree of parallelism problem

Consult Microsoft SQL Server Engineer, collect the query plan of the first query statement on two machines, save the result to Microsoft Engineer, after analysis, the query plan of the same SQL statement on both machines is the same, but the degree of parallelism of the two machines is different, the SQL Server has the default number of cores. The degree of parallelism is how much, the judgment may be due to different degrees of parallelism caused.

HP parallelism defaults to 4*6=24

Dell parallelism defaults to 4*4=16

Force the parallelism of the two machine SQL Server to be manually set to 4 and test the results as follows:

At the end of the first query, add the parameter option (MAXDOP 4), which is then performed on both HP and Dell, Time is 2s, and the parameter value is changed to 1, which is time consuming 1s.

At the end of the second query, add the parameter option (MAXDOP 4), which is then performed on both HP and Dell, Time is 2s, and the parameter value is changed to 1, which is time consuming 1s.

As you can see, the problem is really caused by the degree of parallelism.

The higher the degree of parallelism, the faster the SQL query is not necessarily, the slower it may be, but it is not the less nuclear the better.

When the query is executed, SQL Server automatically parses the SQL statements, makes a query plan, allocates tasks for each CPU, and the CPU is too high, the longer it takes to allocate tasks, but the execution time of the query itself does not grow. After reaching 4CPU, the higher the CPU frequency, the faster the SQL query.

In general, the total number of cores/2 or 4 can be used when customizing the number of cores used by SQL Server.

Microsoft, based on practical experience, has the highest efficiency of 4 cores for SQL Server. For SQL Server, the CPU is transparent and does not care which core is on which CPU.

The number of CPUs supported by SQL Server is affected by the SQL Server version and the operating system version.

SQL Server SP4 + windows2003 Sp2,sql Server supports a maximum of 4 cores.

The degree of parallelism has an effect on SQL server2000,2005,2008, which in fact supports only 4 cores.

There are two ways to change the degree of parallelism in SQL Server, as follows:

1. The following statement changes are applicable at the server level

Close the statistics at the end of the SQL statement:

Select GETDATE ()
Set STATISTICS profile off
SET STATISTICS IO off
Set STATISTICS time off

So you can get the disk IO overhead and time information of the SQL statement, so that we may analyze other SQL statements and find the database bottleneck later.

The default value in the Processor tab of the server properties of the

SQL is best not to change, and if the change is likely to cause instability in the database, it is necessary to change the server from the new start. Changing the settings is best done with SQL statements and does not require a server restart. ----Microsoft Tab function read: 1) Processor control: The maximum number of worker threads that can be selected using the CPU2): 255 (on Microsoft's official website can be seen, 8 CPUs can be set to 480, but not recommended)
3) Promote SQL Server priority on Windows: This key can be set to be set on a dedicated database server, but only for dedicated SQL Server servers. ---Microsoft is also recognized.