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.  

Add new comment