DaveWentzel.com All Things Data
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
- blocking and waiting data
- index utilization
- free space/used space statistics
- buffer cache utilization
- interesting query plans
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.