Skip to main content

Performance Counters - Analysis

I've struggled to find a good source on how to properly interpret performance counters. In our case, it was determining IOPS for a potential SAN we were looking at. I had really been trying to convince the management that we could better utilize our current server-base if we set up a SAN and went ESX for production (we had ESX for testing already). They were not convinced that the SQL servers would fare well under ESX, so I started researching.

I spoke to a number of vendors (Xiotech, Lefthand, EMC, Equalogic), and all of them wanted to know how many IOPS I needed out of the solution. They then of course went on about how many their particular ware allowed, but I digress... I had no idea how to calculate IOPS (I/O per second) requirements, so it was off to the internet to figure things out. One of the vendors offered to decode perfmon logs for me, but I really wanted to know how to do that myself. Besides being a handy skillset to have, it meant much less time spent waiting for others.

I'll have a list of links at the end of this post, as a lot of the info I gleaned was from multiple sources, all quite good in their own respect.

Here goes, and please note that this spec is for an SMB with around 100 users.

First of all - perfmon. It is not advisable to use readings on the fly - set a capture to run with all your desired counters and let it go for a business week (depending on the number of counters, you may need to allow perfmon to use the maximum size of the logs - you can also set a custom size - 4095MB is the maximum).



Exchange calculations
Pulled off Technet, the basics were this:
Based on a heavy user - 60sent/150received,500+MB mailbox), you get approximately 1.5IOPS. Take the number of mailboxes you have (I used a high number of 250 - we'll have around 200 mailboxes when all is said and done) and multiply by the IOPS, so 375IOPS is what we need to have Exchange running correctly with room to grow. Again, my calcs are pretty generous.


SQL calculations
This is a deep, deep pool to wade into...but I really had no other choice.

Basically, I needed to know what our current usage was, because if it is more than the SAN will be able to handle, then it'll clearly be a fruitless effort. (I will also use SQLIO to get our max throughput for a benchmark - thanks D!)

The essentials of a properly running SQL server are:

Hardware setup
  • Dual quads, 8-16GB RAM, 64bit OS (keep in mind that even if you have 16GB of RAM, and you allow SQL to use 15GB of it, then wonder why you have less than 1GB left over...it's because SQL will use as much RAM as you allow it to have - the basis being that it's faster to page to RAM than disk.)
  • Your DBs (databases) are on one array, preferably a RAID1 or RAID10 array with disk caching (pretty sure you can't get a RAID controller these days without battery backed-up caching). This array also has as many spindles as you can spare (for our CRM4.0 rollout the consultants specced 8x146GB 15k).
  • Your transaction logs are on another array, again preferably RAID1 or RAID10 with four disks as above specced.
  • There is a temp array for DB copying, backups, etc, same spec as the logs array.
With that number of disks (8DB,4LOGS,4TEMP,2OS), you're really into a shelf at that point, or some sort of SAN. Also, the reasoning behind RAID1 or RAID10 is with those RAID levels you get more reads, and since SQL is read-heavy you then have better performance.

Performance counters to note
This was all noted right off a series of videos hosted by techtarget.com. I'll note them Counter - Instance, if applicable -notes.

Paging - Total use % - Should be less than 70%.
Paging - Peak use % - Same as above, should also be fairly constant.

Processor - % Processor time - All - Should be 80% or less.
If you're troubleshooting the above:
Processor - % Privilged time - All - Should be less than 30% of total % processor time.
Processor - % User time - All - Should equal (100% - % Priv. time).

Memory - Pages input/sec - Should be less than 10.
Memory - Pages/sec - It's okay if this is around 500-600 or less.

SQLServer:Access Methods
- Index searches/sec
- Full scans/sec
If you divide (Index searches/sec) by (Full scans/sec), the number you get should be less than 1000.
- Batch requests/sec
- Forwarded requests/sec - Should be less than 10 per 100 batch requests.
- Page splits/sec - Should be less than 20 per 100 batch requests.

SQLServer:Buffer Manager
- Buffer cache hit ratio - Should be 95% or higher.
- Free list stalls/sec - Should be less than 2.
- Free pages - Should be at least 640, preferably more.
- Lazy writes/sec - Should be less than 20.
- Page life expectancy - Should be at least 300.
- Page lookups/sec - Divide (page lookups/sec) by (batch requests/sec) and it should be under 100.
- Page reads/sec & Page writes/sec - Should be less than 90. If not, could be your indexes or memory constraints.

You get the point. There's a lot to consider.

The consultant we have in has suggested that to get a clearer idea we should get max benchmarks using SQLIO. On the disk array with 6x300GB 15k SAS in RAID5 we got around 1400IOPS max when doing random read/write of different sizes.


File/print/other

This can be considered to be a fairly small part of the equation, unless you add VM access, but that's another category all-together.



I'll post more as I think of it.



Links
http://technet.microsoft.com/en-us/library/bb125019(EXCHG.65).aspx
http://searchsqlserver.techtarget.com/video/0,297151,sid87_gci1347899,00.html


Comments

Popular posts from this blog

DFSR - eventid 4312 - replication just won't work

This warning isn't documented that well on the googles, so here's some google fodder:


You are trying to set up replication for a DFS folder (no existing replication)Source server is 2008R2, 'branch office' server is 2012R2 (I'm moving all our infra to 2012R2)You have no issues getting replication configuredYou see the DFSR folders get created on the other end, but nothing stagesFinally you get EventID 4312:
The DFS Replication service failed to get folder information when walking the file system on a journal wrap or loss recovery due to repeated sharing violations encountered on a folder. The service cannot replicate the folder and files in that folder until the sharing violation is resolved.  Additional Information:  Folder: F:\Users$\user.name\Desktop\Random Folder Name\  Replicated Folder Root: F:\Users$  File ID: {00000000-0000-0000-0000-000000000000}-v0  Replicated Folder Name: Users  Replicated Folder ID: 33F0449D-5E67-4DA1-99AC-681B5BACC7E5  Replication Group…

Fixing duplicate SPNs (service principal name)

This is a pretty handy thing to know:

SPNs are used when a specific service/daemon uses Kerberos to authenticate against AD. They map a specific service, port, and object together with this convention: class/host:port/name

If you use a computer object to auth (such as local service):
MSSQLSVC/tor-sql-01.domain.local:1433

If you use a user object to auth (such as a service account, or admin account):
MSSQLSVC/username:1433

Why do we care about duplicate SPNs? If you have two entries trying to auth using the same Kerberos ticket (I think that's right...), they will conflict, and cause errors and service failures.

To check for duplicate SPNs:
The command "setspn.exe -X

C:\Windows\system32>setspn -X
Processing entry 7
MSSQLSvc/server1.company.local:1433 is registered on these accounts:
CN=SERVER1,OU=servers,OU=resources,DC=company,DC=local
CN=SQL Admin,OU=service accounts,OU=resources,DC=company,DC=local

found 1 groups of duplicate SPNs. (truncated/sanitized)

Note that y…

Logstash to Nagios - alerting based on Windows Event ID

This took way longer than it should have to get going...so here's a config and brain dump...

Why?
You want to have a central place to analyze Windows Event/IIS/local application logs, alert off specific events, alert off specific situations.  You don't have the budget for a boxed solution.  You want pretty graphs.  You don't particularly care about individual server states.  (see rationale below - although you certainly have all the tools here to care, I haven't provided that configuration)

How?
ELK stack, OMD, NXlog agent, and Rsyslog.  The premise here is as follows:

Event generated on server into EventLogNXlog ships to Logstash inputLogstash filter adds fields and tags to specified eventsLogstash output sends to a passive Nagios service via the Nagios NSCA outputThe passive service on Nagios (Check_MK c/o OMD) does its thing w. alerting
OMD
Open Monitoring Distribution, but the real point here is Check_MK (IIRC Icinga uses this...).  It makes Nagios easy to use and main…