Performance optimization Case study--powering a mobile OA system

System conditions

Hardware configuration

　　

　　

Software situation

　　

Database status

　　

System situation can be seen, this is a smaller OA system data size 70G, hardware configuration more common 2 16CPU, 48G memory, the database is 2008R2 version.

Database metrics

Let's take a look at the database performance-related situation: Data is from 9:30 A.M. to 8 o'clock

Number of requests per second:

　　

Number of user connections

　　

Number of slow statements

　　

System wait Condition

　　

Wait time

　　

　　

CPU, memory, disk indicators all normal, there are many indicators, here does not map the www.qwangxiao.com.

Actually see here, most crossing can come to the conclusion, the hardware index is normal, the blockage is so serious, the system is slow mainly because of blocking! And the statement has been running for a long time because it has been blocked for a long time!

　　My guess.

Ok no problem is this positioning, also we see a lot of blocking types are lck_m_is, lck_m_s, Lck_m_u With this positioning, I can guess that the system must have an UPDATE statement is not optimized or too frequent (OA such a system is generally not particularly frequent, so it must not be optimized), and the design of the core query statements are often blocked (if not the core function is slow, users will not shout like this!) ), and 80% of the possible core queries are not optimized!

Problem diagnosis

With my guesses, let's take a look at some of the core statements:

　　

Many statements are similar, see such a simple statement (all basic query several fields a where condition), I know the problem must be very simple!

So simple statement design how long does it run out?

　　

Many people think that there must be a missing index, so there is no index on the critical WHERE condition!!!!!

Look at the structure:

　　

　　This table is a table with 2.8 million data, not a missing index as we imagined, but the condition on the where field is a clustered index!! (In fact, if the condition is simply missing index, how can the technician find out?) ）

The whole system other problems, that is to say, the system has been optimized, the program design is also very good, there is no such a very complex SQL, are split into a step by step very simple SQL, that is to say that the technical level of the technician is still very good!

So the question is, what's the problem?

The possible scenarios are:

1. My simple statement is not missing the index, and alone in the database run quickly quickly (this is certain)

2. I am blocked in the system so serious, is there any place I did not find? How can such a statement be blocked so hard?

3. Is there something wrong with my server?

　　

　　In a book of Creative stickiness, which is related to the "Commander's intention", there is a metaphor that is very close to this, and if other distractions are excluded, it's just a matter of how slow it is to see such a simple statement. So we are intent clear, eliminate interference, we will soon think of "implicit conversion" causes the index can not be used, but it is because of some of the above problems interference, we may be directed to, is not a server problem, is not blocking the situation we have the analysis clear? Not too many ways, the database itself is such a complex thing, the combination of various factors is the most test of the wisdom of practitioners.

To get back to the point, "implicit conversion" is really a very difficult thing for a person who writes a program, because I write the statement quickly, to the database run slowly, which I do not know.

If you do not know what an implicit conversion is, see: Some details of implicit conversions in SQL Server

But we have a very clear analysis of "implicit conversions" through tools.

　　

　　

In the previous table definition, we can see that the field type of the table is varchar, and the parameter passed in is nvarchar (learned from implicit conversion hints)

　　

Support for a simple problem to get the location, it is also very easy to solve, here are a few implicit conversion of the common solution:

1. The Program definition field type does not match the table definition (priority is higher than the table definition type), directly modifies the parameter setting type

2. The program does not define a type such as a Java program definition string, and the driver is automatically translated into nvarchar, which can generally be added in the program casting such as "where A = @a" is rewritten as "where A = Cast" (@a as varchar (custom length)) "

3. If the program is difficult to modify, or third-party development, you can directly modify the table field type

Performance comparison

Significantly improved performance over a 1-day simple optimizer program

Before optimization

　　

After optimization

　　

Before optimization

　　

After optimization

　　

Performance optimization Case study--powering a mobile OA system