Database performance optimization-database waiting queue (on)

 

Introduction to database waiting queue (on)

This articleArticleThe purpose is to help DBAs, developers, and other database users explain what is a database waiting queue.

Performance OptimizationWith the launch of SQL Server 2005, it contains a lot of dynamic management views (DMV) and functions, we need to use these dynamic management objects to help us diagnose and eliminate performance.

For performance, the most perceptible phenomenon is that the SQL server's response slows down and the server's resources are not normally consumed. In the face of these problems, it is our first step to analyze the waiting queue. Only by knowing why the database is slow and why too many resources are consumed can we take the right action.

 

The following describes how to analyze the waiting queue in An OLTP application. Of course, on this basis, we will further study and study in depth.

Before that, let's take a look at the performance-related content, so that we can easily expand the subsequent content. First, let's talk about the overall response time.

Overall response time

As you know, in SQL Server 2000 and earlier versions, we must use a lot of similar methods to set many different counters and tools to capture and analyze the causes of performance bottlenecks. Tools we often use include profiler traces, performance counters, network sniffer, and some third-party diagnostic tools. It is undeniable that using these tools can help us find the crux of the problem, but it takes a lot of time and effort.

We often ask the following question: what is the database engine waiting ?, In fact, when we use tools to analyze performance problems, we are trying to find these answers. In addition, after the problem is solved, the end user is most concerned about the total response time, or the response time of the sent query.

The overall response time is the time of the process after a request is sent until the response is received. For exampleProgramA request is sent to the database to obtain data. Then the overall response time is:

 

In fact, in a strict sense, the overall response time also includes the amount of time it takes for an application to process data after the data is sent to the application. However, the focus here is on Database waits. Therefore, we will not consider the time of applications.

 

 

Let's take a look at the waiting issues.

 

What is the waiting statistics?

 

The official Microsoft statement is: when a request is generated within SQL Server, the request cannot be processed immediately for some reason, the system then enters the waiting state for this request. The internal engine of SQL Server tracks the time spent waiting for requests, aggregates the waiting time based on database instances, and saves the information in the memory.

The above statement may be a little blunt. Let's take a simple example. For example, when we run a query, SQL Server cannot process our query requests in time because the CPU or other resources are occupied, our request can continue only after the resources are released. During this period, our query request will be suspended and put in the waiting queue. In addition, the SQL Server internal engine will go back and save some records to indicate the waiting type of the query request, or, because of what causes waiting.

 

You can see the following figure:

 

 

With the help of this image, we can understand it very well. I am not doing too much parsing here, so we will talk about it later.

 

From the above discussion, we can know that if we can analyze the waiting statistics stored inside the database engine, the accuracy will be improved much more than before.

 

 

 

Types of waiting

Since we need to analyze the problem with internal wait statistics, we need to first know which wait types are available.

In SQL Server, there are many waiting types. For ease of use and management, these waiting types are classified. So, let's take a look at the types of waiting.

It may be a little scary: within SQL Server, you can track more than 400 wait types. With so many waiting types, we can feel that there are too many reasons for waiting. If we analyze it by using our own "millet + rifle" method, how much effort will be spent.

Although there are so many waits, we often use them, or we only pay attention to a small number of waits, such as those related to resource competition: CPU, I/O, memory. Next, we will introduce the four types of waiting that we often use:

Resource waiting(Resource waits ):This type of waiting occurs in the following scenario: when a worker thread wants to access a resource, the resource is occupied by other threads. In this waiting classification, we often see typical ones, such as locks, latches, and networks.

Signal wait (Signal waits): The amount of time a worker thread spends waiting for CPU availability. In SQL Server, if all the resources required for running a worker thread are ready, it will be run, but the CPU can only run one thread at a time, the ready worker threads will be placed in the queue called runnable, waiting for the arrival of the CPU clock and then running. At this point, you can know that if the wait time is long, it indicates that the CPU is under pressure, because the thread to be run is not short of any resources, and the execution is executed by the CPU.

Queue wait (Queue waits): The waiting for such classification is like this: a working thread is idle and waiting for a task to teach it. Here, we need to note that there is a working thread pool in the database, and every request we send to the database is sent to the working thread, then these threads run. For example, if you want to borrow books from the library, there are many librarians in the library. These administrators are called "working threads ". When we initiate a request to borrow a book, the request will be handed over to the librarians who will find the book. If the library is never borrowed, or few people borrow, these administrators are idle. This type of wait is often related to the system and can be used to analyze deadlocks and other situations.

External wait (External waits ):It is easy to understand that when the SQL Server worker thread waits for an external event to occur before it runs, it has to wait before the event occurs, such as using the linked server for query, you must wait until the result is passed before proceeding.

 

 

 

In order to reply to the"IntroductionDatabaseWaiting queue".