SQL Ninja, Hardware Concerns

It turns out that the title of my blog is also the name of a SQL injection attack generator program! I knew it was too good to be true, but did I bother Googling it first? No, that'd be like reading the manual.. 

Ah well, moving on with life.

Today's problem: you've got to provide a report on the state of health of your server hardware, and you've got to provide it quickly. 

Quickest way? The Real Time server stats in SQL Nexus will give you a look at the server performance as a current snapshot.

Time to hit PerfMon direct instead if you need to see more in-depth and chart it out over time, like say over the next hour. But what to monitor?

Measuring a database server's performance (as opposed to the performance of the database code and objects), is really down to looking at a few parts: the CPU, the disk system, & the RAM.

For the CPU: enable Processor - % Processor Time and System - Processor Queue Length. The lower for both of these the better, hopefully less that 50% for the former and less than 2 per CPU for the latter.

For the disk system: % Disk Time (aim for average less than 50%), Average disk queue (like the CPU, less than 2 per disk is good), average writes per second and average reads per second (both of these depend on the disk capacity, against which you'll need to compare and hopefully you'll be using less than 85%). 

For RAM: Memory - pages per sec (lower the better, less than 20 preferably) and Available Bytes (at least 10% of the total RAM). With a grain of salt taken, SQL Server Buffer Mgr - Buffer Cache Hit Ratio should be at least 99%, and the Page Life Expectancy should be above 300 seconds. I mention the grain of salt, as coding quality can affect these counters, an apparent lack of RAM may really be a case for proper indexing or another DB optimisation problem in disguise. On the other hand, RAM may be cheap relative to development costs..

Set these counters up, and save a log, polling every 15 seconds or less if there are performance problems (and be aware that running PerfMon will cause its own slight performance hit, as will saving the logs on the local disk if you have to). The log can be reloaded into PerfMon to see averages, maxes and mins and charting of the performance over the monitoring time. 

These counters aren't the be all and end all, but should be enough to spot if there's a problem in one or more of the main systems, and suggest how the server can be beefed up if necessary.