Favorite Perf Mon Counters / Thresholds???
-
I'm wondering if you guys and gals out there have "favorite" perfmon counters you use to monitor your servers. MS only gives a brief description of each counter and rarely gives guidelines on thresholds and counter-to-counter ratios (although some are given - disk queue length comes to mind)
Here are a list of counter categories to get your thoughts going...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_9g6s.asp
Maybe someone out there has an opinion on the average ratio of (Full Scans per sec) / (Full Scans per sec + Range Scans per sec + Probe scans per sec) for a healthy OLTP server.
I've never seen a hypothesis on such a calculation, but these are the types of ideas I'd like to hear about.
Anecdotal...empirical...nothings to simple. I'd like to here about it.
-
I cover mine in my eBook, but it doesn't cover scans in the detail you're looking for... I was more geared towards overall health of the server. My homepage (Professional Site in my .sig) links to where you can order it off Amazon.
K. Brian Kelley
@kbriankelley -
Sold. RH
-
You might also want to look at http://www.sql-server-performance.com. It goes into quite a bit of depth about performance monitoring.
John
-
Here is a list of the counters we monitor - we collect the data every 5 minutes to a spreadsheet - as you watch them over time, you will get a feel for the counter levels in busy/slow periods - one of the most critical in our environment seems to the Avg Disk Queue Length - as this begins to approach or exceed 2 x the number of spindles it indicates the need to add addl spindles to the array.
PhysicalDisk(_Total)\% Disk Time
PhysicalDisk(_Total)\% Disk Read Time
PhysicalDisk(_Total)\% Disk Write Time
PhysicalDisk(_Total)\Avg. Disk Queue Length
Processor(_Total)\% Processor Time
SQLServer:Access Methods\Full Scans/sec
SQLServer:Access Methods\Page Splits/sec
SQLServer:Access Methods\Table Lock Escalations/sec
SQLServer:Buffer Manager\Buffer cache hit ratio
SQLServer
atabases\Transactions/sec: TotalSQLServer:General Connections\User Connections
SQLServer:Locks(_Total)\Lock Wait Time (ms)
SQLServer:Locks(_Total)\Average Wait Time (ms)
SQLServer:Locks(_Total)\Number of Deadlocks
SQLServer:Locks(_Total)\Lock Timeouts
SQLServer:Memory Manager\Memory Grants Pending
SQLServer:Memory Manager\SQL Cache Memory
Good Luck,
Harley
-
This is an excellent site, too. Information on everything from query tuning to why not to use fibers in most environments.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply