PerformanceCollector Part 2: How It Works

This is Part 2 of my blog series on PerformanceCollector.  This utility will help you monitor and baseline the performance of your SQL Server.  You can download the source code here. It is totally open source and there is no copyright.  It follows the Oracle YAPP method of performance monitoring where we monitor our response times and try to improve them by eliminating unnecessary waiting on the server.  

Overview

PerformanceCollector looks at various DMVs and at given intervals takes a snapshot of the most interesting DMV data that you can then query later.  It is implemented as two SQL Agent jobs.  

1. PerformanceCollector BlockAndWaitMonitor:  runs in a "WHILE 1=1" loop and will rerun every 5 mins if it is stopped. Within the loop, every 15 seconds (configurable in PerformanceCollector.Config) it will collect and blocking and waiting data to PerformanceCollector.BlockAndWaitMonitor.
2. PerformanceCollector.RunAddIns: runs every 5 mins to determine what AddIns are scheduled to run. This is discussed more in a future blog post:  [[PerformanceCollector AddIns]].  The AddIns capture data at less frequent intervals.  You can write your own AddIns as needed.  

Installation

Initial deployment and upgrades are handled by the same Powershell script: Deploy-PerformanceCollector.ps1. There is one parameter: $servername. It assumes you are running as an NT acct with sysadmin permissions.  Simply download the code and execute it like the screenshot.  

It will install a new database for your instance called PerformanceCollector that contains all of the code.  It will also create the new SQL Agent jobs noted above.    The installer only takes a few seconds to create or upgrade the utility.  The installer uses a variant of my MD3 (Metadata Driven Database Deployments) utility to install or upgrade a database via scripts.  

In the next blog post in this series I'll cover the BlockAndWaitMonitor which is the core component of the tool and the go-to object when I am presented with a performance problem on a production system.  

You have just read "PerformanceCollector Part2: How It Works" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.