Shulou(Shulou.com)06/01 Report--

This article focuses on "how to optimize the performance of SQL Server", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to optimize the performance of SQL Server.

Database performance diagnosis and optimization is one of the key technologies to improve database performance and stability. It is the requirement of every user to quickly find the anomaly, locate the root cause and stop the loss.

Processor/%Privileged Time

Threshold: if the value continues to be greater than 75%, it indicates that there is a bottleneck.

Meaning: this counter represents the percentage of time spent by a thread in privileged mode. When your program calls operating system methods (such as file operations, network Icano, or allocating memory), these operating system methods run in privileged mode.

Processor/ UserTime

In contrast to the% Privileged Time counter, it refers to the percentage of time spent on operations in user state mode (that is, unprivileged mode).

Process (sqlservr.exe) /% Processor Time

CPU usage by SQLServer

Processor/% Interrupt Time

Threshold: depends on the processor

Meaning: this counter represents the percentage of time that the processor takes to receive and process hardware interrupts. This value indirectly indicates the activity of the hardware device that caused the interruption, such as network changes. A significant increase in this counter indicates that there may be a hardware problem.

System/Processor Queue Length

Threshold: if the average value continues to be greater than 2, then there is a bottleneck in CPU.

Meaning: if the ready task exceeds the processing capacity, the thread will be placed in the queue. A processor queue is a collection of threads that are ready but cannot be executed by the processor because another thread is in a state of execution. Continuous or repeated occurrence of more than 2 queues clearly indicates that there is a processor bottleneck. You can also achieve greater throughput by reducing concurrency. You can combine Processor/% Processor Time to decide whether your program can benefit by adding CPU. Even on multiprocessor computers, it is a single queue for CPU time. Therefore, on a multiprocessor computer, the value of Processor Queue Length (PQL) is divided by the number of CPU used to handle the load. If CPU is very busy (more than 90% usage), the average of PQL continues to be greater than 2/CPU, which should have CPU bottlenecks and benefit from more CPU. Alternatively, you can reduce the number of threads and increase the queues at the application layer. This results in a small amount of Context Switching, but a little Context Switching is good for reducing the load on CPU. A common reason for PQL greater than 2 but low CPU utilization is that requests for CPU time arrive randomly and threads request asymmetric CPU time from the processor. This means that the processor is not a bottleneck, and your thread logic needs to be improved.

SQLServer:SQL Statistics/Auto-Param Attempts/sec

The number of automatic parameterization attempts per second. The total should be the sum of failed, safe, and unsafe automatic parameterization attempts. When the SQL Server instance tries to parameterize the Transact-SQL request by replacing some text with parameters, it is automatically parameterized so that the execution plan saved in the cache can be used again for multiple similar requests. Note that in newer versions of SQL Server, automatic parameterization is also called simple parameterization. This counter does not include forced parameterization.

SQLServer:SQLStatistics/Failed Auto-params/sec

The number of failed automatic parameterization attempts per second. The value should be very small. Note that in later versions of SQL Server, automatic parameterization is also called simple parameterization.

SQLServer:SQLStatistics/Batch Requests/sec

The number of batches of Transact-SQL commands received per second. This statistic is affected by all constraints, such as the number of users, the cache size, the complexity of the request, and so on. A high number of batch requests means good throughput.

SQLServer:SQL Statistics/SQLCompilations/sec

The number of SQL compilations per second. Represents the number of times the compiled code path has been entered. Includes compilation caused by statement-level recompilation in SQL Server. When the SQL Server user activity is stable, the value will reach a steady state.

SQLServer:SQL Statistics/SQLRe-Compilations/sec

The number of statement recompilations per second. Calculates the number of times the statement is triggered to recompile. Generally speaking, this number had better be small.

At this point, I believe you have a deeper understanding of "how to optimize the performance of SQL Server". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.