Today I’m going to give a brief about collecting Performance Monitoring metrics using Perfmon. ( For a better Picture of what exactly is happening with your SQL Server, you may need to use SQL Profiler along with Perfmon and Ofcourse you should consider using DMO’s(Dynamic management Objects) starting SQL Server 2005.
Enough intro 🙂
As a DBA you must and Will use Perfmon if you want to gather Real Metrics on your Server(Both Physical and SQL Server). But if you are new to perfmon i’m sure that you’ll be confused with the number of counters and metrics it offers. Make sure to familiarize with atleast important counters which we as a DBA might need to capture your server health. Consider opening from your client machine or choose a DEV Server( ideally sitting in the same data center) and capture your production server metrics from there.
Familiarize yourselves with scheduling counter logs and analyzing what it captured.
What format to choose for your counter logs?
You can leave it as default(BLG) and you can make use of PAL tool( It’s a free and fabulous tool for analyzing your Performance Logs). Please note Log Parser is a prerequisite to install PAL. Again, don’t install these tools on your Servers. Make use of your Client machine(s).
or
CSV – if you are more into and addicted to Excel, you can save it to a CSV file and open it with Excel and slice and dice the results accordingly.( you can go as fancy as you can with all Cool graphs and color coding and what not).
Get familiar with Win Server 2003/2008/2008R2 as most of the shops out there are still running on Win Server 20003.
Okay…enough blabbering and let’s get straight into the point.
Counters we must monitor for SQL Server at Minimum:
Processor – % Processor Time
System – Processor Queue Length
Memory – Available MBytes – ideally always should > 1GB(at least 750 MB)
Memory – Page Faults/Sec – look for constant high numbers. That’s something which we don’t want.
Paging File – % Usage
SQLServer: Buffer Manager – Page life expectancy – more the Number more better.
SQLServer: Buffer Manager – Page reads and Writes/Sec. Note – these are physical Reads not Logical Reads.
SQLServer: Memory Manager – Memory Grants Pending – Sessions which are waiting for memory to be granted. This should be ideally close to Zero always.
SQLServer: SQL Statistics – Batch Requests/sec
SQLServer: SQL Statistics – Compilations/sec
SQLServer: SQL Statistics – Recompilations/sec
Physical Disk –Avg Disk Reads/Sec
Physical Disk –Avg Disk Writes/Sec – < 8 msec on nonchached Disks and <1 msec on cached Disks
Physical Disk – Avg. Disk sec/Read and Write – The Avg. Disk sec/Read performance counter indicates the average time, in seconds, of a read of data from the disk.
I would say DISK I/O metrics are little tricky and sometimes misleading( It really depends on what storage you are on. I’ll get into more specifics below)
For example,Your perfmon says you have waits for disk reads/writes. Does this always mean that you need faster and more disks?
A big screaming Noooo! It could be paging, So the real culprit is memory not your disks.
Also, are your Disks DAS D’s or SAN D’s? Let me make something very clear, Windows designed Perfmon DISK metrics for your Direct Attached Storage not for your SAN Storage.
For example let’s consider Disk Queue Length Metric:
Disk Queue Length will Vary….It actually depends on how Old/New your Drives are and also we should consider do we really know how many Disks are present in our array??
All the Modern days Drives will come with Read-Ahead/Rear-Behinds, which will increase Disk queue lengths which is really not a Problem. If we don’t have any regular Long durationIO Stalls in our SQL Server (we can find out using virtualfilestats DMO) we are fine!
Read-ahead/read-behind:
When executing a read from the disk, the disk arm moves the read/write to (or near) the correct track, and after some settling time the read head begins to pick up bits. Usually, the first sectors to be read are not the ones that have been requested by the operating system. The disk’s embedded computer typically saves these unrequested sectors in the disk buffer, in case the operating system requests them later.
Disk Buffers:
Disk Controllers, The disk buffer is usually quite small from 2 to 32 MB for most of the drives out there present in market.
Disk Array Controllers, as opposed to Disk Controllers usually have normal cache memory of around 0.5 to 8 GB.
Typically Perfmon Counters for Disk(s) are designed for DASD’s, not for SAN D’s. For SAN ideally we’ve to go with the Vendor provided Tool for monitoring.
I personally feel monitoring the counters of sec/read and sec/write; Reads/Sec and Writes/Sec in Perfmon and the IO stalls by virtualfilestats DMO in SQL server would give us a much better picture of DISKS(especially in the present days of SAN).
For Newbies
DAS – Direct Attached Storage
SAN – Storage Area Network
Like this:
Like Loading...
Related
MSSQLTREK 