SQL Server 2000 Consolidation: a business case

Server utilization is determined by performance measurement data. This consists primarily of SQL
Server performance data collected using Microsoft’s Windows Performance Monitor tool and SQL
Server profiler trace activity. Customers can also choose third-party tools to simplify the process.
Scalability Experts has built a data-collection process that can be configured for automated data
collection against multiple servers across multiple domains. This process involves running native SQL
scripts from two central collection servers that have minimal impact on the production environment
and are least intrusive. Microsoft Systems Management Server (SMS) or a third-party tool may also be
used to gather hardware configuration data and deploy scripts on the servers.
The counters listed in Table 8, following, provide a statistical picture of a server’s resource utilization,
covering all the major resource areas (memory, disk, and CPU).
Table 8. SQL Server resource utilization counters
Object Counter
%Processor Time Processor
Processor Queue Length
Available Bytes Memory
Page Faults/Sec
% Disk Time
Avg. Disk sec/Read
Avg. Disk sec/Write
Current Disk Queue Length
Disk Reads/sec
Disk Writes/sec
Physical disk
Avg. Disk Queue Length
Server Server Sessions
SQL Server: cache manager Cache Hit Ratio
SQL Server: databases Transactions/Sec
SQL Server: general statistics User Connections
File Read Operations/sec
File Write Operations/sec
System
Processor Queue Length
Once the consolidation matrix is completed, the servers are grouped together in, for example, the
following order. (This order can be adjusted to meet a company’s business needs.)
1. Sensitivity
2. Domain
3. Capability
4. Lifecycle
5. Clustered
6. Failover
7. Tier
20