"Turn" Microsoft®sql server®2012 performance Dashboard Reports

Http://www.cnblogs.com/shanyou/archive/2013/02/12/2910232.html

SQL Server Performance Dashboard reports is a set of Reporting Services reports that are used with reports that are described in SQL Server Management Studio. These reports allow database administrators to quickly determine if bottlenecks exist in their systems and whether bottlenecks are occurring, and capturing these additional diagnostic data can be more helpful in resolving problems. For example, the system is waiting for disk IO, which is a dashboard that allows the user to quickly see which of the session,session in which query plan, which statement in the query plan consumes IO the most.

Performance dashboard can help solve some of the common performance issues, including:

-cpu bottleneck problem (what query consumes CPU)-io bottleneck (what query consumes IO most)-index recommendation scenario (unused index) generated by the query optimizer-blocking issues-latch competition issues

SQLSERVER2005 's performance tool performance Dashboard is a new extension tool that is newly added to SQLServer2005 and is available shortly after SP2 is released. For specific installations see [original] Tips for installing SQL Server 2005 Performance Dashboard reports. The performance Dashboard report for SQL Server 2008/2008 r2/2012 can download the latest toolkit from here: Microsoft®sql server®2012 performance Dashboard Repo Rts.

The information captured in the report stems from the dynamic management view of SQL Server, which does not require additional tracking or data capture, and the information is consistently available, so it is a way to manage servers that are less resource-intensive.

Reporting Services does not have to be installed for reports that use Performance dashboard.

1. Download the performance Dashboard report for SQL Server 2008/2008 r2/2012: microsoft®sql server®2012 performance Dashboard Reports, after installation in Dashboard and related custom reports can be found under C:\Program Files (x86) \microsoft SQL Server\110\tools\performance Setup.sql.

There are installation methods in the Readme, and there are specific ways to use them in the document.

2. Open SSMs Execution Setup.sql

3. Select Custom report PERFORMANCE_DASHBOARD_MAIN.RDL, load and see the Performance Analysis report:

Performance dashboard does not collect or store any information, but instead extracts the current data from within SQL Server. Because of this, a lot of data starts at a certain time, but sometimes you see some historical data as a byproduct to see how SQL Server works. Historical data is limited, but useful, as we'll mention later.

I point out this because you have to manually refresh performance Dashboard to get the latest active snapshot of your SQL Server. This is also easy to do, just click the Refresh button on the top of the performance dashboard, just like this picture below.

Now, let's look at some of the main areas of performance dashboard and see what they can tell us. The main page is divided into five large chunks.

1. Review CPU usage, and if SQL Server CPU usage is longer than 80%, be aware that I/O is causing CPU bottlenecks (over-switch).

System CPU Utilization

This is useful for most Dba,system CPU utilization (System CPU usage) charts. You can see at least 15 minutes of SQL Server CPU activity, starting with SQL Server startup and updating every minute. However, note that if you just started the SQL Server service, there will be no CPU activity chart, because it is not 15 minutes, and after 15 minutes, you will see the data for the first time. It is also emphasized here that CPU usage is not an exact value but an approximate value, but a approximate value is sufficient. In the following example, you will see a 15-minute CPU data, and after each update, the chart still shows data for a 15-minute CPU activity.

Performance Dashboard not only provides information on CPU usage, but also provides a number of other valuable information that we will continue to look at.

2. Look at the types of requests that are currently waiting, and you can see where those resources are competing (if the waiting time is too long).

SQL Server performs hundreds or thousands of operations per second, but they are not done at the same time. In other words, many activities often require short-term "wait". In fact, SQL Server solves their complexity with hundreds of different wait states. As a DBA, our goal is to minimize this wait state. The more you wait, or the longer you wait, the slower your performance will be. When the waiting state reaches normal, the extended wait state is not needed, and these States need to be identified and corrected.

SQL Server uses a variety of DMV to track these wait states, and interestingly, SQL Server can also collect some historical data about the wait states since the last time the SQL Server service was restarted, both historical data and current wait state information that are very useful to DBAs.

In the initial Performance dashboard screen, you may see the following icon. Note that it is just "possible" to see. This is because this chart shows information about the current wait state of performance dashboard when it was last refreshed. It is possible that there is no waiting state, and if so, then no such chart will appear on the screen.

3. See the current event information, where you can quickly see the value of the cache hit rate (recommended >90%).

The user requests and user Sessions in the figure are performance dashboard obtained at the time of the last update. In addition, the value of elapsed time and cache hit ratio (cache trigger rate) refers to the total elapsed time of all previously completed requests. Click on the blue user requests or user sessions you can see the drop-down information. When you click on user requests, you can see the information that shows the current user request at the time of the last update. (as in the previous part of the diagram, in order to omit the display, this picture is truncated and the real icon has more information.) ）

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

This report is similar to the information displayed by current activity in Management Studio, but it can provide more information. (Again, the report is truncated, and the actual report is longer than that.) ）

4. View the relevant historical information, the data here I think is quite valuable, you can see the I/O read/writes status, and what kind of wait type most. There are also the most time-consuming queries that can be found (depending on CPU, run time ... , etc.).

Although performance dashboard does not collect historical data, some of the SQL Server DMV is collected, and 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 the history of all wait states that have occurred since the start of the SQL Server instance restart.

In the example above, we can see the maximum wait state for the network IO category of the sleep Wait states and this SQL Server instance record. If you want to see more details, you can expand these status categories. This information is very powerful and can help us to 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, along with some additional information. The following is the top section of the report, summarizing the IO situation of the database.

is another part of the report that looks at which object consumes IO the most. In addition, if you find that there is a missing index, you can drop down the report to see the specific missing index, so you can add it back.

Expensive Queries

This section provides similar information we see in other query reports, but it shows those query statements that are currently being added to the cache in SQL Server. So we can give us a better show and see what happens to our servers. There are six different ways of sorting the results (each of which will form a separate report). You can also drop down to see more details.

5. Comprehensive information can be quickly browsed, such as database overview, expansion events. such as

Active traces

Active traces identifies all traces executed in the current instance of SQL Server. Even if you can't run Profiler trace, you can still see this active Trace information. Why is it? This is because SQL Server has been automatically tracking these events for you, and when you perform a profiler trace on this strength, you will see the following information.

Databases

The databases report provides a quick tour of the database in the current instance to quickly see the configuration options for these primary databases.

Missing Indexes

The last report lists all missing index determined by SQL Server. This analysis is not as comprehensive as the database Engine Tuning Advisor does, but it shows the obvious missing index. In fact we just want to not appear in this list any missing index, which represents our database design better.

Finally, I would like to remind you:

Most of the data sources for these statistics come from the so-called dynamic management view (DMV) in SQL Server, which is the dynamic information accumulated since the start of the database entity (Instance), so that the longer SQL server runs, the more accurate the information is collected. It is also possible to find out what performance issues have accumulated since the start of the SQL Server service.

Related articles:

How to install performance Dashboard Reports in SQL 2008

[SQL] SQL Server 2008 uses extended Events SSMS Addin + performance Dashboard reports to watch the system

Use Performance Dashboard Reports

"Turn" Microsoft®sql server®2012 performance Dashboard Reports