Database Reference
In-Depth Information
Table 4-7.
Performance Monitor Counters to Analyze Volume of Incoming Requests
Object(Instance[,InstanceN])
Counter
SQLServer:General Statistics
User Connections
SQLServer:SQL Statistics
Batch Requests/sec
User Connections
Multiple read-only SQL Servers can work together in a load-balancing environment (where SQL Server is spread
over several machines) to support a large number of database requests. In such cases, it is better to monitor the User
Connections counter to evaluate the distribution of user connections across multiple SQL Server instances. User
Connections can range all over the spectrum with normal application behavior. This is where a baseline is essential to
determine the expected behavior. You will see how you can establish this baseline shortly.
Batch Requests/Sec
This counter is a good indicator of the load on SQL Server. Based on the level of system resource utilization and Batch
Requests/sec, you can estimate the number of users SQL Server may be able to take without developing resource
bottlenecks. This counter value, at different load cycles, helps you understand its relationship with the number of
database connections. This also helps you understand SQL Server's relationship with Web Request/sec, that is, Active
Server Pages.Requests/sec for web applications using Microsoft Internet Information Services (IIS) and Active Server
Pages (ASP). All this analysis helps you better understand and predict system behavior as the user load changes.
The value of this counter can range over a wide spectrum with normal application behavior. A normal baseline is
essential to determine the expected behavior.
Summary
In this chapter, you learned how to gather metrics on the CPU, network, and SQL Server in general. All this
information feeds into your ability to understand what's happening on your system before you delve into attempting
to tune queries. Remember that CPU is affected by the other resources since it's the thing that has to manage those
resources, so some situations that can look like a CPU problem are better explained as a disk or memory issue.
Networks are seldom a major bottleneck for SQL Server. You have a number of methods of observing SQL Server
internals behavior through Performance Monitor counters, just like the other parts of the system. This concludes the
discussion of the various system metrics. Next, you'll learn how to put all that together to create a baseline.
