Using Windows System Monitor

Microsoft Windows server operating systems offer the System Monitor graphical tool (formerly known as Performance Monitor [PerfMon]) to measure the performance of all aspects of the server. You can use System Monitor to view SQL Server and other applications from a coarse perspective, looking at events from a high level. You can view SQL Server objects and performance counters by using the System Monitor tool. You can also use it to gather information pertaining to the behavior of processors, memory, cache, and other object resources on the server.

Each object you monitor by using System Monitor has an associated set of counters. These counters are put in place when software is installed in the Windows environment. You can use them to measure aspects of an object, such as device usage, queue lengths, delays, and other factors. In general, when looking at information on how the server as a whole is operating and how application are performing in respect to the server's performance, the System Monitor provides you with optimum benefits.

When you install SQL Server and its related components, several sets of counters are placed in the environment and made available for monitoring. The general list of objects available to be monitored depends on which SQL components have been installed. The following prefixes identify the objects you can monitor in SQL Server:

  • .NET CLR Use this prefix to access the common language runtime.

  • MSAS 2005 Use this prefix to access Microsoft Analysis Services.

  • MSFTESQL Use this prefix to access the Microsoft Full-Text engine.

  • MSRS 2005 Use this prefix to access Microsoft Reporting Services.

  • MSSQL$InstanceName Use this prefix to access a named instance of the database engine.

  • SQLAgent$InstanceName Use this prefix to access a named instance of the SQL Server Agent.

  • SQLAgent Use this prefix to access the default instance of the SQL Server Agent.

  • SQLServer Use this prefix to access the default instance of the database engine.

Each of the software categories has several related objects, and within each object is a set of one or more counters. Depending on the information you want to gather, you need to select the objects in the software prefix you are looking for and then select one or more counters from that object's list. Each of the software categories has some counters that may be of interest.

Although we could easily spend several chapters going over this one utility, there is no need for that level of coverage on System Monitor for the 70-431 exam. You need to be familiar with a few of the counters; Table 7.1 lists the most important of them.

Table 7.1. Common System Monitor Counters


What It Monitors

Most Commonly Accessed Counts


Specifics about data connections

Current connections, pooled connections, failed connections

.NET CLR Exceptions

Specifics about exception instances

Exceptions thrown

.NET CLR:Networking

Specifics about data flow through the network

Bytes sent, bytes received

MSAS 2005:Cache

Particulars about the Analysis Services cache

Lookups, hits, misses

MSAS 2005:Memory

Particulars about the memory utilization of Analysis Services

Memory usage

MSAS 2005: Proactive Caching

Particulars about Analysis Services proactive caching use



Facts about text index catalogs



Data about jobs executed under agent control

Active jobs, failed jobs, queued jobs


Data about individual job steps

Active steps, queued steps


Data about alerts fired

Activated alerts


Information about lock requests made

Lock time-outs, deadlocks per second

SQLServer: Plan Cache

Information about the SQL Server cache

Hit ratio, objects in use

SQLServer: Buffer Manager

Information about memory buffers and their use

Buffer cache hit, lazy writes, read-aheads

SQLServer: SQL Statistics

Information about aspects of T-SQL queries

Batch requests

SQLServer: General Statistics

Information about general serverwide activity

Users connected, active temporary tables, logins

SQLServer: Databases

Information about a SQL Server database

Free log space, number of transactions, transactions per second

Exam Alert

On Microsoft exams, always be on the lookout for misleading answers. You will find it easiest to answer some of the performance-related questions if you first eliminate the obviously incorrect responses. For example, you should not select any counters that don't deal with the product being monitored. The 70-431 exam is not an operating system exam, so you don't need to know about every counter available.

As you begin any of these processes, you need to begin with the hardware and operating system and then proceed into the application server. As you get further into data gathering and analysis, you should look into each database and the interactions between the data and the user applications. To view SQL Server tasks in detail, after the initial data-gathering processes, you use the SQL Server Profiler to develop a more complete picture.

MCTS 70-431(c) Implementing and Maintaining Microsoft SQL Server 2005
MCTS 70-431 Exam Cram: Implementing and Maintaining Microsoft SQL Server 2005 Exam
ISBN: 0789735881
EAN: 2147483647
Year: 2006
Pages: 200
Authors: Thomas Moore © 2008-2017.
If you may any questions please contact us: