Skip to content

SqlWorkload

Gianluca Sartori edited this page Dec 8, 2018 · 14 revisions

SqlWorkload

SqlWorkload is a command line tool to start workload collection, analyze the collected data and replay the workload to a target machine.

The main concepts in SqlWorkload are two: Listeners and Consumers.

Listeners allow SqlWorkload to capture workload events from a source and publish them to a list of consumers. An instance of SqlWorkload can make use of a single listener, but it can publish the events to multiple consumers at the same time.

SqlWorkload Architecture

Listener types

WorkloadTools includes listeners to collect workload events from a number of sources, depending on your needs. You have listeners to collect events from SqlTrace, Extended Events or from a saved workload file.

Here is the complete list:

  • ExtendedEventsWorkloadListener: can capture events from an extended events session, using the streaming API or reading the .XEL files in the output folder in an incremental fashion. This is the recommended listener for capturing workloads from a live SQL Server instance.

  • FileWorkloadListener: reads workload events from a saved workload file

  • ProfilerWorkloadListener: can capture events from a SQL Server instance by leveraging the profiler API from Microsoft. This listener only works in the 32 bit implementation (the API is only available through a 32 bit DLL) and can have a severe performance impact on the server.

  • SqlTraceWorkloadListener: captures the workload events by reading the rollover files of a server-side SqlTrace in an incremental fashion. This is the safer and more performant way to work with SqlTrace in older versions of SqlServer

All workload listeners also capture additional metrics about the source system (live SQL Server instance or workload file) and inject these metrics in the workload, in order to better describe the state of the system being observed. These are the metrics collected:

  • cpu usage
  • wait statistics

Consumer Types

The workload events captured by the listener are then passed to all the consumers registered to it. The consumers are designed to implement the typical activities that take place during a benchmarking session: saving the workload to a file for later replay, analyzing the workload, replaying the events to a target SQL Server instance (on-premises or in the cloud).

These are the available consumer types:

  • AnalysisConsumer: processes the events to extract information about the workload. Can “normalize” the batches (strips away parameters and constants) and calculates execution metrics like cpu time, duration, reads and writes. The data is then available for display or for comparison with another analyzed workload.

  • ReplayConsumer: executes all the batches from the captured workload to a target machine

  • WorkloadFileWriterConsumer: writes all the events to a target file, so that it can be later used as a source for a replay or for workload analysis.

Clone this wiki locally