30 minutes with your proficiency in the performance optimization of the Thing (case description)

Preface

Performance optimization is a necessary skill for database ops and middle and senior software developers, and many times the difference between an old driver and a new driver is what is being written to optimize.

Bloggers have approached thousands of customer systems, all of which have a variety of performance issues. So how do you get a thorough understanding of our database performance issues? Let's use a case today to illustrate the performance optimization thing.

PS: A lot of technical staff to optimize a set of their own understanding, before reading this article please put down your own understanding.

is so-called: follow Bo Master not lost, Bo master take you on high speed!

Click to open the case follow the Bo master's ideas to see How to optimize these things: this case demo

　　

Understanding the system environment

Optimization first know what kind of hardware/software environment The database runs in? What is the configuration? Memory, CPU can these be fully applied? How large is the database mass?

First, let's look at the configuration of the system:

Software level, we want to know our operating system version, SQL Server version, and the corresponding version of the hardware limitations (such as 32-bit system does not open awe can not use more than 4G of memory, Server 2008 Standard version of the largest support 32G memory, etc.)

In this example, we can see that the system environment is not an exception, the SQL Server patch is not up-to-date, CPU resources are not sufficient, the CPU may become the bottleneck of the system.

performance at the global level

The global level of the problem mainly refers to the comprehensive server of various indicators positioning system bottlenecks or problems, the most taboo in performance optimization is to see an indicator immediately , against an indicator is blind, it is possible to deviate from the root cause of the problem itself, It is also possible that optimization simply does not solve the underlying problem but the appearance is mitigated.

　 Performance Counters

CPU: CPU usage reaches 100% in a large amount of time, indicating that the CPU has become a bottleneck.

　　

Memory: Memory counter life cycle at 11 o'clock has dropped to 0, the lazy writer is also high, indicating that memory pressure, and more serious.

　　

Disk: The average queue for the disk is high (the general system best case queue should be less than 2) and the read queue and write queue are high. Due to the memory pressure, it is now impossible to determine whether the disk pressure is due to insufficient memory or the disk speed is not sufficient.

　　

Other counters:

You can see a large number of full table sweep faces in the system, which indicates that many queries do not have an index applied.

　　

The system has a large number of lock waits around 11 points and 11 points around 24, and the wait time is long (more than 150s)

Through many kinds of counter can see the system problem synthetically. There's no detail here.

　　

　 system Wait

Wait is another can look at the global level of the system's indicators, the system is running the card slow problem is caused by the wait, and then the type of wait can be very intuitive to reflect the problem of the system.

Several main waits:

Asnc_network_io: The general reaction is that some queries may return a large amount of data, please Gajah Mada whether the specific wait statement needs to return so much data.

WAITFOR: May be configured with a CDC publish subscription or a program that uses a statement WAITFOR delay

Cxpacket:cpu the dispatch wait.

Lck_m_u: statement blocking between update statements.

Writelog: Note that there are cyclic inserts and new operations in the program to write the log frequently, the disk speed does not meet the write frequency caused.

　　

　 Comprehensive Analysis

Comprehensive system wait and performance counters, we can basically determine that the system has the following problems:

The CPU, memory and disk of the system have great pressure, especially the CPU load is close to 100%, there are a large number of table sweep faces in the system may be missing than multi-index. Some statements in the system may return a large amount of unnecessary data, the system lock is serious, the wait time is long, and the statement execution time must be very long.

　　Statement execution of the overall situation: due to the above problems, then there must be a large number of long-time statements in the system!

　　

solve the problem

The definition of the problem is a very important step, from the overall analysis of a number of indicators, so that all problems can not be found. After locating the problem, let's take a look at the basic steps to solve these problems.

This case is a situation of their own simulation, so although the appearance of the resource pressure is very large, but the actual operation of the statement is not many, the scene is limited, but in the production system if there is such a representation, then your system performance problem is very serious need a detailed optimization.

Then the following also describes how the production system encountered such a problem should be how to optimize, there are necessary steps.

Step one is to optimize the database for the system problem and improve the overall efficiency .

Many people optimization may go straight to the statement that the statement can solve the performance of all problems, in fact, such a view is not comprehensive, system configuration, database configuration, index planning, etc. are the necessary steps to solve the performance.

For example, the statements in the system are the best, the database is running slowly, it may be because of your checkdb configuration problem, or because you automatically shrink without shutting down the performance problems caused.

Optimize operating system Configuration

Configure the server for configuration checks to see if there are configuration items that are configured unreasonably or that can be optimized, such as whether virtual memory is configured or not? Is the server level limiting the use of resources? is the server running in high-performance mode?

Optimize configuration at the database level

The reasonable configuration of the database parameters makes the hardware fully exert the hardware function, optimize the unreasonable configuration, and reduce the possibility of impact on the database. For example: Maximum degree of parallelism? Maximum memory?

　　

Whether a large number of missing indexes

A large number of index misses will inevitably lead to poor statement performance, and consume a large amount of system resources, it is likely to cause the above server high-pressure representation

　　

Delete useless indexes

Deletes a useless index in the database. The time that the update operation was raised.

Remove Duplicate Index

Deletes a duplicate index in the database. The time that the update operation was raised.

　　

Index the key statements

This paper analyzes the statement that consumes large sentences or executes many times in the system, evaluates the performance problem of the statement, and establishes the appropriate index, reduces the resource consumption of the statement, and the efficiency of the statement running.

Resolve blocking

Resolve the blocking between the statements, which need to analyze the blocking chain of the statement, exactly what kind of action is blocked, why is blocking?

A lot of beginners often ask questions: Why do I sometimes check out very slowly sometimes? A: In most cases, your statement is blocked.

　　

Optimize tempdb

Reduces the pressure caused by tempdb resource contention for tempdb tuning. In this case, it is possible to see contention waiting for tempdb, so optimizations for tempdb are also necessary.

Optimizing Log Fragmentation

The log fragmentation problem is optimized for log increase.

Clear Index Fragmentation

Check the system for index maintenance and fragment cleanup for tables with too large fragments. Mainly reflected in the system has an aging index, index aging results in the performance of the index is not high or invalid.

One-stage expected effect

One-stage optimization is the overall performance improvement, performance improvement will be obvious, for different systems are generally 2-3 times higher.

Step two dealing with hot issues

The main problem in dealing with hot spots is to tune the key statements after the basic optimization of phase one, which may include creating indexes, modifying the wording, querying hints, Planning wizards, and so on.

In statement tuning, please focus on: whether there are missing indexes, whether there is implicit conversion, the execution time of the statement, CPU, logical Read and write, the amount of physical read and write, occupy tempdb space and other information.

Example: Such a statement after the first phase of optimization is not much improved, and the resource consumption is still very large, then we can be specific to this statement for a detailed two-phase optimization.

　
　

Simple optimization.

　　　

Just a simple change of the wording of the sentence time has 7 seconds into 1 seconds, memory consumption from 300+MB to 1MB

two stage expected effect

Phase Two optimization is a detailed optimization step, to the more specific statement, the specific situation. The optimization of this stage can make most of the statements in the system from the writing, configuration, running indicators to optimize the value.

step three for business

This step needs to be matched with the developer, what is the function still slow? What statements are executed? Is the function of the leader? Or is it generally slow to function? If the big leader uses the function, then maybe you need to spend more time. This part does not unfold here to say.

three-stage expected effect

The third stage belongs to the most meticulous stage, which can combine the problem of the real point-to-point elimination system in the business.

Guide Map

For performance optimization A few diagrams hope to help the database practitioners to comb the idea of optimization (personal ideas for reference only, imperfect places also forgive me)

　　

Cpu:

　　

Memory:

Disk:

　　

Wait:

　　

Summary

In performance optimization is the most taboo is to see an indicator immediately on the start, the judgment against an indicator is blind, it is possible to deviate from the root cause of the problem itself, may also make optimization simply can not solve the fundamental problem but the appearance has been relieved.

This article is just an example of the basic idea of optimization, hoping to help more database practitioners to understand performance optimization.

This article only describes the idea, the specific parts of the solution please see my series of articles: SQL Server Comprehensive optimization-------Expert for SQL Server Diagnostic series

Performance tuning is a continuous work, not once solved the problem can be peace of mind, regular inspection is also a database practitioners one of the necessary work, to early detection early resolution.

Patrol series articles see: Easy to master the way of database management--operation and maintenance patrol series

30 minutes with your proficiency in the performance optimization of the Thing (case description)