Expansion Diary Part 2
So, here I am hip deep in the Performance Monitor.... Trying to get an idea of what a normal day looks like from the server's point of view. This stuff is SO interesting. Makes me wonder how it is that I do this job so well without ever having had to look at these numbers before. Must be luck.
I'm going through the book: SQL Server Query Performance Tuning Distilled, by Sajal Dam. Pretty good so far. I'm trying to use his guidance to know what numbers are "good" or "bad" when monitoring performance. And to have clues about what each number really means in terms of my bad TSQL all over the place :)
Here's a sampling of what I'm finding:
Memory: I've got plenty of RAM :) My Available MB is great (avg 1524), my Buffer Hit Cache Ratio is 99, plenty of Free Pages, and no Memory Grants Pending :) However, I'm finding an average of 114 Pages/sec, with a max of 1770. Apparently that's bad, and means I'm going to disk too often. I'm wondering, if my Buffer Hit Cache Ratio is so high, why am I going to disk so often? Only the Shadow knows....
Disk: Strange :) My average % Disk Time is 3.5 which seems great, but with a max of 137. How can you have 137%? My Avg Disk Queue Length is 0, with a max of 3, Disk Transfers/sec average is only 20, but with a max of 270 which is quite high. Seems to go along with the findings in Memory too...
CPU: Just like Memory, this is strange to me so far. It seems good at first, with average % Processor Time of 18, maxing out at 56, average % Privileged Time at 3, maxing out at 11. Processor Queue Length at 0, maxing at 8. But I have way too many Context Switches/Sec, averaging at 6335 and maxing at 9481. Not sure how this can be. Nothing else is running on this machine but SQL Server.
Overall: These are the ones that the book doesn't tell you which numbers are good versus bad. It just leads you to believe that less is better :) FreeSpace Scans/Sec: 382, maxing at 4008. Wow, I didn't know I had heaps! I'll find and fix THAT :) My Lock Timeouts/Sec average at 0 but spiked up to 55. Lock Wait Time averages 180, maxing out at a huge 19933! One super long lock? Wow!
Statistics: The server averages 13 User Connections, maxing at 34. With 53 Batch Requests/Sec average, and 407 max.
What does all this mean? Heck if I know. I bet it basically means I've got some way dumb TSQL somewhere :) One thing's for sure: as I find out, I'll tell it here.