PerformanceCollector: a performance monitoring tool for SQL Server

About 15 years ago I wrote a suite of performance monitoring scripts for SQL Server 7 that would try to tell you why a given query was running slowly.  Back in those days there was no concept of DMVs to help you out.  Most people I knew relied solely on sqltrace (Profiler).  The problem is, Profiler will tell you how long something will run, but it won't tell you what subsystem of SQL Server is causing those long runtimes.  Is it a memory issue?  Slow IO?  Blocking?  The only information available was sysprocesses.  And it contained excellent information.  

I came from an Oracle background and in those days (Oracle8) there was no equivalent to Profiler.  If you wanted to look at your performance metrics you needed to query the sys and system schemas and the v$ views.  These are functionally equivalent to DMVs.  In fact, the folks at Oracle published a bunch of whitepapers on using these DMV-like tools and called it YAPP (Yet Another Performance Profiling Methodology).  YAPP was quite simply tuning based on the subsystems that caused long response times.  

I never realized how simple and elegant this approach to performance management was until I started working with SQL Server 6.5.  Other than sysprocesses, there was nothing else to tell you where the bottleneck was.  sqltrace was great for baselining but given a big query that had sporadic performance problems, where do you look next?  I actually started to do some independent learning about [[Performance Management Home| Performance Management]] that I began to document.  

But how could I do YAPP in SQL Server which had no publically-exposed wait interface?  Well, it turns out it did.  sysprocesses would show you the waittype, waittime, and waitresource for any spid that is waiting on some subsystem.  

I actually started this website in 1997 partially to document my findings with sysprocesses and ways to do YAPP-like analysis.  [[Subsystem Information Home]], which now has lots of information on DMVs and response time tuning techniques, was initially a series of static pages on interested, undocumented things I found.  Remember, there was no google yet.  

I began to see more performance engineers and DBAs relying on sysprocesses as their tool of choice for performance troubleshooting.  Slowly DBAs started complaining to M$ to expose even more information in sysprocesses and give us something more YAPP-like.  With the release of SQL Server 2005 we were given our first DMVs.  I was thrilled.  But there were so many DMVs and they were still not entirely documented.  I created a new section of my website, [[DMV Info]] where I started to document what these DMVs did and how they helped in response time and wait analysis.  

I certainly wasn't the only DBA doing this, nor even the best.  In the last 10 years a complete shift in how SQL Server DBAs do performance engineering has occurred.  Nowadays I don't see anyone using Profiler and everyone uses the DMVs.  How the times have changed.  

Anyway, my first attempt at a performance monitoring and baselining solution was a SQL Executive (now known as SQL Agent) job that merely took a snapshot of sysprocesses and squirrelled it away so I could look at it later when someone complained about poor performance.  It was invaluable.  I rewrote it with the advent of DMVs.  Slowly over time I was monitoring even more "stuff" as every new release of SQL Server gave us new and exciting DMVs.  

I found this so useful I was installing it at every client engagement.  I decided to release a version of it a few years ago and posted it on my blog under the name [[Performance Engineer-in-a-Box]].  I've since installed it at even more client sites and I finally got around to properly releasing this in a manner where I can do controlled releases as needed.  I now call the tool "PerformanceCollector".  The source code is available at my GitLab installation and is open to the public.  Feel free to modify it, fork it, steal it, change it, or whatever you would like.  I really don't care and their is no form of copyright on it.  I would appreciate an attribution if you do use it, but no payment is necessary.  

Over the next few blog posts I'll cover in details what PerformanceCollector does and how it works.  

You have just read "PerformanceCollector: a performance monitoring tool for SQL Server" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.