DaveWentzel.com            All Things Data

Performance Engineer-in-a-Box

UPDATE:  I have since revised this code and have better documentation as well as version control.  Please see my PerformanceCollector tool.  

 

Years ago I wrote a little utility called DBA-in-a-Box.  If you needed to administer a large and growing installation of SQL Servers you needed a quick way to make sure that every new server had a scheduled backup job, reindexing job, and DBCC CHECKDB job.  In one quick script you could deploy all of these things.  

When I first became a performance engineer I found that I had to quickly run a series of scripts on potentially new servers to determine if they were configured correctly, gather performance metrics, start SQL Traces, etc.  I never bothered to publish those scripts because they required some manual intervention.  I was recently tasked with creating a utility that could be deployed en masse that would monitor

  • deadlocks
  • blocking and waiting data
  • index utilization
  • free space/used space statistics
  • buffer cache utilization
  • interesting query plans
  • etc

I call this utility the Performance-Engineer-in-a-box.  It is configurable via a metadata table that controls two jobs, a WaitAndBlock Inspector that runs every 15 seconds (by default) and another job that runs a series of user-configurable add-ins. 

It is deployed via a PoSH script.  Within a few minutes of execution you can quickly determine where your performance bottlenecks reside.  Our entire team has begun using this as the first step in troubleshooting performance issues.