SQL Server performance dashboard reports

SQL Server performance dashboard reports is a set of Reporting Services reports, which are used together with the reports described in SQL Server Management studio. These reports allow database administrators to quickly determine if there are bottlenecks in their systems and whether the bottlenecks are occurring. Capturing these additional diagnostic data may be more helpful for solving the problem. For example, the system is waiting for disk Io, which is a dashboard that allows users to quickly view which session, which query plan in the session, and which statement in the query plan consumes the most Io.

Performance dashboard can help solve common performance problems, including:

-CPU bottleneck (what queries consume the most CPU)
-I/O bottleneck (what queries consume the most I/O)
-Index recommendation solution generated by the query optimizer (no index is used)
-Blocking
-Latch Competition

Performance dashboard, a performance tool of sqlserver2005, is a new extended tool added to sqlserver2005 and available shortly after the release of SP2. For detailed installation instructions, refer to [original] tips for installing SQL Server 2005 performance dashboard reports. For performance dashboard reports of SQL Server 2008/2008 R2/2012, download the latest Toolkit: Microsoft SQL Server 2012 performance dashboard reports.

The information captured in the report is derived from the dynamic management view of SQL server. It does not require additional tracking or data capture, and the information is consistent and available, therefore, it is a method for managing servers that do not consume much resources.

Reporting Services is not required to install performance dashboard reports.

1. Download the performance dashboard Report of SQL Server 2008/2008 R2/2012: Microsoft SQL Server 2012 performance dashboard reports. After installation, go to c: \ Program Files (x86) \ Microsoft SQL Server \ 110 \ tools \ performance dashboard can be found in setup. SQL and related Custom reports.

There are installation methods in readme and specific usage methods in the Help file.

2. Enable SSMs to execute setup. SQL

3. Select the custom report performance_dashboard_main.rdl. After loading the report, you can see the performance analysis report:

Performance dashboard does not collect or store any information, but retrieves existing data from the SQL server. Because of this, a large amount of data starts at a specific time, but sometimes you will see some historical data as a by-product to see how SQL Server works. Historical data is very limited, but it is very useful. We will mention it later.

The reason is that you must manually refresh the performance dashboard to obtain the latest snapshot of your SQL Server activity. This is also easy to do. Just click the refresh button at the top of the performance dashboard, as shown in the figure below.

Now let's take a look at some of the main performance dashboard areas to see what they can tell us. The home page is divided into five blocks.

1. Check the CPU usage. If the SQL Server CPU usage exceeds 80% for a long time, you may need to check whether I/O causes a CPU bottleneck (excessive switch ).

System CPU utilization

For most DBAs, the system CPU utilization (system CPU usage) chart is very useful. You can see the CPU activity of SQL Server at least 15 minutes, starting from SQL Server start, update every minute. However, please note that if you have just started the SQL Server service, there will be no CPU activity charts, because it is less than 15 minutes. After 15 minutes, you will see the data for the first time. Here I want to emphasize that the CPU usage is not a precise value, but an approximate value, but an approximate value. In the following example, you will see a 15-minute CPU data, and after each update, this chart still shows a 15-minute period of CPU activity data.

Performance dashboard not only provides CPU usage information, but also provides many other valuable information. Let's continue to take a look.

2. Check the type of the current request waiting. Here we can see the resource competition (if the waiting time is too long ).

SQL Server executes hundreds of operations per second, but they are not completed at the same time. That is to say, many activities usually require short-term "Waiting ". In fact, SQL server uses hundreds of different wait states to solve their complexity. As a DBA, our goal is to minimize the waiting state. The more wait status, or the longer the wait time, the slower the performance. When the waiting status reaches the normal value, the extended waiting status is not needed. You need to identify and correct these statuses.

SQL Server uses various DMV to track these waiting states. Interestingly, SQL Server can also collect some historical data about the waiting status since the last SQL Server Service restart, these historical data and current wait status information are very useful to DBAs.

On the initial performance dashboard screen, you may see the following icons. Note: It is only "possible. This is because this chart displays the information about the current wait status when the performance dashboard was last refreshed. It is very likely that there is no waiting status at the time. If so, such charts will no longer appear on the screen.

3. Check the current activity information. Here you can quickly see the cache hit rate (recommended> 90% ).

The user requests and user sessions in the figure are obtained by performance dashboard during the last update. In addition, the value of elapsed time and cache hit ratio (Cache trigger rate) indicates the sum of the elapsed time of all previous completed requests. Click the blue user requests or user sessions to view the drop-down list. When you click user requests, the information you can see shows the current user request in the last update. (Similar to the previous graph, to omit the display, this graph is truncated and more information is displayed on the real icon .)

When you click user sessions, you can see the following reports:

This report is similar to the information displayed in the current activity of Management studio, but it provides more information. (Likewise, this report is truncated. The actual report is longer than this report .)

4. Check the relevant historical information. I think the data here is quite valuable. We can see the I/O read/writes status and the most waiting type. You can also find the most time-consuming queries (by CPU, running time, etc ).

Although performance dashboard does not collect historical data, some SQL Server DMV is collected. We can see that these are historical data displayed using DMV data: waits, IO statistics, and expensive queries.

Waits

This report shows a snapshot of all historical data in the waiting status since the SQL server instance restarts.

In the preceding example, we can see the maximum waiting status of sleep wait state and the network Io category recorded by this SQL server instance. To view more details, you can expand these status categories. This information is very powerful. It can help us determine whether this wait state has a negative impact on SQL server performance.

IO statistics

These historical reports tell you which database consumes the most Io and some additional information. Below is the top part of the report, which summarizes the I/O of the database.

Is another part of this report. You can see which object consumes the most Io. In addition, if a missing index is found, you can view the specific missing index in the drop-down report, so that you can add it again.

Expensive queries

This section provides the similar information we see in other query reports, but it displays the query statements currently added to the cache in SQL Server. In this way, we can better demonstrate what happened to our server. There are six different result sorting options (each one forms a separate report ). You can also view the details from the drop-down list.

5. The integrated information can be quickly viewed, such as database overview and extended events.

Active traces

Active traces identifies all traces executed in the current SQL server instance. Even if you cannot run Profiler trace, you can still see the active trace information. Why? This is because SQL server has been automatically tracking these events for you. When you execute a Profiler trace on this strength, you will see the following information.

Databases

Databases reports provide a quick view of databases in the current instance, allowing you to quickly view the configuration options of these major databases.

Missing Indexes

The last report lists all the missing indexes determined by SQL Server. This analysis is not as comprehensive as Database Engine Tuning Advisor does, but it shows an obvious missing index. In fact, we just hope that no missing index will appear in this list, which means our database is better designed.

Finally, I would like to remind you that:

Most of these statistical data sources come from SQL Server's so-calledDynamic Management View(DMV), the information is the dynamic information accumulated after the database entity (Instance) is started. Therefore, the longer the SQL server runs, the more accurate the collected information, the more you can find out which performance problems have been accumulated since the SQL Server service was started.

RelatedArticle:

How to install performance dashboard reports in SQL 2008

[SQL] SQL Server 2008 uses extended events SSMs addin + performance dashboard reports to view the system

Use Performance dashboard reports