Microsoft® SQL Server® 2012 Bible

978

Part VII: Monitoring and Auditing

In the past, it required a large effort to gather the necessary metrics and necessary sta-

tistics to pinpoint a performance issue. Database administrators would normally resort to

third-party monitoring tools or spending a large amount of time profi ling queries and con-

suming Dynamic Management Views (DMVs) to pinpoint a performance issue. MDW makes

the process easier; it takes less time and provides an automated and simple way to report

what is happening in real time, and also view performance patterns historically. It collects

data, aggregates data, analyzes data, and reports on data. It does this with a minimal per-

formance overhead to SQL Server.

The value of MDW can be more noticeable with the drill-down capability that allows looking

at more detail data about a specifi c event, object, or a time period. For example, we can dig

deep into the actual query execution plan and check for missing indexes or out-of-date sta-

tistics. You can also isolate and drill down into the processes running during high resource

wait periods that might hint at resource contention issues.

The ability to collect all these data to do close to real-time analysis allows Database

Administrators to quickly assess and implement corrective actions to guarantee and main-

tain performance service level agreements. Additionally, MDW is a valuable tool that helps

in capturing and documenting server activity and resource utilization trends that can be

used to do capacity planning.

Confi guring MDW

This section demonstrates how to confi gure MDW. The fi rst step is to make sure that SQL

Agent is running and SQL Server Integration Services (SSIS) is installed and running as

well. Assuming those two prerequisites have been met, follow these steps to confi gure MDW.

1. Launch SQL Server Management Studio, and connect to the SQL Server 2012
   Database Engine where you want to confi gure MDW.


2. Expand the Management section, and right-click Data Collection.


3. From the context menu, select Confi gure Management Data Warehouse, as shown in
   Figure 43-1. The Confi gure Management Data Warehouse Wizard screen displays, as
   shown in Figure 43-2.


4. Click the Next button. The Select Confi guration Task screen displays, as shown in
   Figure 43-3.


5. Select the Create or Upgrade a Management Data Warehouse option, and click Next.
   The Confi gure Management Data Warehouse Storage screen displays, as shown in
   Figure 43-4.


6. Select an existing database, or create a new database for MDW, and click Next. The
   Map Logins and Users screen displays, as shown in Figure 43-5.

c43.indd 978c43.indd 978 7/31/2012 10:18:43 AM7/31/2012 10:18:43 AM

http://www.it-ebooks.info