Expert Diagnostic Optimization Series------------------is your CPU high?

Many users are now plagued by slow database problems and are struggling to pay for a professional DBA that is too expensive. Software maintenance personnel on the database is not so deep understanding, so that the problem can not be resolved, or can only be temporarily resolved can not be cured. Developers to solve data problems are basically searching Baidu various methods to try again, may miss the best time to diagnose problems and may try a bunch of methods at last helpless give up.

How to let the trivial of the program maintenance personnel, the fastest way to solve the problem of the database? How to minimize the pain of our programmers ... Drink tea every day to see the news safe through the day? This series of important through the expert for SQL Server tools to explain the various problems encountered in the database and the root causes of such problems, so that the location of the problem more accurate, the problem-solving ideas clearer!!

The performance of the database is good or bad, for the end-user performance is the click of the operation can respond quickly, then the response to the database is the statement execution time is short enough!

The performance of the database using operation and maintenance personnel, simple may be seen as CPU, memory, disk three major indicators are normal, then today we start from the CPU, see what the CPU can see what problems!

No more nonsense, just open the whole---------------------------------------------------------------------------------------------------.

The main use of performance counters (do not know what is the performance counter, please self-Baidu)

Use two of them ~

1. %process time full instance (primarily used to view the current server's CPU condition)
2. %process time sqlservr (primarily used to view the CPU usage of the database)

Exclude other apps that affect the CPU　　　　　　　　　　　　　　　　　　　　　　　

　　　　

　　　　

　　

　　combining the two counters at the same point in time can diagnose whether the CPU is consumed by other applications of the server, about 17:10 "%process time full instance (red line)" Suddenly raised, and the SQL Service (Green Line) did not significantly increase, which also shows that the CPU pressure in this period is not a database caused!

　　This red line obviously rises when I do a file compression on the server where the database is located! Similar file compression This operation will use a lot of CPU, the database performance Impact!

　CPU Problem Analysis

CPU high or up to 100% must be a big pressure on your business? Does the CPU not meet your needs? Before jumping to conclusions, please analyze carefully, a hasty verdict may change, boss a comfort "the world is so big you should go out!" ”

Below we use a few typical scenarios, analyze the problem, and give the best practices ~

　　　High CPU duration during peak hours

　　　　

The graph is the CPU of the server for a few days

A lot of people see this picture, don't they see their servers? Is there a kind of intimacy--below we analyze the possible problems of this appearance!

First make clear that a point 90% of the problem may be concentrated in 10% of the scenario, this CPU continues to be very high in the case of the following two points:

1. Is your database parallelism adjusted?
2. Is your database missing indexes, causing frequent queries to consume very high CPU resources?

What is the maximum degree of parallelism? It is simple to understand how many CPUs can be used to execute a single statement. It seems that the more you use the better, the more words you use, the faster you'll be! The answer is the uppercase "NO", the use of excessive CPU will cause the thread to work together for a long time, directly resulting in the statement is very slow, and consumes a lot of CPU time, resulting in high CPU usage, and thus become a bottleneck!

Look at a data statement duration is the execution time, but look at the CPU time, this is not set the degree of parallelism, a parallel plan will produce a lot of CPU consumption, but also make the statement execution slower!

　　

Is it better to use as little as possible? Anything is not absolute, depending on the circumstances, if the system has a large amount of data on the operational requirements, parallel use of multiple CPUs will be a great boost.

　　　　General recommendation system if more than 32 CPUs are set to 8 or 4, if the system is particularly short and frequent statements are recommended to set to 1 (Cancel statement parallelism, be careful to really match your scene is good)

　　　　The threshold for parallel overhead, which mainly controls when the SQL Optimizer chooses a parallel plan, suggests default values, and the smaller the optimizer, the easier it is to choose a parallel plan.

　　　　The setting for the degree of parallelism is set at the instance level (2016 can be set for a separate database)

How to set the degree of parallelism and threshold, see: The system default degree of parallelism is 0, the threshold is 5 default

　　　　

　　

The degree of parallelism can be adjusted to who knows Ah, the following we talk about the system is very persistent problem-the statement caused high CPU

Statement causes high CPU is also one of the most common problems, then how to tune the statement to reduce CPU consumption? Here are just a few simple instructions, specific statement tuning, parametric reduction statement compilation, see the following series of articles.

There are many ways to tune statements, and here are some of the most common CPU-related:

1. Adding an index reduces the cost of the statement, and the CPU consumed by executing the required resources consumes less naturally.
2. Reduce the complexity of the statement, allowing SQL Server to perform efficiently (and also reduce resource consumption).
3. Whether the parse statement can take a serial plan.
4. The front-end program minimizes the compilation consumption of the statement by Parameterization.

CPU regularity fluctuation

Get the CPU monitoring data do not blindly jump to conclusions, data is often the most reflective problem, to provide you with ideas!

　　　　

If you are a system maintainer and see a CPU data indicator like this, if you don't have some ideas, please familiarize yourself with your beloved system.

This picture clearly reflects the system every half an hour of CPU increase, then do not busy to find the corresponding time point of the statement, we have to think about it at least, what operation in the system for half an hour to execute always? SQL JOB? Planning tasks? Front desk timing processing? Wait, wait.

Is there any abnormality in the timing of this rule? Have you changed anything recently? Is the result of execution the same as you think?

Perhaps the problem is so clearly positioned ...

The CPU suddenly soared high

The 9-point CPU in the figure soared from an average of 20 to 100%

The sudden high CPU may be accidental, perhaps you can think of no relationship, but when you judge the accident before, you have done the following analysis:

1. Has the system log been analyzed and is there an exception to the high CPU point in time?
2. Do you want to check for any special applications on the server?
3. Did you check the status of the database?
4. Have you asked the relevant business people?
5. Are you ready to start monitoring immediately for the next emergency?

Without your judgment, there is no basis ... Also missed a discovery problem, the opportunity to learn knowledge!

The most likely reason for excluding the above exception is that in the database, there is one or more statements running abnormally at that moment, or very not optimized. If this is true because of a statement problem and only occurs once, then this may not be a problem, we try to find the current statement and look at the problem. The statements that were found at that time can be viewed through the system view Sys.dm_exec_query_stats to see CPU consumption and uptime, or by their own monitoring tools.

Find the corresponding point in time to see exactly what statement is running ~

　　　　

　　　　

What is the reason for this statement to be analyzed?

The CPU is really high!

After various analysis optimization, if the CPU pressure is obvious, the hardware can not support the business, then we have to choose a more tall way, such as modify the design of vertical/horizontal split, add hardware, read and write separate sharing pressure, build cluster load balancing and so on means ...

-----------------------------------------------------------------------------------------------------

Summary: For the solution of CPU pressure, most of the users can be solved by adjusting the degree of parallelism and the optimization of the system statement.

In addition, the monitoring and analysis of the system plays an important role in diagnosing problems and solving problems.

It is important to have a careful analysis before jumping to conclusions, and a decision taken for granted can have a serious impact.

Does your system really need to add hardware, or a big plan?

　　　　

-----------------------give some CPU-related article Connections-----------------------------------------------------

SQL Server in Birch to troubleshoot high CPU usage

Gaodashia in- depth analysis of SQL Server parallel execution principles and practices (bottom)

Careyson's talk about the execution plan cache in SQL Server (top)common monitoring SQL Server performance counters

----------------------------------------------------------------------------------------------------

Note: This article is original, welcome reprint, please in the article page obvious location give this article link!
If you think this article is not bad please click on the lower right corner of the recommendation , thank you very much!

Quote Gaodashia in a sentence: "Deny SQL Server back pot, start with me!"

Expert Diagnostic Optimization Series------------------is your CPU high?