Shulou(Shulou.com)05/31 Report--

How to optimize the performance of the database, in view of this problem, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Preface

One of Microsoft's engineers once had a very vivid metaphor for performance tuning: peeling onions. I also very much agree, let's take one layer at a time and unveil its mystery outside.

Six major factors

The following is the most commonly used diagram that we use to analyze database performance problems for our customers.

After looking at this diagram, do you have a basic concept of performance tuning? Generally speaking, we will analyze it in the following order:

Hardware capability

System scale

Internal factors of database

Software environment

The order of these four can be adjusted or exchanged, but the performance optimization of the system must be based on the overall situation. Don't go too deep into the optimization of a SQL statement, because you may spend a lot of time optimizing a SQL from 20s to 1s, but the slowness of the whole system still exists.

Actual combat case

Cut the crap, open the whole, and go straight to the practical information.

Time: sometime in January 2018

Event: a large area of slow card suddenly appeared in a hospital customer at 4: 00 p. M. There was a serious problem with the whole system, the telephone of the information center exploded and the hospital engineers were at a loss.

Fortunately, we installed a 'camera' to the database, so let's take a look at what was sent from the surveillance video. And then solve it.

Hardware capability

CPU

During the period when the problem occurred, the CPU utilization rate was less than 20%, which is normal.

Memory

As shown in the image below, memory usage is normal.

Page life cycle

Available memory

IO

The average value of the IO queue is very low, and there is an instantaneous high around 15.48. Please note whether there are any bulk writes during this period.

Generally speaking, the hardware resources are adequate.

System scale

When the problem occurs, the number of batch requests per second is not an upward trend, but a decline. This is because the congestion and waiting of the system affects the throughput of the system.

Internal factors of database

wait for

Slow sentence

From the trend charts of conversations and slow sentences, we can see that the time when the problem occurred coincides with the customer description, and we can conclude that our own accident is indeed slow in the database.

What causes slowness?

If you check the statements running over a period of time, you can find that around 15.58pm, more and more CMEMTHREAD waits begin to appear in the database.

Until 16.08 minutes on page 1900, there were 100 concurrency and CMEMTHREAD waiting.

What is CMEMTHREAD waiting

Microsoft's official description: occurs when a task is waiting for a thread-safe memory object. When multiple tasks try to allocate memory from the same memory object, resulting in contention, the wait time may increase.

The description is so obscure that I still have no idea what the waiting type is and how to deal with it.

In fact, according to the official description, it is a problem of memory contention, but in fact, the crux of the problem lies in the contention of multiple tasks, which is actually a problem of concurrent execution.

Scene

Occurs when the ad hoc execution plan ad hoc plans is inserted into the plan cache when the database is compiled or recompiled

Under NUMA architecture, memory objects are partitioned according to nodes.

There are three types of memory objects (Global,Per Numa Node,Per CPU). SQL Server will allow memory objects to be segmented so that only threads on the same node or CPU have the same underlying CMemObj, thereby reducing thread interaction from other nodes or cpu, thereby improving performance and scalability. Reduce concurrent contention for memory.

SELECT type, pages_in_bytes, CASE WHEN (0x20 = creation_options & 0x20) THEN 'Global PMO. Cannot be partitioned by CPU/NUMA Node. TF 8048 not applicable.' WHEN (0x40 = creation_options & 0x40) THEN 'Partitioned by CPU.TF 8048 not applicable.' WHEN (0x80 = creation_options & 0x80) THEN 'Partitioned by Node. Use TF 8048 to further partition by CPU' ELSE' UNKNOWN' END from sys.dm_os_memory_objects order by pages_in_bytes desc

If you find that Partitioned by Node's memory overhead is at the top of the list, you can use TRACE FLAG 8048 to reduce CMEMTHREAD wait.

As you can see from the figure, the customer's Partitioned by Node is relatively low, ranking 14th.

3. Patch

This kind of scene is the most common. If a large number of CMEMTHREAD waits are found in the system, priority should be given to whether the database has been patched.

2008 r2: FIX: SQL Server 2008 R2 performs poorly when most threads wait for the CMEMTHREAD wait type if the threads use table variables or temp tables to load or update rowsets

2012, 2014 when you execute many special queries in SQL Server 2012 or SQL Server 2014 CMEMTHREAD wait.

Software and hardware environment

The current version of the database is 11.0.5556.0 and the patch mentioned earlier is 11.0.5623.0 after installation.

Code design

What statement creates the wait.

It is similar to the following statement, when the concurrency exceeds 100.

SELECT

* INTO # Tmp from TB where 1 / 2

The features are as follows:

1. Parallelism does not occur when the simple statement overhead is less than 5.

two。 All take the form of select into # temptable.

As analyzed above, CMEMTHREAD waiting is a concurrency issue, not a memory issue. When other schemes don't work, we can reduce CMEMTHREAD waiting by adjusting the way such statements are written.

Business model and architecture

At present, the system is running on a single machine, which is actually very rare. There is a small mix of OLAP and OLTP services. In the future, we will plan a solution for read-write separation or load balancing for our customers.

Solution

Install patches for * *

At a minimum, you need to install the previously issued FIX that solves the waiting problem. It is recommended to install the 2012 SP4 patch that has been installed so far.

Modify parameters

Optimize for ad hoc workloads changed from 0 to 1. Reduce the memory consumed by ad hoc queries for scenarios where an ad hoc execution plan ad hoc plans is inserted into the plan cache.

Increase the number of TEMPDB data files

Select * into # temptable creates a lot of latch contention to prevent a large number of pagelatch latch contention after CMEMTHREAD waits for elimination. I have experienced a lot of cases, after solving a congestion in the front, there is a new wait behind, resulting in worse performance. Keep in mind that optimization is a long-term, gradual process.

Migrate the location of the TEMPDB data file

At present, some tempdb files are placed in S and generally on D disk. It is recommended to migrate to S disk (storage above) to increase the response speed of tempdb. If possible, it would be a good choice to use SSD to improve the performance of tempdb.

Optimize the code of the program

Modifying the code is usually placed on the * side, because there are more situations involved. In 80% of the cases, the previous means can solve the problem. For the remaining 20%, we need to check the logic in the program to see what kind of business these statements are generated. What conditions will trigger this kind of business? Stored procedures are used for similar statements below, or parameterized, to reduce the number of compilations and recompilations. In addition, such statements will create temporary tables concurrently, which may speed up the execution of such statements and reduce the number of concurrency of such statements at the same time by adjusting the settings of tempdb.

Optimization effect

After the previous several optimization methods, starting from the next day, there was no waiting for CMEMTHREAD.

wait for

Slow sentence

This is the answer to the question on how to tune the performance of the database. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.