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.

Hello World

I just found the first cute little bit of SQL I ever wrote at work, on the 6th of October 2004;

USE Scott_Test
DECLARE @TestNum INT
   SET @TestNum = 1047
SELECT SHID_ID, SHID_Complete, SHID_SMPNo, SHID_Date, SHID_TotalShares, SHID_PortionAnalyzed,
         SHID_complete, SHID_NoExtracts, SHID_EnteredBy, SHID_ReportSentDate,
         SHID_ReportSentBy, SHID_Stage 
   FROM Shid 
   WHERE SHID_COID = @TestNum 
      AND SHID_Stage <> 0
   ORDER BY SHID_ID DESC
SELECT SHID_ID, SHID_Date, SHID_InternalDeadLine, SHID_CompletionDate, SHID_Benchmark,
         SHID_NoExtracts, Shid_Comment 
   FROM Shid 
   WHERE SHID_COID = @TestNum 
      AND SHID_Stage = 0
   ORDER BY SHID_ID ASC
         

SQL Nexus Tool

Amazingly, even in SQL 2008 there's no performance testing/tuning GUI. But of course, you just need to know where to look to find free tools. Welcome to Codeplex.

SQL Nexus takes advantage of the SQLDiag command line tool that ships with SQL Server, sucking in DMV and Trace data to present in a great drill-through Reporting Services based interface.

Within an hour, I'd identified 2 hardware based issues with solid evidence to present to management.

Tip: Read the installation steps carefully; it's not an all-in-one install. Also, when directed towards a download of PerfStatsScript.zip, look here for the download.

Measuring Performance of Stored Procedures

Lately I've been faced with dual problems: how to spot which stored procs are performing the worst within an application which has been released, and secondly, how to help developers write code that runs quickly before deploying it to production.


Of course you can run Traces, or have a look into System Views, but knowing the how is a lot different to knowing the whiches, whens and whys. For example, which trace events do you enable?


This article by Preethiviraj Kulasingham at SQL Server Universe set off a few light bulbs for me.