Main Monitor counts about SQLServer 2000(SQLServer 2000中主要的计数器)

Main Monitor counts about SQLServer 2000

The following describle the main counts about SQL Server.include:

1.Monitoring Memory and Paging File Use

2.Processor

3.Monitoring Physical Disk I/O

 

1.Monitoring Memory and Paging File Use

These counters indicate the amount of bytes currently available for use by processes, the number of pages that relate to page faults, and the rate of page faults attributable to SQL Server.

		Object: Counter		Description		Guideline
		Memory: Available Bytes		Monitors the number of bytes available for use by processes to execute		This counter should always be greater than 5000 KB. Low values indicate an overall shortage of physical memory, which should be increased.
		Memory: Pages/sec		Monitors the number of pages that the Windows 2000 operating system reads from or writes to hard disk to resolve hard page faults		This counter should never be consistently greater than zero. If the value is consistently greater than zero, the Windows 2000 operating system is using the paging file to fill memory requests.
		Process: Page Faults/sec/SQL Server Instance		Monitors the page faults caused by Windows 2000 trimming the working-set sizes of those processes		A high number for this counter indicates excessive paging and disk thrashing. Check whether it is SQL Server or another process that is causing the excessive paging.

 

Isolating Memory Used by SQL Server

You should monitor the following performance counters to help identify the amount of memory that SQL Server, rather than other processes, is using.

		Object: Counter		Description		Guideline
		Process: Working Set/SQL Server Instance		Monitors the amount of memory that the SQL Server process uses for an instance of SQL Server.		This counter should be greater than 5,000 KB. When this counter falls below 5,000 KB, no additional memory is available for SQL Server.
		SQL Server: Buffer Manager: Buffer Cache Hit Ratio		Monitors the percentage of pages in the buffer cache, without reading from hard disk. Does not differentiate between physical memory and paging file memory that is used for buffer cache.		This counter should be greater than 90 percent, because it indicates the number of pages found in the cache.
		SQL Server: Buffer Manager: Total Pages		Monitors the total number of pages in the buffer cache, including database, free, and stolen pages from other processes.		A low number may indicate frequent disk I/O or thrashing. Consider adding more memory.
		SQL Server: Memory Manager: Total Server Memory		Monitors the total amount of dynamic memory that the server is using.		If this counter is consistently high in comparison to the amount of physical memory available, more memory may be required.

 

 

2.Processor

The following table lists the descriptions and guidelines for useful counters of the System and Processor objects.

		Object: Counter		Description		Guideline
		Processor: %Processor Time		Monitors the percentage of time that the processor spends processing non-idle threads		This counter should be less than 90 percent. If this counter is higher, decrease the workload, increase the efficiency of the workload, or add more processor power.
		System: Context Switches/sec		Monitors the number of times per second that the processor switches among threads		On a multiprocessor computer, if this counter reaches 8000, and the Processor: % Processor Time counter is over 90 percent, consider enabling SQL Server fiber mode scheduling.
		System: Processor Queue Lengthr		Monitors the number of threads waiting for processor time		This counter should never be consistently greater than 2. If this counter is consistently greater than two, decrease the workload, increase the efficiency of the workload, or add more processor power or processors in a multiprocessor system.
		Processor: %Privileged Time		Monitors the percentage of time that the processor spends in privileged time executing Windows 2000 operating system kernel commands, such as processing SQL Server I/O requests		If a significant percentage of processor time is spent performing system kernel commands, and physical hard disk counters are high, consider improving the performance of your hard disk I/O subsystem.
		Processor: %User Time		Monitors the percentage of time that the processor spends executing user processes, such as SQL Server itself		This can indicate that other processes or applications are executing and preventing SQL Server operations.

 

SQL Server uses Windows 2000 I/O calls to perform disk reads and writes. SQL Server manages when and how disk I/O is performed but relies on Windows to perform the underlying I/O operations. The I/O subsystem includes the system bus, disk controller cards, disks, tape drives, CD-ROM drive, and many other I/O devices. The disks are frequently the biggest bottleneck in a system.

3.Monitoring Physical Disk I/O

Monitoring hard disk I/O will help you determine whether page reads and writes exceed the capabilities of the hard disk subsystem. A busy hard disk subsystem also may indicate excessive paging file I/O caused by insufficient memory. The following table describes performance object counters that you can use to monitor the performance of your hard disk subsystem.

		Object: Counter		Description		Guideline
		PhysicalDisk: %Disk Time		Monitors the percentage of elapsed time that the hard disk services read/write requests		This counter should be consistently less than 90 percent.
		PhysicalDisk: Avg.Disk Queue Length		Monitors the average number of read/write requests that are queued		This counter should be no more than 2 times the number of spindles.
		PhysicalDisk: Disk Reads/sec		Monitors the rate of read operations		This counter should be consistently less than the capacity of your hard disk subsystem.
		PhysicalDisk: Disk Writes/sec		Monitors the rate of write operations		This counter should be consistently less than the capacity of your hard disk subsystem.