Microsoft® SQL Server® 2012 Bible

911

Chapter 37: Performance Monitor and PAL

37

The SQL Server Database Engine isn’t the only server to expose counters to System Monitor. Analysis Services,

Reporting Services, .NET, ASP, BizTalk, and other servers all add counters to System Monitor.

If a new counter displays a line at the top or bottom of the graph, you can get more mean-

ingful results by adjusting the scale. Using the System Monitor Properties dialog, available

from the context menu, you can adjust the scale of the graph, the scale of each counter,

and the presentation of each counter.

Although there are hundreds of possible System Monitor counters, Table 37-1 describes some

counters commonly used when investigating a SQL Server installation.

TABLE 37-1 Key Performance Monitor Counters

Object Counter Description Usefulness

SQL Server

Buffer Manager

Page life

expectancy

Number of seconds a

page stays in the buffer

pool without

references.

Monitoring page life

expectancy in an OLTP

environment can provide a

preliminary predictor of

server slowdowns.

Processor Percentage of

processor time

Total percentage of

processor activity for

physical servers; not a

reliable counter for vir-

tual servers.

If CPUs are regularly more

than 60 percent active,

additional CPU cores or a

faster server increases

performance.

SQLServer: SQL

Statistics

Batch requests

per second

SQL batch activity. A good indicator of user

activity.

Physical Disk Bytes received

per second

Measures bytes

received by the physi-

cal disk per second.

Disk throughput is a key

hardware performance fac-

tor. Carefully splitting the

database across multiple

disk subsystems probably

improves performance.

SQLServer: SQL

Statistics

Failed auto-

params per

second

Number of queries for

which SQL Server could

not cache the query

execution plan in mem-

ory; an indication of

poorly written queries.

(Check the scale when

applying.)

Locating and correcting

the queries improves

performance.

Continues

c37.indd 911c37.indd 911 7/31/2012 10:03:52 AM7/31/2012 10:03:52 AM

http://www.it-ebooks.info