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

Counter	What It Monitors	Most Commonly Accessed Counts
.NET CLR:Data	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	Notifications
MSFTESQL:Catalogs	Facts about text index catalogs	State
SQLAgent:Jobs	Data about jobs executed under agent control	Active jobs, failed jobs, queued jobs
SQLAgent:JobSteps	Data about individual job steps	Active steps, queued steps
SQLAgent:Alerts	Data about alerts fired	Activated alerts
SQLServer:Locks	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.