10. Monitoring SQL Server Performance

One of the primary responsibilities of the database administrator is to continuously monitor SQL Server performance. There are several reasons for monitoring, including performance, storage status, security, and standard compliance. Although many of these types of monitoring can be completed automatically
In most cases, the database administrator must describe the results of the monitoring and take action on them in a systematic way.
Monitoring work needs to be ongoing, and it can become very complex. Know what to monitor, when to monitor and what is
Acceptable and unacceptable behavior, which could be the content of a full-time job. Make things more difficult
Is that each SQL Server installation is different, so it is difficult to give a general recommendation about acceptable and unacceptable performance metrics.
This chapter describes the various tools used to monitor SQL Server and provides guidance on how to use these tools to identify potential security issues and optimizations . Monitoring SQL Server is a challenging process. SQL Server has a lot of interaction with each operating system subsystem. Some applications rely heavily on RA M, while others are CPU-intensive or magnetic
Disk-intensive. SQL Server may meet these three scenarios at the same time. SQL Server may also be network-intensive, especially for distributed applications, replication, or database mirroring. Many database administrators feel that the entire monitoring and optimization
The process of the transition is mysterious and vague. However, it is not so mysterious. Good understanding of tools and familiarity with monitoring
Different objects can make monitoring tasks less intimidating.
Many books discuss the subject of monitoring, and there are some websites devoted to it. This book does not cover the monitoring SQL
Everything about the server, but it will explain-some basic knowledge, which is the best starting point.
1 0.1 Performance monitoring
SQL Server 2008 monitoring can essentially be divided into 5 basic zones:
? system resources
? SQL Server itself
? Database
? Database application
? Internet
Before you begin to explore specific aspects of performance monitoring, it is important to understand the monitoring methods. To monitor and monitor, yes, no.
Make sense. monitoring hardware and SQL Server implementations are designed to anticipate and prevent performance issues. To do this, there must be some kind of plan, a strategy that allows you to devote the appropriate amount of time and resources to maintaining and improving the performance of SQL Server.

10.1.1 Performance Monitoring Policies
The strategy for monitoring and optimizing SQL Server is fairly straightforward and consists of the following steps: (1) Create a performance benchmark one if the database server does not have a baseline, change the server platform
, it is difficult to be sure that changes will achieve the desired performance improvement. The baseline contains all of the previously mentioned systems (System resources, SQL
servers, databases, database applications, and networks). The specific counters and measures are discussed later in this chapter. When evaluating benchmarks, you can identify areas that guarantee immediate optimization. If you make a change, you must create a new baseline.
(2) Complete periodic performance audits once the baseline is created, perform periodic performance audits to ensure that after the baseline is created
Performance is not degraded. This step is usually supplemented or superseded by passive audits, and the purpose of performing a passive audit is to respond to
Complaints about poor server performance. I tend to be proactive in arranging these audits, but sometimes it can happen because of unforeseen
Performance issues and perform passive audits.
(3) Making changes and assessing their impact once the audit is performed, the areas that need to be modified may be found.
When making these changes, you need to be very careful. In general, you should not make multiple changes at one time, but should first
Line one or two changes, and then evaluate the metrics that prompt you to make changes. This makes it easier to find which changes for performance
Maximum impact. The 11th chapter describes in more detail what you can do to optimize a performance audit when it identifies a problem area
Specific changes to SQL Server.
(4) Reset benchmark-Once all modifications have been made, another benchmark can be created to measure future performance trends.
Mad Clicker
I worked with a colleague who was affectionately known as the Mad Clicker. When a problem occurs in the server room, he always puts it in and starts hitting the keyboard, making a thorough change to the configuration settings to
Time to solve the problem. He will usually succeed, but it is almost impossible to repeat his operation in the future, because even
He himself was not aware of every single content he had changed. Therefore, do not become a "Mad Clicker". After each change is completed, the results are measured and archived. This repetition is simple, and if the change results in a performance
It can be rolled back easily, rather than lifted.
1 0.1.2 Create a performance benchmark
When creating baselines, it is important to monitor typical activities. Monitoring performance in a monthly import may bring
Some interesting data, but it does little to assess and raise overall system performance. There are many ways to create baselines
Two Most database administrators have their own preferences for collecting and comparing performance data. They also have their own favorite
and system views, that these counters and system views allow them to better understand the performance of the database. In fact
SQL Server performance monitoring and optimization is more of an art than a science. I've seen a number of suggestions about what System Monitor counters to collect and what SQL Server specific activities are monitored. All the suggestions are different. Some database administrators recommend that all content be monitored while another
Some suggest selectively monitoring a small subset of processes. I support the latter for two reasons. The first reason is that "information is too
More "Such a situation will certainly arise. If all the performance data is collected, it is likely to cause "trees,
Trees "because there is too much data to be examined in detail. The second (and possibly more important) cause is the performance factor.
There is no cost to collecting performance information. The more you collect, the greater the performance loss. There's an interesting
The paradox. To fully monitor performance, you must introduce performance degradation operations into the database. The dilemma that this causes is

Is that you cannot be certain that your monitoring behavior has nothing to do with unacceptable performance.
Limiting the data retrieved can reduce this uncertainty, but remember that it should not be viewed in isolation from any one special
Fixed counter. For example, heavy disk activity may be caused by memory limitations, and CPU performance may be
caused by poor query or index loss for writing. Any subsystem is not in a vacuum.
So what should be included in the benchmark? Based on years of experience, I've summed up a benchmark and performance review
A list of monitored objects and processes. These counters are described below.
The primary tool for creating performance baselines is Performance Monitor. However, we also use the dynamic management view (Management view, DMV) to provide more context to the benchmark. After explaining the counters used for benchmarks and performance audits, this chapter provides an in-depth look at the SQL Server-specific tools and explores how to identify the unhealthy processes.
1. Performance counters
Some of the following counters are useful for performance audits. The discussion here does not include all the counters,
It's all about the counters that I and some of my colleagues rely on to understand SQL Server performance on a macro level. In addition, there are many counters that can be used to diagnose performance problems and drill down into SQL Server activity. However, the counters described here are more likely to provide the information needed to quickly assess the state of the server.
Processor counters
Processor counters (Processor Counter) are used with other counters to monitor and evaluate CPU performance and identify CPU bottlenecks.
? Processor:% Processor-----Processor:% Processor Time counter shows where
The total percentage of time spent on non-idle threads. On a multiprocessor machine, you can independently monitor each
A separate processor. If you have customized the CPU affinity settings, you may want to monitor a specific
Cpu. In addition to this scenario, I typically use the _total instance identifier to look at the combined processor usage. CPU activity is a good indicator of SQL Server CPU activity and is a key way to identify potential CPU bottlenecks. For this counter should be what kind of, different people have different suggestions.a
In General, if the total% Processor time-is greater than 70%, then a CPU bottleneck may be present, so consider optimizing the current application process, upgrading the CPU, or both. Should put
This counter is used with the processor Queue length counter to determine CPU bottlenecks.

? Process processing:% Processor time Processor times (sqlservr)-----When set to monitor from SQL Server process
Information, the process:% Processor Time counter can be used to determine how many of the total processing times are
SQL Server occupies.

? System:processor Queue Length-----Processor Queue Length counter shows wait by
The number of threads processed by the CPU. If the average queue length is consistently greater than twice times the number of processors, you can
A CPU bottleneck can occur because the processor is too late to process the request.
You can use the Processor Queue length and% Processor time counters to determine if there is a CPU
Bottleneck. If both are outside the acceptable range, there must be a CPU bottleneck.

If the Processor Queue length is not within the acceptable range, but the% Processor time in acceptable fan
, there may not be a CPU bottleneck, but there is a configuration problem. Need to ensure that for the system, no
you have set the maximum number of worker threads (max worker threads) server to a value that is too large. The maximum number of worker threads is the default

The default setting is 0, which causes SQL Server to automatically set the maximum number of worker threads to match the values in table 10-1. However, in addition to 0, you can also configure 128? Any value between 32 and 767. SQL Server online The acceptable range from the book is 32? 32 767, this is wrong. Will the graphical interface accept 0? Any value between 32 767, but 1?
Any value between 127 will be set to 128.

10. Monitoring SQL Server Performance