Advanced Usage Scenarios

Non-Interactive Data Collection:  SQL Workload Collector

Introduction

Workload Profiler was originally created for desktop use for quickly analysing the performance of a workload running inside SQL Server.

However, it may also be useful to run a lighter version of the tool, non-interactively, purely for the purposes of data collection e.g. writing the data into a different SQL Server instance.  The saved performance data can then be analysed later by loading it into the desktop client.

To that end, a console version of the tool is available that performs only the data collection function.

2015042901-CmdLine

Instructions

  1. Download Workload Collector (link below) and unzip.
  2. Configure Workload Collector by setting the relevant values in the config file.  By design, Workload Collector supports exactly the same config file as Workload Profiler, so the simplest approach is to test your configuration settings in the Workload Profiler, then copy the Workload Profiler config file and rename it to SqlWorkloadCollector.exe.config, replacing the existing Workload Collector config file.
  3. Run the tool either from the command line or simply by double clicking on it.  Additional options are described below.

Download

As ever, all tools downloaded from the internet should be checked and tested on non-production systems first.

  • Current Version: 0.9.0 (i.e. a beta version)
  • Size: 0.2 MB
  • First Released: 29th April 2015
  • Expiry Date: 1st January 2018
  • Download Link: Download

Command Line Options

As with the full Workload Profiler, most configuration values are specified inside the configuration file.  There are however two specific command line options:

  • /QuitInMinutes:n – specifies to collect data for minutes then automatically exit.
    Example:  /QuitInMinutes:15
  • /QuitEventName:name – specifies the event wait handle name to use – described in more detail below.
    Example:  /QuitEventName:SqlColExit

Ending Data Collection

Workload Collector runs without any user interaction.  Indeed, it is not possible to interact with Workload Collector (such as by a console prompt).  There are however three different ways that can be used to control when Workload Collector finishes.

Option 1:  Command Line Option:  /QuitInMinutes:n

The simplest option is simply to specify on the command line the duration that data collection should run for.

Option 2:  Use a .NET EventWaitHandle

The .NET platform supports the EventWaitHandle class as a simple mechanism for cross-thread or cross-process synchronisation.  This allows a process external to Workload Collector to signal that data collection should be terminated.  The code to accomplish this (which would sit in an external application) is as simple as:

EventWaitHandle oWaitHandle = null;
bool bOpenWaitHandle = EventWaitHandle.TryOpenExisting(sWaitHandleName, out oWaitHandle);
if (oWaitHandle == null || (!bOpenWaitHandle)) 
    oWaitHandle = new EventWaitHandle(false, EventResetMode.ManualReset, sWaitHandleName);
if (oWaitHandle == null) 
    throw new ApplicationException("Unable to create or open event wait handle.");
oWaitHandle.Set();

Here, sWaitHandleName is a string specifying the name of the wait handle to use.  By default, Workload Collector will use a wait handle named “SqlCollectorExit”.  However, should an alternative name be desired, then it can be specified on the command line when starting Workload Collector.

An example application, including source code, demonstrating the use of the event wait handle can be downloaded here.

2015042902-QuitDemo

To use this demo app:

  1. Start Workload Collector as described above.
  2. Run the Quit Demo app.  The Wait Handle Status will show “n/a” – i.e. the wait handle does not exist yet.
  3. Click “Create Event Wait Handle”.  It will be created, but not set (think of this as a boolean value of false).  Data collection should continue.
  4. To instruct Workload Collector to finish, click “Set Event Wait Handle”.  This will set/signal the Wait Handle (set it to true).  Workload Collector should soon notice this and cleanly finish (it may take a few moments if a data collection is currently in progress).
  5. Click “Clear Event Wait Handle” to reset it back to false.
  6. When all applications referencing the Wait Handle exit (so in this case the Quit Demo app and Workload Collector), the handle is automatically deleted by the CLR/Windows.
Option 3:  Process.Kill()

A quick and dirty way is to use the Kill() method on the .NET System.Diagnostics.Process class to abruptly terminate data collection.

Configuration Reference

By design, Workload Collector supports exactly the same config file as Workload Profiler, so the simplest approach is to test your configuration settings in the Workload Profiler config file, then copy that file and rename it to SqlWorkloadCollector.exe.config, replacing the existing Workload Collector config file.

Workload Collector supports only those Workload Profiler configuration settings that relate to data collection (any other settings in the file are ignored).  These are:

  • MonitorConStr
  • DataCollectionMethod
  • WritingEnabled
  • WriteToConStr
  • WriteToTableName
  • AutomaticDataCollection
  • CollectionIntervalSeconds
  • ProcessingEnabled
  • ProcessingSqlFilePath
  • MaxStatsPerCollection
  • MaxStatsTotal
  • MaxSqlTextLengthRead
  • MaxSqlTextLengthWrite
  • DataCollectionFilter_DatabaseName
  • DataCollectionFilter_ProcedureOrBatchText
  • DataCollectionFilter_QueryText
  • Adv_HideDataCollectionQueryInStats
  • UseCustomCollectionSQL
  • Adv_SimpleCollectionCustomSQLFilePath
  • Adv_ComplexCollectionCustomSQLFilePath1
  • Adv_ComplexCollectionCustomSQLFilePath2
  • Adv_ComplexCollectionCustomSQLFilePath3

Please see the Workload Profiler pages for descriptions of these settings.

Running From a Web Application

One potential usage scenario is starting / stopping data collection from a web browser (e.g. running the tool on a small VM in Azure separate from the main Azure VM(s) running your main SQL Server workload(s)).

A (quickly created!) sample project can be downloaded here that demonstrates one way the tool can be controlled from a .NET Web Forms project.

2015042903-QuitDemo

This project is not intended to be a complete solution – it is only a very basic demonstrator.  Some potential/obvious enhancements include:

  • Securing the website so that only authorised individuals can control data collection,
  • Moving various settings into a config file (such as the path to the Workload Collector executable file),
  • Writing the Workload Collector output to a file instead of the in-memory class used in the sample (which will lose any captured output if/when the Web Application restarts),
  • Potentially supporting multiple instances of the Workload Collector to allow data to be pulled from different servers using a single monitoring website.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s