Databases Reference
In-Depth Information
➤
Management Data Warehouse —
This SQL Server Management Studio tool is used for
performance trending. You can use it to collect and consolidate various data over time,
which you can then analyze to see how performance has changed.
➤
Policy-Based Management (PBM) —
PBM can be used to validate whether predetermined
standards have been followed. Some policies can prevent certain actions from ever
occurring.
DATA ANALYSIS
After data collection, data analysis is the second iterative activity required to identify the problem's
root cause. It may be necessary to perform data analysis multiple times for a single problem, includ-
ing data from multiple sources and formats. The typical starting point is to review PerfMon output
to identify bottlenecks or contention with I/O, CPU, memory, or the network resources. Often, once
the main bottleneck is resolved, another will appear. At this point it is important to understand the
success criteria and SLAs to have a clear goal and know when to stop troubleshooting.
The following list describes several tools and utilities you can use to analyze the data collected:
➤
database and generates reports showing the most common performance issues based on that
data. It takes Proi ler, PerfMon, and PerfStats as input. For example, it can show all state-
ments captured in the trace that were blocked for more than 30 seconds. SQL Nexus is com-
monly used to show the stored procedures or queries that had the highest duration, were
executed most frequently, or used the highest cumulative CPU. With stored procedures, it
is possible to drill down to get more specii c information on duration and other statistics
about individual queries within the stored procedure. SQL Nexus is a great tool for taking a
large amount of data and quickly locating pain points that require more thorough examina-
tion. You can read all about it in Chapter 12.
➤
Proi ler —
Proi ler can be used to replay statements if the right events were captured. A
Replay template built into Proi ler can be used to capture those events. This is useful to test
the same queries repeatedly against a database to which changes have been made, such as
modifying indexes and altering i le layout. The impact/benei ts of these changes can be
measured relative to the overall workload using this consistent set of replay workload.
Proi ler can also be useful for troubleshooting security and connectivity problems.
➤
PerfMon —
Performance Monitor can be used to isolate CPU, memory, I/O, or network
bottlenecks. Another use is to help determine whether SQL Server is the victim of another
process (such as anti-virus apps or device drivers), consuming resources such that SQL
Server performance is affected.
➤
Database Tuning Advisor (DTA) —
The DTA can take as input either an individual query
or an entire trace workload. It makes recommendations for possible index or partition-
ing changes that can be tested. Never implement suggestions from DTA without thorough
review in the context of the total workload. Analysis with DTA is most effective when a
complete workload can be captured in a trace and processed.
Search WWH ::
Custom Search