Server Performance Advisor Pack Development Guide

Updated: October 30, 2013

This development guide for Microsoft Server Performance Advisor (SPA) provides guidelines to help developers and system administrators develop advisor packs to analyze server performance.

It assumes you are familiar with Performance Logs and Alerts (PLA), performance counters, registry settings, Windows Management Instrumentation (WMI), Event Tracing for Windows (ETW), and Transact SQL (T-SQL).

This guide applies to the following versions of Windows Server:

  • Windows Server 2012 R2

  • Windows Server 2012

  • Windows Server 2008 R2

  • Windows Server 2008

This guide applies to the following versions of Windows:

  • Windows 8.1

  • Windows 8

  • Windows 7

For more info about using SPA, see Server Performance Advisor User's Guide.

An advisor pack is typically designed for a particular server role, and it defines the following:

  • Data to be collected through PLA, including Windows Management Instrumentation (WMI), performance counters, registry settings, files, and Event Tracing for Windows (ETW)

  • Rules, which shows alerts and recommendations

  • Data to be shown (collected raw data, aggregated data, or top 10 lists)

  • Statistics to view a value that changes over the time

  • Statistics values that can be trended

An advisor pack includes the following elements:

  • XML metadata (ProvisionMetadata.xml)

  • SQL scripts

    • A main stored procedure

    • SQL objects, such as stored procedures and user-defined functions

  • ETW schema file (—this is optional

Advisor pack workflow

In this flowchart, the green circles represent advisor packs. All other circles represent the phases that are running in the process of the SPA framework. SPA uses an advisor pack to collect data, import the data into the database, initialize execution environment, and run SQL scripts.

When an advisor pack is queued for a particular server by using SPA, the data collection module queries the data collector set XML from the advisor pack and collects data from the target server. The raw data is stored in a user-specified file share. The data collection will not stop until the SPA run duration designated by the user is exceeded.

After the data collection is completed, each type of data is imported into a corresponding table in the SQL Server database. For example, registry settings are imported into a table called #RegistryKeys.

Importing ETW file requires an ETW schema file for decoding the .etl file. The ETW schema file is an XML file. It can be generated by using tracerpt.exe, which is included with Windows. The ETW schema file is only required when the advisor pack needs to import ETW data.

The SPA framework automatically adjusts privileges to minimize the required security access level. Because advisor packs can be developed or modified by anyone, it is possible for an advisor pack to contain tampered SQL scripts. To mitigate the security risk, any SQL script for an advisor pack should be run with low user rights. It can only access limited database objects, such as temporary tables and SPA APIs exposed as stored procedures. The SQL scripts in an advisor pack can call those store procedures to interact with the SPA framework.

Advisor packs can generate different types of output, such as notifications, recommendations, fact tables, statistics, and charts for statistics. The SQL scripts perform certain calculations against the collected data. The yielded results are stored in temporary tables through SPA public APIs. At the initialization stage, these temporary tables and other system resources need to be provisioned.

There is a main stored procedure, which is named by the advisor pack developer. The SPA framework calls this stored procedure to initiate the calculation. The stored procedure consumes the collected data and communicates the end result to the SPA framework.

Administrator rights are required to generate a report. Report generation is fully controlled by SPA. It is less likely to be tampered with.

Before the main stored procedure completes for an advisor pack, all the calculated results, such as notifications and statistics, are not persisted. During this phase, the SPA framework transfers the end results from temporary tables to tables in a particular format. After this phase is complete, you can view the reports by using the SPA console.

The following flowchart describes the steps for you to develop a fully functional advisor pack. This section also includes step-by-step examples to better explain each step.

Advisor pack development process

An advisor pack is usually structured in the following way:

  • Advisor pack

    • ProvisionMetadata.xml

    • Scripts

      • main.sql

      • func.sql


Every advisor pack must have a file called ProvisionMetadata.xml. It defines basic advisor pack information, what data to collect, notifications and rules, and how the report needs to be stored and displayed. The SPA framework uses this information to generate a temporary table and then transfer the results in the temporary table into a table that users can access.

All report SQL scripts must be saved in a subfolder called Scripts. For maintenance purposes, we recommend that you save different database objects in different SQL Server files. There must be at least one stored procedure as a main entry point.

The file is not required unless your advisor pack collects ETW traces. This schema file is used to describe the schema of the ETW events and to decode ETW events.

This section describes some of the basic elements that make up an advisor pack, including ProvisionMetadata.xml and attributes.

The following is an example header for the ProvisionMetadata.xml file:

  displayName="Microsoft CoreOS Advisor Pack V2"
  description="Microsoft CoreOS Advisor Pack"

Attribute name: version

Advisor pack developers can define major and minor versions for the advisor pack:

  • A major version usually involves significant improvements. The results that are generated by an old version might not be compatible with the new one. We strongly recommend including the major version in the advisor pack name.

  • SPA allows minor version upgrades when there are only minor changes with no data incompatibility issues.

For more info about versioning, see Advanced topics.

Attribute name: reportScript

The SPA framework looks for the main stored procedure name from the script entry point and runs it in a secured manner.

Here are some other attributes that can be used to identify an advisor pack:

  • Display name: displayName

  • Description: description

  • Author: author

  • Framework version: frameworkVersion (defaults to 3.0)

  • Minimum operating system version: minOSVersion (this is reserved for future extensibility)

  • Lost event notification: showEventLostWarning

A data collector set defines the performance data that the SPA framework should collect from the target server. It supports registry settings, WMI, performance counters, files from the target server, and ETW.

  <dataSourceDefinition xmlns="">
    <dataCollectorSet duration="10">
        <path>Root\Cimv2:SELECT * FROM Win32_DiskDrive</path>
      <performanceCounters interval="2">
        <performanceCounter>\PhysicalDisk(*)\Avg. Disk sec/Transfer</performanceCounter>
        <provider session="NT Kernel Logger" guid="{9E814AAD-3204-11D2-9A82-006008A86939}" keywordsAny="06010201" keywordsAll="00000000" level="00000000" />

The duration attribute of <dataCollectorSet/> in the previous example defines the duration of data collection (the unit of time is seconds). duration is a required attribute. This setting controls the collection duration that is used by performance counters and ETW.

You can collect registry data from the following registry hives:






To collect a registry setting, specify the full path to the value name: HKEY_LOCAL_MACHINE\MyKey\MyValue

To collect all of the settings under a registry key, specify the full path to the registry key: HKEY_LOCAL_MACHINE\MyKey\

To collect all the values under a registry key and its sub-keys (PLA recursively collects the registry data), use two backslashes for the last path delimiter: HKEY_LOCAL_MACHINE\MyKey\\

To collect registry information from a remote computer, include the computer name at the beginning of the registry path: HKEY_LOCAL_MACHINE\MyKey\MyValue

For example, you may have a registry key that appears as follows:

Windows Registry Editor Version 5.00


“FriendlyName”=”Power Source Optimized”

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Power\User\PowerSchemes\db310065-829b-4671-9647-2261c00e86ef \0012ee47-9041-4b5d-9b77-535fba8b1442\6738e2c4-e8a5-4a42-b16a-e040e769756e

Example 1: Return only the active PowerSchemes and their values:


Example 2: Returns all the key value pairs under this path:

PLA runs under user credentials. Some registry keys require administrative credentials. The enumeration stops when it fails to access any of the sub-keys.


All collected data will be imported into a temporary table called #RegistryKeys before a SQL report script is run. The following table shows the results for example 2:


KeyName KeyTypeId Value








Power Source Optimized







The schema for the #RegistryKeys table is as follows:


Column name SQL data type Description


Nvarchar(300) NOT NULL

Registry key full path name


Smallint NOT NULL

Internal Type Id


Nvarchar(4000) NOT NULL

All the values

The KeyTypeID column can have one of the following types:


ID Type























You can add any WMI query. For more info about writing WMI queries, see WQL (SQL for WMI).The following example queries a page file location:

<path>Root\Cimv2:SELECT * FROM Win32_PageFileUsage</path>

The query in the above example returns one record:


Caption Name PeakUsage




Because WMI returns a table with different columns, when the collected data is imported into a database, SPA performs data normalization and is added to the following tables:

#WMIObjects table


SequenceID Namespace ClassName RelativePath WmiQueryID







#WmiObjectsProperties table


ID Query


Root\Cimv2:SELECT * FROM Win32_PageFileUsage

#WmiQueries table


ID Query


Root\Cimv2:SELECT * FROM Win32_PageFileUsage

#WmiObjects table schema


Column name SQL data type Description



Correlate the row and its properties


Nvarchar(200) NOT NULL

WMI namespace


Nvarchar(200) NOT NULL

WMI class name


Nvarchar(500) NOT NULL

WMI relative path



Correlate the key of #WmiQueries

#WmiObjectProperties table schema


Column name SQL data type Description



Correlate the row and its properties


Nvarchar(1000) NOT NULL

Property name


Nvarchar(4000) NULL

The value of the current property

#WmiQueries table schema


Column name SQL data type Description



Unique query ID


Nvarchar(4000) NOT NULL

Original query string in the provision metadata

Here’s an example of how to collect a performance counter:

<performanceCounters interval="1">
  <performanceCounter>\PhysicalDisk(*)\Avg. Disk sec/Transfer</performanceCounter>

The interval attribute is a required global setting for all performance counters. It defines the interval (the unit of time is seconds) of collecting performance data.

In the previous example, counter \PhysicalDisk(*)\Avg. Disk sec/Transfer will be queried every second.

There could be two instances: _Total and 0 C: D:, and the output could be as follows:


Timestamp CategoryName CounterName Instance value of _Total Instance value of 0 C: D:



Avg. Disk sec/Transfer





Avg. Disk sec/Transfer





Avg. Disk sec/Transfer





Avg. Disk sec/Transfer



To import the data to the database, the data will be normalized into a table called #PerformanceCounters.


CategoryDisplayName InstanceName CounterDisplayName Value



Avg. Disk sec/Transfer



0 C: D:

Avg. Disk sec/Transfer




Avg. Disk sec/Transfer



0 C: D:

Avg. Disk sec/Transfer




Avg. Disk sec/Transfer



0 C: D:

Avg. Disk sec/Transfer




Avg. Disk sec/Transfer



0 C: D:

Avg. Disk sec/Transfer


The localized names, such as CategoryDisplayName and CounterDisplayName, vary based on the display language used on the target server. Avoid using those fields if you want to create a language-neutral advisor pack.

#PerformanceCounters table schema


Column name SQL data type Description


Datetime2(3) NOT NULL

The collected date time in UNC


Nvarchar(200) NOT NULL

Category name


Nvarchar(200) NOT NULL

Localized category name


Nvarchar(200) NULL

Instance name


Nvarchar(200) NOT NULL

Counter name


Nvarchar(200) NOT NULL

Localized counter name



The collected value

The paths can be absolute or relative. The file name can include the wildcard character (*) and the question mark (?). For example, to collect all the files in the temporary folder, you can specify c:\temp\*. The wildcard character applies to files in the specified folder.

If you want to also collect files from the subfolders of the specified folder, use two backslashes for the last folder delimiter, for example, c:\temp\\*.

Here’s an example that queries the applicationHost.config file:


The results can be found in a table called #Files, for example:


QueryPath FullPath ParentPath FileName Content








#Files table schema


Column name SQL data type Description


Nvarchar(300) NOT NULL

Original query statement


Nvarchar(300) NOT NULL

Absolute file path and file name


Nvarchar(300) NOT NULL

File path


Nvarchar(300) NOT NULL

File name


Varbinary(MAX) NULL

File content in binary

After enough data is collected by using PLA from a target server, the advisor pack can use this data for validation, and show a quick summary to the system administrators.

Rules give a quick overview about the server’s performance. They highlight issues and provide recommendations. You can list all the rules that you want to validate for an advisor pack. For example, if you want to develop a core operating system advisor pack, the possible rules could include:

  • Whether the CPU power mode is power saving

  • Whether the server is in a virtualized environment

  • Whether there is disk I/O pressure

Rules contain the following elements:

  • Dependent threshold (a configurable part of a rule)

  • Rule definition (alerts and recommendations)

Here’s an example of a simple rule:

      <threshold …/>
      <rule …/>

Threshold is a configurable factor that enables the system administrators to decide when a rule should show a good or a bad status. The following example shows a rule to detect free space on a system drive and a warning when free space is less than 10 GB.

<threshold name="FreeDiskSize" caption="Free Disk Size (GB)" description="Free Disk Size” value="10" />

However, in this case, the system administrator has a smaller hard drive. He thinks 5 GB of free space might still be a good condition, and he does not want to see a warning. He can update the default value from 10 to 5 through the SPA console without having to understand how to develop an advisor pack.

Introducing a threshold helps system administrators quickly change the value without having to modify the advisor pack.

In the example, all attributes except description are required. You can use any number for value.

A threshold can be shared across the rules.

The rule definition does not involve any logic calculations. It defines how the UI might look and how the SQL Server report script communicates the results to the UI.

A rule has three parts:

  • Alert (rule caption)

  • Recommendation (advice)

  • Associated threshold (optional information about dependencies)

Here’s an example of a rule:

<rule name="FreeDiskSize" caption="Free Disk Size on System Drive" description="This rule checks free disk size on system drive ">
  <advice name="SuccessAdvice" level="Success" message="No issue found.">No Recommendation.</advice>
  <advice name="WarningAdvice" level="Warning" message="Not enough free space on system drive.">
Install OS on larger disk.</advice>
    <threshold ref="FreeDiskSize"/>

You can define as much advice as you want, and you usually would define recommendations. The level of advice can be Success or Warning.

You can link to as many thresholds as you want. You can even link to a threshold that is irrelevant to the current rule. Linking helps the SPA console easily manage thresholds.

The rule name and the recommendations are keys, and they are unique in their scope. No two rules can have the same name, and no two recommendations within one rule can have the same name. These names will be very important when you write an SQL script report. You can call the [dbo].[SetNotification] API to set the rule status.

After the rules are defined, system administrators can see the report summary. However, often system administrators are interested in the aggregated data, and they want to check the data sources that were used in the performance rules.

Continuing with the previous example, the user knows whether there is enough free disk space on the system drive. Users might also be interested in the actual size of the free space. A single value group is used to store and display such results. Multiple single values can be grouped and shown in a table in the SPA console. The table has only two columns, Name and Value, as shown here.


Name Value

Free Disk Size On System Drive (GB)


Total Disk Size Installed (GB)


If a user wants to see a list of all hard drives that are installed on the server and their disk size, we could call a list value, which has three columns and multiple rows, as shown here.


Disk Free disk size (GB) Total size (GB)







In an advisor pack, there could be many tables (single value groups and list value tables). We can use a section to organize and categorize these tables.

In summary, there are three types of UI elements:

Here’s an example that shows the UI elements:

      <dataType .../>
      <section .../>
      <singleValue .../>
      <listValue .../>

A section is purely for the UI layout. It does not participate in any logical calculations. Each single report contains a set of top-level sections that do not have a parent section. The top-level sections are presented as tabs in the report. Sections can have subsections, with a maximum of 10 levels. All the subsections under the top-level sections are presented in expandable areas. A section can contain multiple subsections, single value groups, and list value tables. Single value groups and list value tables are presented as tables.

Here is an example of top-level section.

<section name="CPU" caption="CPU"/>

A section name must be unique. It is used as a key that can be linked to by other sections, single value groups, and list value tables.

The following example has an attribute, parent, and it is pointing to the section CPU. CPUFacts is a child of the section named CPU. parent must refer to a previous section name; otherwise, it can result in a loop.

<section name="CPUFacts" caption="Facts" parent="CPU"/>

The following single-value group has an attribute, section, and it can point to any section, based on your UI design.

<singleValue name="CPUInformation" section="CPUFacts" caption="Physical CPU Information">…</singleValue>

A single value group and a list value table contain different types of data, such as string, int, and float. Because these values are stored in the SQL Server database, you can define an SQL data type for each data property. However, defining an SQL data type is quite complicated. You have to specify the length or precision, which might be prone to change.

To define logical data types, you can use the first child of <reportDefinition/>, which is where you can define a mapping of the SQL data type and your logical type.

The following example defines two data types. One is string and the other is companyCode.

<dataType name="string" = sqlType="nvarchar(4000)" />
<dataType name="companyCode" sqlType="nvarchar(100)" />

A data type name can be any valid string. Here’s a list of allowed SQL data types:

  • bigint

  • binary

  • bit

  • char

  • date

  • datetime

  • datetime2

  • datetimeoffset

  • decimal

  • float

  • int

  • money

  • nchar

  • numeric

  • nvarchar

  • real

  • smalldatetime

  • smallint

  • smallmoney

  • time

  • tinyint

  • uniqueidentifier

  • varbinary

  • varchar

For more info about these SQL data types, see Data Types (Transact-SQL).

A single value group groups multiple single values together to present in a table as shown here.

<singleValue name="SystemOverview" section="SystemOverviewSection" caption="Facts">
  <value name="OsName" type="string" caption="Operating system" description="WMI: Win32_OperatingSystem/Caption"/>
  <value name="OsVersion" type="string" caption="OS version" description="WMI: Win32_OperatingSystem/Version"/>
  <value name="OsLocation" type="string" caption="OS location" description="WMI: Win32_OperatingSystem/SystemDrive"/>

In the previous example, we defined a single value group. It is a child node of the section SystemOverviewSection. This group has single values, which are OsName, OsVersion, and OsLocation.

A single value must have a global unique name attribute. In this example, the global unique name attribute is SystemOverview. The unique name will be used to generate a corresponding view for custom report. Each view contains the prefix vw, such as vwSystemOverview.

Although you can define multiple single value groups, no two single value names can be the same, even if they are in different groups. The single value name is used by the SQL script report to set the value accordingly.

You can define a data type for each single value. The allowed input for type is defined in <dataType/>. The final report could look like this:



Name Value

Operating system

<a value will be set by report script>

OS version

<a value will be set by report script

OS location

<a value will be set by report script>

The caption attribute of <value/> is presented in the first column. Values in the value column are set in the future by the script report through [dbo].[SetSingleValue]. The description attribute of <value/> is shown in a tooltip. Usually the tooltip shows users the source of the data. For more info on tooltips, see Tooltips.

Defining a list value is as the same as defining a table.

<listValue name="NetworkAdapterInformation" section="NetworkIOFacts" caption="Physical network adapter information">
  <column name="NetworkAdapterId" type="string" caption="ID" description="WMI: Win32_NetworkAdapter/DeviceID"/>
  <column name="NetworkAdapterName" type="string" caption="Name" description="WMI: Win32_NetworkAdapter/Name"/>
  <column name="Type" type="string" caption="Type" description="WMI: Win32_NetworkAdapter/AdapterType"/>
  <column name="Speed" type="decimal" caption="Speed (Mbps)" description="WMI: Win32_NetworkAdapter/Speed"/>
  <column name="MACAddress" type="string" caption="MAC address" description="WMI: Win32_NetworkAdapter/MACAddress"/>

The list value name must be globally unique. This name will become the name of a temporary table. In the previous example, the table named #NetworkAdapterInformation will be created at the execution environment initialization stage, which contains all the columns that are described. Similar to a single value name, a list value name is also used as part of custom view name, for instance, vwNetworkAdapterInformation.

@type of <column/> is defined by <dataType/>

The mock UI of the final report could look as follows:

Physical network adapter information


ID Name Type Speed (Mbps) MAC address

The caption attribute of <column/> is shown as a column name, and the description attribute of <column/> is shown as a tooltip for the corresponding column header. Usually the tooltip shows the user the source of the data. For more info, see Tooltips.

In some cases, a table may have a lot of columns and only a few rows, so swapping the columns and rows would make the table look much better. To swap the columns and rows, you can add the following style attribute:

<listValue style="Transpose" …

You can pick any statistics key and view the values in an historical chart or a trend chart. There are two types of statistics:

  • Static statistics A single value, which is known at design time. For example, the free disk space on a system drive would be a static statistic.

  • Dynamic statistics Might be unknown at design time. For example, the average CPU usage of each core is a dynamic statistic because you do not know how many CPU cores could in the system at design time.

The statistics key has a limitation that the data must be compatible with double data type. It can be an integer, decimal, or a string that can be converted to double.

SPA uses a single value group to support static statistics and a list value table to support dynamic statistics. The following sections describe how to define static statistic and dynamic statistic keys.

As mentioned previously, a static statistic is a single value. Logically, any single value could be defined as a static statistic. However, it is meaningless to view a single value that cannot be cast to a number type. To define a static statistic, you can simply add the attribute trendable to the corresponding single value key as shown below:

<value name="FreeDiskSize" type="int" trendable="true" …

Dynamic statistic keys are not known at design time, so the number of possible values is unknown. However, because list values are stored in multiple rows, it would be easy to use a list value table to store dynamic statistics.

For example, if we need to show charts for the average CPU usage of different cores, we could define a table with columns for CpuId and AverageCpuUsage:

<listValue name="CpuPerformance">
  <column name="CpuId" type="string" caption="CPU ID" columnType="Key"/>
  <column name="AverageCpuUsage" type="decimal" caption="Average" columnType="Value"/>

Another attribute, columnType, can be Key, Value, or Informational. The data type of the Key column must be double or double convertible. In a Key column, you cannot insert the same keys into a table. Value or Informational columns do not have this limitation.

The statistics values are stored in Value columns.

Informational columns are like ordinary columns in normal list value tables. Informational is the default column type if you do not specify one. Such columns will not affect the number of statistics keys or participate in statistics-related calculations.

Continuing with the previous example, if a server has two CPU cores, the result in the table could look like this:


CpuId AverageCpuUsage





At the same time, two statistics keys are generated by the SPA framework. One is for CPU 0 and the other is for CPU 1.

As the following example indicates multiple Value columns with multiple Key columns is supported.


CounterName InstanceName Average Sum

% Processor Time




% Processor Time




In this example, you have two Key columns and two Value columns. SPA generates two statistics keys for the Average column and another two keys for the Sum column. The statistics keys are:

  • CounterName (% Processor Time) / InstanceName (_Total) / Average

  • CounterName (% Processor Time) / InstanceName (CPU0) / Average

  • CounterName (% Processor Time) / InstanceName (_Total) / Sum

  • CounterName (% Processor Time) / InstanceName (CPU0) / Sum

CounterName and InstanceName are combined as one key. The combined key cannot have any duplication.

SPA generates many statistics keys. Some of them might not be interesting to you, and you may want to hide them from the UI. SPA enables developers to create a filter to show only useful statistics keys.

For the previous example, the system administrators may only be interested in keys in which the InstanceName is _Total or CPU1. The filter can be defined as follows:

<listValue name="CpuPerformance">
  <column name="CounterName" type="string" columnType="Key"/>
  <column name="InstanceName" type="string" columnType="Key">
  <column name="Average" type="decimal" columnType="Value"/>
  <column name="Sum" type="decimal" columnType="Value"/>

<trendableKeyValues/> can be defined under any Key column. If more than one Key column has such a filter configured, AND logic will be applied.

After the provision metadata is defined, we can start to write the report script, which is a T-SQL stored procedure.

There are name and reportScript attributes in the provision metadata header, as shown here:

<advisorPack name="Microsoft.ServerPerformanceAdvisor.CoreOS.V1" reportScript="ReportScript" …

The main report script is named by combining the name and reportScript attributes. In the following example, it will be [Microsoft.ServerPerformanceAdvisor.CoreOS.V2].[ReportScript].

CREATE PROCEDURE [Microsoft.ServerPerformanceAdvisor.CoreOS.V2].[ReportScript] AS SET NOCOUNT ON

-- Set alert and notification

-- Prepare data for report view

The name attribute will be used as a database schema name, such as a namespace. This rule applies to all other database objects that belong to the current advisor pack, such as list value and stored procedures.

Benefits to having this schema name in front of the database objects include:

  • Avoiding naming conflict for different advisor packs

  • Providing greater security

In the SQL Server database, the default schema name is dbo. Database owner credentials are usually required to operate database objects under dbo. If we do not create a schema for each advisor pack, it is likely that two advisor packs will define a list value with the same name. This should be irrelevant because you can introduce a schema name to solve this issue. In addition, deprovisioning an advisor pack is much easier. Because the advisor pack object belongs to a schema other than dbo, this allows SPA to use a lower user privilege to access them.

A normal report script does the following:

  • Accesses raw collected data

  • Performs calculations based on the raw data

  • Changes alerts and recommendations

  • Prepares data for the report view

All collected data is imported into the following corresponding tables. For more info about the table schema, see Defining the data collector set.

  • Registry

    • #RegistryKeys

  • WMI

    • #WMIObjects

    • #WmiObjectProperties

    • #WmiQueries

  • Performance counter

    • #PerformanceCounters

  • File

    • #Files

  • ETW

    • #Events

    • #EventProperties

The [dbo].[SetNotification] API sets the rule status, so you can see a Success or Warning icon in the UI.

  • @ruleName nvarchar(50)

  • @adviceName nvarchar(50)

The alert and recommendation messages are stored in the provision metadata XML file. This makes the report script easier to manage.

Initially, every rule status is N/A. You can use this API to set a rule status by specifying an advice name. The level of the advice name will be used as the rule status.

Recall that we defined the following rule earlier:

<rule name="FreeDiskSize" caption="Free Disk Size on System Drive" description="This rule checks free disk size on the system drive ">
  <advice name="SuccessAdvice" level="Success" message="No issue found.">No recommendation.</advice>
  <advice name="WarningAdvice" level="Warning" message="Not enough free space on system drive.">Install the operating system on a larger disk.</advice>

Assuming the free space is less than 2 GB, we need set the rule to the Warning level. The SQL script will be as follows:

IF (@freeDiskSizeInGB < 2)
    exec dbo.SetNotification N'FreeDiskSize', N'WarningAdvice'
    exec dbo.SetNotification N'FreeDiskSize', N'SuccessAdvice'

The [dbo].[GetThreshold] API gets the thresholds:

  • @key nvarchar(50)

  • @value float output

The thresholds are name-value pairs, and they can be referenced in any rules. The system administrators can use the SPA console to adjust the thresholds.

Continuing with the previous example, for a threshold, the definition will be as follows:

  <threshold name="FreeDiskSize" caption="Free Disk Size (GB)" description="Free Disk Size” value="10" />
<rule name="FreeDiskSize" caption="Free Disk Size on System Drive" description="This rule checks free disk size on system drive ">
  <advice name="SuccessAdvice" level="Success" message="No issue found.">No recommendation.</advice>
  <advice name="WarningAdvice" level="Warning" message="Not enough free space on the system drive.">
Install the operating system on a larger disk.</advice>
    <threshold ref="FreeDiskSize"/>

The report script can be modified as shown here:

exec dbo.GetThreshold N’FreeDiskSize’, @freeDiskSize output

IF (@freeDiskSizeInGB < @freeDiskSize)

The [dbo].[SetSingleValue] API sets the single value:

  • @key nvarchar(50)

  • @value sql_variant

This value can execute multiple times for the same single value key. The last value is saved.

The following example shows some defined single values:

<singleValue section="SystemOverview" caption="Facts">
  <value name="OsName" type="string" caption="Operating System" description="WMI: Win32_OperatingSystem/Caption"/>
  <value name="OsVersion" type="string" caption="OS Version" description="WMI: Win32_OperatingSystem/Version"/>
  <value name="OsLocation" type="string" caption="OS Location" description="WMI: Win32_OperatingSystem/SystemDrive"/>

You can then set the single value as shown here:

exec dbo.SetSingleValue N’OsName’, ‘Windows 7’
exec dbo.SetSingleValue N’OsVersion’, ‘6.1.7601’
exec dbo.SetSingleValue N’OsLocation’, ‘c:\’

In rare cases, you may want to remove the result that you previously set by using the [dbo].[RemoveSingleValue] API.

  • @key nvarchar(50)

You can use the following script to remove the previously set value.

exec dbo.RemoveSingleValue N’OsVersion’

The [dbo].[GetDuration] API gets the user designated duration in seconds for the data collection:

  • @duration int output

Here’s an example report script:

DECLARE @duration int
exec dbo.GetDuration @duration output

The [dbo].[GetInternal] API gets the interval of a performance counter. It could return NULL if the current report does not have performance counter information.

  • @interval int output

Here’s an example report script:

DECLARE @interval int
exec dbo.GetInterval @interval output

There is no API for updating list value tables. However, you can directly access the list value tables. At the initialization stage, a corresponding temporary table will be created for each list value.

The following example shows a list value table:

<listValue name="NetworkAdapterInformation" section="NetworkIOFacts" caption="Physical Network Adapter Information">
  <column name="NetworkAdapterId" type="string" caption="ID" description="WMI: Win32_NetworkAdapter/DeviceID"/>
  <column name="NetworkAdapterName" type="string" caption="Name" description="WMI: Win32_NetworkAdapter/Name"/>
  <column name="Type" type="string" caption="Type" description="WMI: Win32_NetworkAdapter/AdapterType"/>
  <column name="Speed" type="decimal" caption="Speed (Mbps)" description="WMI: Win32_NetworkAdapter/Speed"/>
  <column name="MACAddress" type="string" caption="MAC Address" description="WMI: Win32_NetworkAdapter/MACAddress"/>

You can then write a SQL script to insert, update, or delete the results:

INSERT INTO #NetworkAdapterInformation (

If there is any further information that you want to communicate to the system administrators, you can write logs. If there is any log for a particular report, a yellow banner will be shown in the report header. The following example shows how you can write a log:

exec dbo.WriteSystemLog N'Any information you want to show to the system administrators’, N’Warning’

The first parameter is the message you want show in the log. The second parameter is the log level. The valid input for the second parameter could be Informational, Warning, or Error.

The SPA console can run in two modes, Debug or Release. Release mode is the default, and it cleans up all the collected raw data after the report is generated. The Debug mode keeps all raw data in the file share and database, so that you can debug the report script in the future.

  1. Install Microsoft SQL Server Management Studio (SSMS).

  2. After SSMS is launched, connect to localhost\SQLExpress. Be aware that you must use localhost, instead of “.”. Otherwise, you might not be able to start the debugger in SQL Server.

  3. Run the following script to enable Debug mode:

    UPDATE dbo.Configurations
    SET Value = N'true'
    WHERE Name = N'DebugMode'
  4. Launch the SPA console and run the advisor pack that you want to debug.

  5. Wait for the task to complete. If the report is successfully generated, switch back to SSMS and look for the latest task.


    For example, the output might be:


    Id SessionId AdvisoryPackageId ReportStatusId LastUpdateTime ThresholdVersionId





    2011-05-11 05:35:24.387


  6. You can run the following script as many times as you want to execute the report script for Id 12:

    exec dbo.DebugReportScript 12
    You can also press F11 to step into the previous statement and debug.

Running [dbo].[DebugReportScript] returns multiple result sets, including:

  1. Microsoft SQL Server messages and advisor pack logs

  2. Results of rules

  3. Statistics keys and values

  4. Single values

  5. All list value tables

  • Use Pascal casing for any names in ProvisionMetadata.xml.

  • Use Pascal casing for stored procedures, functions, and view names.

  • Use Pascal casing for temporary table names.

  • Use camel casing for parameter names.

  • Use camel casing for local variables.

  • Use uppercase for all SQL reserved keywords.

  • Move the most logical pieces into other stored procedures and user-defined functions.

  • Make your main script as brief as possible for maintenance purposes.

  • Use the full name of the SQL object.

  • Treat your SQL code as case sensitive.

  • Add SET NOCOUNT ON to the beginning of every stored procedure.

  • Consider using temporary tables to transfer huge amount of data.

  • Consider using SET XACT_ABORT ON to terminate the process if an error occurs.

  • Always include major version number in the advisor pack display name.

SPA supports running multiple advisor packs at the same time. This is especially useful when you want to look at Internet Information Services (IIS) and core operating system performance at the same time. Many data collectors that are used by the IIS advisor pack might also be used by the Core OS advisor pack. When two or more advisor packs are running on the same target computer, SPA does not collect the same data twice.

The following example shows the workflow for running two advisor packs.

Running multiple advisor packs

The Merger Data Collector Set is only for collecting performance counter and ETW data sources. The following merge rules apply:

  1. SPA takes the biggest duration as the new duration.

  2. Where there are merge conflicts, the following rules are followed:

    1. Take the smallest interval as the new interval.

    2. Take the super set of the performance counters. For example, with Process(*)\% Processor Time and Process(*)\*,\Process(*)\* returns more data, so Process(*)\% Processor Time and Process(*)\* is removed from the merged data collector set.

SPA needs a defined data collector set at design time. It is not always possible to know which data is needed for report generation because the dynamic data and query path are not known until its dependent data is available.

For example, if you want to list all the friendly names of network adapters, you must first query WMI to enumerate all the network adapters. Each returned WMI object has a registry key path, where it stores the friendly name. The registry key path is unknown at design time. In this case, we need dynamic data support.

To enumerate all network adapters, you can use the following WMI query by using Windows PowerShell:

Get-WmiObject -Namespace Root\Cimv2 -Query "SELECT PNPDeviceID FROM Win32_NetworkAdapter" | ForEach-Object { Write-Output $_.PNPDeviceID }

It returns a list of network adapter objects. Each object has a property called PNPDeviceID, which maintains a relative registry key path. Here’s a sample output from the previous query:


To find the FriendlyName value, open Registry Editor and navigate to registry setting by combining HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Enum\ with each line in the previous sample. , for example: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Enum\ ROOT\*IPHTTPS\0000.

To translate the previous steps into SPA provision metadata, add the script in the following code sample:

  <dataSourceDefinition xmlns="">
    <dataCollectorSet >
        <path name="NetworkAdapter">Root\Cimv2:SELECT PNPDeviceID FROM Win32_NetworkAdapter</path>

In this example, you first add a WMI query under managementPaths and define the key name NetworkAdapter. Then you add a registry key and refer to NetworkAdapter by using the syntax, $(NetworkAdapter.PNPDeviceID).

The following table defines if a data collector in SPA supports dynamic data and whether it can be referenced by other data collectors:


Data type Support dynamic data Can be referenced

Registry key









Performance counter






For a WMI data collector, each WMI object has many attached attributes. Any type of WMI object always has three attributes: __NAMESPACE, __CLASS, and __RELPATH.

To define a data collector that is referenced by other data collectors, assign the name attribute with a unique key in the ProvisionMetadata.xml. This key is used by dependent data collectors to generate dynamic data.

Here’s an example for registry key:

<registryKey  name="registry">HKEY_LOCAL_MACHINE…</registryKey>

And an example for WMI:

<path name="wmi">Root\Cimv2:SELECT PNPDeviceID FROM Win32_NetworkAdapter</path>

To define a dependent data collector, the following syntax is used: $({name}.{attribute}).

{name} and {attribute} are placeholders.

When SPA collects data from a target server, it dynamically replaces the pattern $(*.*) with the actual collected data from its reference data collector (registry key / WMI), for example:

<registryKey  name="registry">HKEY_LOCAL_MACHINE\$(wmi.RelativeRegistryPath)\…</registryKey>
<path name="wmi">…</path>
SPA supports an unlimited depth of reference, but be aware of performance overhead if you have too many levels. Make sure there is no circular reference or self-reference that is not supported.

SPA supports reset and minor version updates. These processes use the same algorithm. The process is to refresh all the database objects and threshold settings but keep the existing data. This can be upgrading to a higher version or downgrading to lower version. Select the advisor pack, and then click Reset in the Configure Advisor Packs dialog box in SPA to reset or apply or the updates.

This feature is mainly for minor updates. You cannot dramatically change the UI display elements. If you want to make significant changes, you have to create a different advisor pack. You should include the major version in the advisor pack name.

The limitations of minor version modifications are:

  • Cannot change the schema name

  • Cannot change the data type of any single value group or the columns of a list value table

  • Cannot add or remove thresholds

  • Cannot add or remove rules

  • Cannot add or remove advice

  • Cannot add or remove single values

  • Cannot add or remove list values

  • Cannot add or remove a column of list values

Almost all description attributes will be shown as a tooltip in the SPA console.

For a list value table, a row-based tooltip can be achieved by adding the following attribute:

<listValue descriptionColumn="Description">
  <column name="Name"/>
  <column name="Description"/>

The descriptionColumn attribute refers to the name of the column. In this example, the description column does not show as a physical column. However, it shows as a tooltip when you mouse over each row of the first column.

We recommend that the tooltip show the data source to the user. Here are the formats for showing the data sources:


Data source Format Example


WMI: <WMIClass>/<Field>

WMI: Win32_OperatingSystem/Caption

Performance counter

PerfCounter: <CategoryName>/<InstanceName>

PerfCounter: Process/% Processor Time


Registry: <RegisterKey>

Registry: HKLM\SOFTWARE\Microsoft


Configuration file

ConfigFile: <FilePath>[; Xpath: <Xpath>]

Xpath is optional and it is valid only when the file is an xml file.

ConfigFile: %windir%\System32\inetsrv\config\applicationHost.config

Xpath: configuration/system.webServer



ETW: <Provider>(Keywords)

ETW: Windows Kernel Trace (process, net)

When an advisor pack becomes more complicated, you can create your own variable tables or temporary tables to store intermediate results in the report script.

Collating string columns may be problematic because the table collation that you create might be different than the one that is created by the SPA framework. If you correlate two string columns in different tables, you may see a collation error. To avoid this issue, you should always define the string for a column collation as SQL_Latin1_General_CP1_CI_AS when you define a table.

Here’s how to define a variable table:

    Name nvarchar(500) COLLATE SQL_Latin1_General_CP1_CI_AS,
    AverageFileAccessVolume float,
    AverageFileAccessCount float,
    FilePath nvarchar(500) COLLATE SQL_Latin1_General_CP1_CI_AS

Here’s how to define ETW in a ProvisionMetadata.xml file:

    <provider session="NT Kernel Logger" guid="{9E814AAD-3204-11D2-9A82-006008A86939}"/>

The following provider attributes are available to use for collecting ETW:


Attribute Type Description



Provider GUID



ETW session name (optional, only required for kernel events)



Any keywords (optional, no 0x prefix)



All keywords (optional)



Properties (optional)



Level (optional)



Buffer size (optional)



Flush time (optional)



Maximum buffer (optional)



Minimum buffer (optional)

There are two output tables as shown here.

#Events table schema


Column name SQL data type Description



Correlation sequence ID



Event type ID (refer to [dbo].[EventTypes])



Process ID



Thread ID


Datetime2 NOT NULL




Kernel time



User time

#EventProperties table schema


Column name SQL data type Description



Correlation sequence ID



Property name




An ETW schema can be generated by running tracerpt.exe against the .etl file. A file is generated. Because the format of the .etl file is computer dependent, the following script only works in the following situations:

  1. Run the script on the computer where the corresponding .etl file is collected.

  2. Or run the script on a computer with same operating system and components installed.

tracerpt *.etl -export

The following terms are used in this document:

  • Advisor pack

    An advisor pack is a collection of metadata and SQL scripts that process the performance logs that are collected from the target server. The advisor pack then generates reports from the performance log data. The metadata in the advisor pack defines the data to be collected from the target server for performance measurements. The metadata also defines the set of rules, the thresholds, and the report format. Most often, an advisor pack is written specifically for a single server role, for example, Internet Information Services (IIS).

  • SPA console

    The SPA console refers to SpaConsole.exe, which is the central part of Server Performance Advisor. SPA does not need to run on the target server that you are testing. The SPA console contains all the user interfaces for SPA, from setting up the project to running analysis and viewing reports. By design, SPA is a two-tier application. The SPA console contains the UI layer and part of the business-logic layer. The SPA console schedules and processes performance analysis requests.

  • SPA framework

    SPA contains two major parts, the framework and the advisor packs. The SPA framework provides all the user interfaces, performance log processing, configuration, error handling, and database APIs, and management procedures.

  • SPA project

    A SPA project is a database that contains all the information about the target servers, advisor packs, and performance analysis reports that are generated on the target servers for the advisor packs. You can compare and view history and trend charts within the same SPA project. The user can create more than one project. The SPA projects are independent of one another, and there is no data shared across projects.

  • Target server

    The target server is the physical computer or virtual machine that runs the Windows Server with certain server roles, such as IIS.

  • Data analysis session

    A data analysis session is a performance analysis on a specific target server. A data analysis session can include multiple advisor packs. The data collector sets from those advisor packs are merged into a single data collector set. All performance logs for a single data analysis session are collected during the same time period. Analyzing reports that are generated by advisor packs running in the same data analysis session can help users understand the overall performance situation and identify root causes for performance issues.

  • Event Tracing for Windows

    Event Tracing for Windows (ETW) is a high-performance, low-overhead, scalable tracing system that is provided in the Windows operating systems. It provides profiling and debugging capabilities, which can be used to troubleshoot a variety of scenarios. SPA uses ETW events as a data source for generating the performance reports. For general info about ETW, see Improve Debugging and Performance Tuning with ETW.

  • WMI query

    Windows Management Instrumentation (WMI) is the infrastructure for management data and operations in Windows operating systems. You can write WMI scripts or applications to automate administrative tasks on remote computers. WMI also supplies management data to other parts of the operating system and to products. SPA uses WMI class information and data points as sources for generating performance reports.

  • Performance counters

    Performance counters are used to provide information about how well the operating system or an application, service, or driver is performing. The performance counter data can help determine system bottlenecks, and fine-tune system and application performance. The operating system, network, and devices provide counter data that an application can consume to provide users with a graphical view of how well the system is performing. SPA uses performance counter information and data points as sources to generate performance reports.

  • Performance Logs and Alerts

    Performance Logs and Alerts (PLA) is a built-in service in the Windows operating system. It is designed to collect performance logs and traces, and it also raises performance alerts when certain triggers are met. PLA can be used to collect performance counters, event tracing for Windows (ETW), WMI queries, registry keys, and configuration files. PLA also supports remote data collection through remote procedure calls (RPC). The user defines a data collector set, which includes information about the data to be collected, frequency of data collection, data collection duration, filters, and a location for saving the result files. SPA uses PLA to collect all the performance data from the target servers.

  • Single report

    Single report is the SPA report that is generated based on one data analysis session for one advisor pack on a single target server. It can contain notifications and various data sections.

  • Side-by-side report

    A side-by-side report is an SPA report that compares two single reports for the same advisor pack. The two reports can be generated from different target servers or from separate performance analysis runs on the same target server. The side-by-side report creates the capability to compare two reports to help users identify abnormal behaviors or settings in one of the reports. A side-by-side report contains notifications and various data sections. In each section, data from both reports are listed side-by-side.

  • Trend chart

    A trend chart is the SPA report that is used to investigate repetitive patterns of performance issues. Many repetitive performance issues are caused by scheduled server load changes from the server or from client computers, which can happen daily or weekly. SPA provides a 24-hour trend chart and a 7-day trend chart to identify these issues.

    The user can choose one or more data series at a time, which is a numeric value inside the single report, such as Average total CPU usage. More specifically, a numeric value is a scalar value from a single server that is generated by a single AP at a given time instance. SPA groups those values into 24 groups, one for each hour of the day (seven for a 7-day report, one for each day of the week). SPA calculates average, minimum, maximum, and standard deviations for each group.

  • Historical chart

    An historical chart is the SPA report that is used to show changes in certain numeric values inside single reports for a given server and advisor pack pair over time. The user can choose multiple data series and show them together in the historical chart to understand the correlation between different data series.

  • Data series

    A data series is numeric data that is collected from the same data source over a period of time. The same source means that the data has to come from the same target server, such as the average request queue length for IIS on one server.

  • Rules

    Rules are combinations of logic, thresholds, and descriptions. They represent a potential performance issue. Each advisor pack contains multiple rules. Each rule is triggered by a report generation process. A rule applies the logic and thresholds to the data in single report. If the criteria are met, a warning notification is raised. If not, the notification is set to the OK state. If the rule does not apply, the notification is set to the Not Applicable (NA) state.

  • Notifications

    A notification is the information that a rule displays to users. It includes the status of the rule (OK, NA, or Warning), the name of the rule, and possible recommendations to address the performance issues.

© 2014 Microsoft