444.Counters of SQL Server 2005

1. Basic knowleadge about SQL Server Performance Counter

 

Category	Counter	Bottlenecks
Memory	Memory: Pages/sec	measures te number of pages per second that are paged out from RAM to Virtual memory on the hard disk. High
	Memory: Available Bytes	measures the amount of free physical memory on a server.<5Mb
	SQL Server: Buffer Manager: Buffer Cache Hit Ratio	indicates how often SQL Server accesses the buffer rather than the hard disk to get data. <90%
	Physical Disk : Disk Read/sec	High
	Physical Disk : Disk Write/sec	High
Disk	Physical Disk: % Disk Time	Messure the pressure on a physical hard disk array. >55%
	Physical Disk: Avg. Disk Queue Length	if it exceed 2 for each individual disk drive in an array. >10Min
	Physical Disk: % Free Space	<15%, should alert the administrator
	Logical Disk: % Free Space	<15%, should alert the administrator
Processor	Processor: % Processor Time	>80% for continuous periods of 10Min
	Processor: Processor Queue Length	>2 per CPU for continuous periods of 10Min
Network	Network Interface: Bytes Received/sec	shows the rate at which bytes are received over each network adapter.
	Network Interface: Bytes Sent/sec	shows the rate at which bytes are sent over each network adapter.
	Netwok Interface: Bytes/sec	total level of network traffic both to and from a server.
	Netwok Interface: Output Queue Length	shows the length of the output packet queue in packets. >2
User Connections	SQL Server: Genereal Statistics: User Connections	shows the number of user connections, not the number of users currently connected to Server

2. Practise

There is one vitrul PC (Windows 2003 Entperise. T7500 2.2G, 256MB RAM), which hosts a SQL Server 2005.
When the SQL Server is idle, whose performance is dicated as the following figure

When i execute the following T-SQL, whose performance is shown here:

Then, we look up anohter case, what can you infor from it?

转载于:https://www.cnblogs.com/yang_sy/archive/2009/05/04/1449069.html