Microsoft® SQL Server® 2012 Bible

909

CHAPTER

37

Performance Monitor and PAL

IN THIS CHAPTER

Observing Server Performance

Saving Counter Logs

Using PowerShell to Gather Performance Counters

A

s a Database Professional, using and interpreting performance counters is one of the most

important skill sets you can develop. When server performance starts to go sideways, jump-

ing in and “reading” what’s happening on the server based on a set of counters allows you to

quickly identify and attack trouble areas.

Performance Monitor, or PerfMon, has been around for quite a while and isn’t going anywhere.

Anyone working with Windows as an IT platform is probably familiar with PerfMon. These are the

fi rst tools used for high-level diagnostics and the health of any server.

SQL Server extends PerfMon by adding hundreds of SQL Server–specifi c counters. Although PerfMon

alone doesn’t provide enough detail to fully diagnose SQL Server, it does a great job to illustrate the

overall server performance issues and highlighting SQL Server themes.

PerfMon is more than just a pretty face. PerfMon’s counter logs can write data to a binary perfl og

(*.blg) fi le or to a comma-delimited fi le (universal across Windows versions).

Perfl ogs can be integrated into SQL Server Profi ler for review and analysis. However, SQL Server

Profi ler will be deprecated in a future version of SQL Server. If you want to get up to speed on

what’s replacing Profi ler, check out Chapter 40, “Extended Events.”

Using PerfMon

PerfMon includes a couple of important features: System Monitor and Data Collector Sets.

Some servers have it installed in the Administrative Tools menu. It’s also found at

Control Panel ➪ Administrative Tools ➪ Performance Monitor, and it can be launched

from SQL Server Profi ler’s Tools ➪ Performance Monitor menu command.

c37.indd 909c37.indd 909 7/31/2012 10:03:51 AM7/31/2012 10:03:51 AM

http://www.it-ebooks.info