SQL Server 2000 Consolidation: a business case
performance data has been built up over a period of time for low, average, and peak usage periods,
a subjective determination of an acceptable performance can be made for each system. These
established baselines can be used to investigate long-term changes in usage patterns for database
capacity planning.
Establishing baselines
To gain enough information to establish system baselines, various counters need to be monitored.
A trend line can be established by monitoring them over a period of weeks and months.
Use the following counters to establish the CPU baseline:
• Process: Working Set (sqlservr)
• Processor\% Processor Time
• System\Processor Queue Length
Use the following counters to establish the memory baseline:
• Memory\Pages/sec
• Memory\Available Bytes
• Paging File(_Total)\% Usage
• SQL Server: Buffer Manager: Buffer Cache Hit Ratio
• SQL Server: Buffer Manager: Total Pages
• SQL Server: Memory Manager: Total Server Memory (KB)
Use the following counters to establish the disk baseline:
• LogicalDisk\% Free Space
• PhysicalDisk\% Disk Time
• PhysicalDisk\Current Disk Queue Length
• PhysicalDisk\Disk Reads/sec
• PhysicalDisk\Disk Writes/sec
• PhysicalDisk\Avg.Disk Queue Length
• Memory\Available Bytes
• Memory\Cache Bytes
• Memory\Pages/sec
• Processor(All_Instances)\% Processor Time
• SQL Server: Buffer Manager\Page Reads/sec
• SQL Server: Buffer Manager\Page Writes/sec
• System\Processor Queue Length
SQL Server consolidation—repeatability and scalability
Though server consolidation is an intricate process, the benefits can include a more reliable and
scalable solution at a lower operating cost. The Scalability Experts server consolidation process
provides a tried and proven approach. The phases of this process include:
• Phase 1: scoping and server utilization study
• Phase 2: designing the consolidated servers
• Phase 3: migration
• Phase 4: test and measurement
29