Introduction

Kurt Vonnegut spoke at my university in 1988.  He said that the best way to engage your readers was to throw away the first 10 pages of your manuscript.  He said those pages were almost always useless, boring fluff.  So let us dive right in.

How hard it is to diagnose a SQL Server performance issue depends somewhat on the immediacy of the problem.  Did you receive a phone call about a problem happening right now?  Or did one of the salespeople in your company tell you about a demo earlier in the day where the application was abnormally slow?  It is easier to find the cause of the problem if it is happening now or can easily be reproduced since there is an opportunity to start up database monitors to help spot the problem. 

Without some type of tool or database monitor, the only recourse is to sift through the default performance data collected by SQL Server.  That is problematic because the performance data collected is aggregated and typically includes months of data, perhaps  back to when SQL Server was last (re)started.  The default view of the data does not include an easy way of knowing what happened during any particular hour of any day.   Look for posts about the Data Management View (DMV) dm_exec_query_stats to see this aggregated data.  By the time you see the data, Murphy's law has had lots of time (perhaps months) to manifest itself over and over again.  Reading the output from dm_exec_query_stats, it is hard to know which, of all the things that went wrong , caused someone to complain to you about slowness.

In this blog, I will start with monitors that show a more immediate view of what is happening on the database and work towards unattended, long running monitors that can reveal what happened on a particular moment on any day.  I chose that order because unattended monitors take much longer to set up.