17th-Configuring SQL Server (1)-Configuring more processors for SQL Server

Original: 17th--Configure SQL Server (1)--Configure more processors for SQL Server

Objective:

SQL Server provides a system stored procedure, sp_configure, that can help you manage the configuration at the instance level. Microsoft recommends using the default configuration, but depending on the server, the different load systems, and your usage, changing the configuration may benefit your performance. In 32-bit and 64-bit systems, sp_configure will have some differences.

We often see servers that are located on SQL Server that contain services or features such as IIS, file servers, or domain controllers. These can affect your performance and even hinder normal operation.

This series of articles will contain:

1. Configure more processors for SQL Server.

Memory configurations in 2, 32-bit, and 64-bit systems.

3. Configure "Optimize for immediate load".

4. Optimize the configuration of the SQL Server instance.

Configure SQL Server to use more processors:

Today's system, the database has become more and more large. In order to get information from a database more quickly, it is not enough to manage your database, and more CPU is needed to handle it.

No matter how efficiently you maintain indexing and statistics, it's hard to get enough response time from a SQL Server that uses inefficient CPUs. How to choose the right CPU for the database operation is not within the scope of this series, but we will show you how to make your CPU work more powerful and efficient.

Have you ever wondered how many CPUs SQL Server will use when running a query? Users often want to speed up the operation of SQL Server by buying more and faster CPUs, but it's more important to focus on how many SQL Server needs to use at runtime?

Start work:

Before you start digging in, you need to know how many CPUs you have on your server. You can use the dmv,sys.dm_os_sys_info of a SQL Server to find this part of the information. This DMV will try to return information about the computer and about the resource consumption, among other things:

SELECT  Cpu_count as ' cores ',--the total number of logical CPUs        hyperthread_ratio--the logical core of a physical CPU to the physical core than the from    Sys.dm_os_sys_info

Steps:

1. To set the number of CPUs to use when running queries at the instance level, execute the following statement:

--0 is the default value sp_configure ' max degree of parallelism ', 0 RECONFIGURE with OVERRIDE GO

2, from the statement level to set the value of the degree of parallelism, you can use hint to achieve, the following add setstatistics time to look at the differences in the degree of parallelism:

Set STATISTICS time on SELECT  *from    sales.salesorderdetailoption  (MAXDOP 1) Set STATISTICS time OFF goset STA Tistics time on SELECT  *from    sales.salesorderdetailoption  (MAXDOP 0) SET STATISTICS time Offgo

Here are the following:

Analysis:

SQL Server has a very good algorithm system to decide whether to run queries in parallel. The decision to rewrite SQL Server requires experience and expertise. As for how many CPUs to use, this more deterministic method is--experiment.

In step 1, using the sp_configure stored procedure to set the maximum degree of parallelism to 0 is also the default value, which represents whether SQL Server generates a parallel execution plan, and if so, how many CPUs can be used. If you set to 4,sqlserver will use 4 cores to process the query, and if set to 1, no degree of parallelism will occur.

In step 2, you use option to set up a parallel query on a specific query. Here are two SELECT statements executed simultaneously. The first query uses MAXDOP = 1, which means that queries are not executed with parallelism, whereas the second query uses MAXDOP = 0, meaning that SQL Server itself decides whether to run with parallelism.

After you add set STATISTICS time, you can see how many CPU times each query consumes in total.

Expand your knowledge:

Changing the default maximum degree of parallelism in a production environment can be very risky. So keep it as it is, and if you want to change it, you need to discuss it with your boss or colleague. Experience has shown that SQL Server does not always use all CPUs for a single query. In addition, it is not recommended to adjust this setting in an OLTP system, but in an OLAP system this can be considered.

Also, if you have 16 cores and set Maxdegree of parallelism to 8, it does not assume that only 8 cores will be used on SQL Server, and that only a single query will not use more than 8 cores, even if it is running in parallel. However, SQL Server will still use all available cores.

17th-Configuring SQL Server (1)-Configuring more processors for SQL Server