Server Performance Advisor Pack Development Guide

Article
05/02/2016

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.

SPA Advisor Pack Overview

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)
- Performance Logs and Alerts (PLA) data collector set
- Report layout
SQL scripts
- A main stored procedure
- SQL objects, such as stored procedures and user-defined functions
ETW schema file (Schema.man)—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.

Collect data

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.

Import data into the database

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.

Switch to low user rights

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.

Initialize execution environment

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.

Run SQL scripts

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.

Switch to administrative rights

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

Generate a report

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.

Authoring an advisor pack

Quick guidelines

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

…

Schema.man

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.

Note

The schema.man 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.

Defining basic information

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:

<advisorPack
  xmlns="https://microsoft.com/schemas/ServerPerformanceAdvisor/ap/2010"
  name="Microsoft.ServerPerformanceAdvisor.CoreOS.V2"
  displayName="Microsoft CoreOS Advisor Pack V2"
  description="Microsoft CoreOS Advisor Pack"
  author="Microsoft"
  version="1.0"
  frameworkVersion="3.0"
  minOSVersion="6.0"
  reportScript="ReportScript">
</advisorPack>

Advisor pack version

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.

Script entry point

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.

Other attributes

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

Defining the data collector set

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.

<advisorPack>
  <dataSourceDefinition xmlns="https://microsoft.com/schemas/ServerPerformanceAdvisor/dc/2010">
    <dataCollectorSet duration="10">
      <registryKeys>
        <registryKey>HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Power\User\PowerSchemes\\</registryKey>
      </registryKeys>
      <managementPaths>
        <path>Root\Cimv2:SELECT * FROM Win32_DiskDrive</path>
      </managementPaths>
      <performanceCounters interval="2">
        <performanceCounter>\PhysicalDisk(*)\Avg. Disk sec/Transfer</performanceCounter>
      </performanceCounters>
      <files>
        <path>%windir%\System32\inetsrv\config\applicationHost.config</path>
      </files>
      <providers>
        <provider session="NT Kernel Logger" guid="{9E814AAD-3204-11D2-9A82-006008A86939}" keywordsAny="06010201" keywordsAll="00000000" level="00000000" />
      </providers>
    </dataCollectorSet>
  </dataSourceDefinition>
</advisorPack>

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.

Collect registry data

You can collect registry data from the following registry hives:

HKEY_CLASSES_ROOT
HKEY_CURRENT_CONFIG
HKEY_CURRENT_USER
HKEY_LOCAL_MACHINE
HKEY_USERS

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

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Power\User\PowerSchemes]
"ActivePowerScheme"="db310065-829b-4671-9647-2261c00e86ef"

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Power\User\PowerSchemes\db310065-829b-4671-9647-2261c00e86ef]
"Description"=””
“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
"ACSettingIndex"=dword:000000b4
"DCSettingIndex"=dword:0000001e

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

<registryKey>HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Power\User\PowerSchemes</registryKey>

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

Note

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

<registryKey>HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Power\User\PowerSchemes\\</registryKey>

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
HKEY_LOCAL_MACHINE\...\PowerSchemes	1	db310065-829b-4671-9647-2261c00e86ef
…\db310065-829b-4671-9647-2261c00e86ef\Description	2
…\db310065-829b-4671-9647-2261c00e86ef\FriendlyName	2	Power Source Optimized
...\6738e2c4-e8a5-4a42-b16a-e040e769756e\ACSettingIndex	4	180
...\6738e2c4-e8a5-4a42-b16a-e040e769756e\DCSettingIndex	4	30

The schema for the #RegistryKeys table is as follows:

Column name	SQL data type	Description
KeyName	Nvarchar(300) NOT NULL	Registry key full path name
KeyTypeId	Smallint NOT NULL	Internal Type Id
Value	Nvarchar(4000) NOT NULL	All the values

The KeyTypeID column can have one of the following types:

ID	Type
1	String
2	ExpandString
3	Binary
4	DWord
5	DWordBigEndian
6	Link
7	MultipleString
8	ResourceList
9	FullResourceDescriptor
10	ResourceRequirementsList
11	Qword

Collect WMI

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
C:\pagefile.sys	C:\pagefile.sys	215

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
10	Root\Cimv2	Win32_PageFileUsage	Win32_PageFileUsage.Name= "C:\\pagefile.sys"	1

Root\Cimv2

Win32_PageFileUsage

Win32_PageFileUsage.Name=

"C:\\pagefile.sys"

#WmiObjectsProperties table

ID	Query
1	Root\Cimv2:SELECT * FROM Win32_PageFileUsage

#WmiQueries table

ID	Query
1	Root\Cimv2:SELECT * FROM Win32_PageFileUsage

#WmiObjects table schema

Column name	SQL data type	Description
SequenceId	Int NOT NULL	Correlate the row and its properties
Namespace	Nvarchar(200) NOT NULL	WMI namespace
ClassName	Nvarchar(200) NOT NULL	WMI class name
RelativePath	Nvarchar(500) NOT NULL	WMI relative path
WmiQueryId	Int NOT NULL	Correlate the key of #WmiQueries

#WmiObjectProperties table schema

Column name	SQL data type	Description
SequenceId	Int NOT NULL	Correlate the row and its properties
Name	Nvarchar(1000) NOT NULL	Property name
Value	Nvarchar(4000) NULL	The value of the current property

#WmiQueries table schema

Column name	SQL data type	Description
Id	Int NOT NULL	Unique query ID
Query	Nvarchar(4000) NOT NULL	Original query string in the provision metadata

Collect performance counters

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

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

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:
13:45:52.630	PhysicalDisk	Avg. Disk sec/Transfer	0.00100008362473995	0.00100008362473995
13:45:53.629	PhysicalDisk	Avg. Disk sec/Transfer	0.00280023414927187	0.00280023414927187
13:45:54.627	PhysicalDisk	Avg. Disk sec/Transfer	0.00385999853230048	0.00385999853230048
13:45:55.626	PhysicalDisk	Avg. Disk sec/Transfer	0.000933297607934224	0.000933297607934224

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

CategoryDisplayName	InstanceName	CounterDisplayName	Value
PhysicalDisk	_Total	Avg. Disk sec/Transfer	0.00100008362473995
PhysicalDisk	0 C: D:	Avg. Disk sec/Transfer	0.00100008362473995
PhysicalDisk	_Total	Avg. Disk sec/Transfer	0.00280023414927187
PhysicalDisk	0 C: D:	Avg. Disk sec/Transfer	0.00280023414927187
PhysicalDisk	_Total	Avg. Disk sec/Transfer	0.00385999853230048
PhysicalDisk	0 C: D:	Avg. Disk sec/Transfer	0.00385999853230048
PhysicalDisk	_Total	Avg. Disk sec/Transfer	0.000933297607934224
PhysicalDisk	0 C: D:	Avg. Disk sec/Transfer	0.000933297607934224

Note

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
Timestamp	Datetime2(3) NOT NULL	The collected date time in UNC
CategoryName	Nvarchar(200) NOT NULL	Category name
CategoryDisplayName	Nvarchar(200) NOT NULL	Localized category name
InstanceName	Nvarchar(200) NULL	Instance name
CounterName	Nvarchar(200) NOT NULL	Counter name
CounterDisplayName	Nvarchar(200) NOT NULL	Localized counter name
Value	Float NOT NULL	The collected value

Collect files

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:

<path>%windir%\System32\inetsrv\config\applicationHost.config</path>

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

QueryPath	FullPath	ParentPath	FileName	Content
%windir%\...\applicationHost.config	C:\Windows \...\applicationHost.config	C:\Windows \...\config	applicationHost.config	0x3C3F78…

%windir%\...\applicationHost.config

C:\Windows

\...\applicationHost.config

C:\Windows

\...\config

applicationHost.config

0x3C3F78…

#Files table schema

Column name	SQL data type	Description
QueryPath	Nvarchar(300) NOT NULL	Original query statement
FullPath	Nvarchar(300) NOT NULL	Absolute file path and file name
ParentPath	Nvarchar(300) NOT NULL	File path
FileName	Nvarchar(300) NOT NULL	File name
Content	Varbinary(MAX) NULL	File content in binary

Defining rules

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:

<advisorPack>
  …
  <reportDefinition>
    <thresholds>
      <threshold …/>
    </thresholds>
    <rules>
      <rule …/>
      </rule>
    </rules>
  </reportDefinition>
</advisorPack>

Threshold

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.

Alerts and recommendations

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>
  <dependencies>
    <threshold ref="FreeDiskSize"/>
  </dependencies>
</rule>

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.

Defining UI display elements

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)	100
Total Disk Size Installed (GB)	500

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)
0	100	500
1	20	320

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:

Sections
Single value groups
List value tables

Here’s an example that shows the UI elements:

<advisorPack>
  <dataSourceDefinition/>
  <reportDefinition>
    <dataTypes>
      <dataType .../>
    </dataTypes>
    <thresholds/>
    <rule/>
    <sections>
      <section .../>
    </sections>
    <singleValues>
      <singleValue .../>
    </singleValues>
    <listValues>
      <listValue .../>
    </listValues>
  </reportDefinition>
</advisorPack>

Sections

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>

Data types

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).

Single value groups

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"/>
</singleValue>

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:

Facts

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.

List value tables

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"/>
</listValue>

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" …

Defining charting elements

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.

Static statistics

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 statistics

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"/>
</listValue>

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
0	10
1	30

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	_Total	10	20
% Processor Time	CPU0	20	30

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">
    <trendableKeyValues>
      <value>_Total</value>
      <value>CPU1</value>
    </trendableKeyValues>
  </column>
  <column name="Average" type="decimal" columnType="Value"/>
  <column name="Sum" type="decimal" columnType="Value"/>
</listValue>

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

Developing report scripts

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

Access raw collected data

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

Set rule status

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>
</rule>

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)
BEGIN
    exec dbo.SetNotification N'FreeDiskSize', N'WarningAdvice'
END
ELSE
BEGIN
    exec dbo.SetNotification N'FreeDiskSize', N'SuccessAdvice'
END

Get threshold value

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

@key nvarchar(50)
@value float output

Note

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:

<thresholds>
  <threshold name="FreeDiskSize" caption="Free Disk Size (GB)" description="Free Disk Size” value="10" />
</thresholds>
<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>
  <dependencies>
    <threshold ref="FreeDiskSize"/>
  </dependencies>
</rule>

The report script can be modified as shown here:

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

IF (@freeDiskSizeInGB < @freeDiskSize)
…

Set or remove the single value

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"/>
</singleValue>

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’

Get data collection information

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

Set a list value table

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"/>
</listValue>

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

INSERT INTO #NetworkAdapterInformation (
  NetworkAdapterId,
  NetworkAdapterName,
  Type,
  Speed,
  MACAddress
)
VALUES (
  …
)

Development and debugging

Writing logs

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.

Debug

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.

Dn481524.wedge(en-us,VS.85).gif To debug a report script

Install Microsoft SQL Server Management Studio (SSMS).
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.

Run the following script to enable Debug mode:

USE SPADB
UPDATE dbo.Configurations
SET Value = N'true'
WHERE Name = N'DebugMode'

Launch the SPA console and run the advisor pack that you want to debug.
Wait for the task to complete. If the report is successfully generated, switch back to SSMS and look for the latest task.
```
SELECT TOP 1 * FROM dbo.Tasks ORDER BY Id DESC
```
For example, the output might be:

Id SessionId AdvisoryPackageId ReportStatusId LastUpdateTime ThresholdVersionId

12

17

1

2

2011-05-11 05:35:24.387

1
You can run the following script as many times as you want to execute the report script for Id 12:
```
exec dbo.DebugReportScript 12
```
Note

You can also press F11 to step into the previous statement and debug.

Id	SessionId	AdvisoryPackageId	ReportStatusId	LastUpdateTime	ThresholdVersionId
12	17	1	2	2011-05-11 05:35:24.387	1

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

Microsoft SQL Server messages and advisor pack logs
Results of rules
Statistics keys and values
Single values
All list value tables

Best practices

Naming convention and styles

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.

Other recommendations

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.

Advanced topics

Run multiple advisor packs simultaneously

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:

SPA takes the biggest duration as the new duration.
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.

Collect dynamic data

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:

ROOT\*ISATAP\0001
PCI\VEN_8086&DEV_4238&SUBSYS_11118086&REV_35\4&372A6B86&0&00E4
ROOT\*IPHTTPS\0000
…

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:

<advisorPack>
  <dataSourceDefinition xmlns="https://microsoft.com/schemas/ServerPerformanceAdvisor/dc/2010">
    <dataCollectorSet >
      <registryKeys>
        <registryKey>HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Enum\$(NetworkAdapter.PNPDeviceID)\FriendlyName</registryKey>
      </registryKeys>
      <managementPaths>
        <path name="NetworkAdapter">Root\Cimv2:SELECT PNPDeviceID FROM Win32_NetworkAdapter</path>
      </managementPaths>

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	Yes	Yes
WMI	Yes	Yes
File	Yes	No
Performance counter	No	No
ETW	No	No

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>HKEY_LOCAL_MACHINE\$(registry.key)\…</registryKey>
<registryKey  name="registry">HKEY_LOCAL_MACHINE\$(wmi.RelativeRegistryPath)\…</registryKey>
<path name="wmi">…</path>
<file>$(wmi.FileName)</file>

Note

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.

Versioning limitations

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

Tooltips

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"/>
</listValue>

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	WMI: <WMIClass>/<Field>	WMI: Win32_OperatingSystem/Caption
Performance counter	PerfCounter: <CategoryName>/<InstanceName>	PerfCounter: Process/% Processor Time
Registry	Registry: <RegisterKey>	Registry: HKLM\SOFTWARE\Microsoft \ASP.NET\RootVer
Configuration file	ConfigFile: <FilePath>[; Xpath: <Xpath>] Note 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 /httpProtocol/@allowKeepAlive
ETW	ETW: <Provider>(Keywords)	ETW: Windows Kernel Trace (process, net)

Table collation

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:

DECLARE @filesIO 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
)

Collect ETW

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

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

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

Attribute	Type	Description
guid	GUID	Provider GUID
session	string	ETW session name (optional, only required for kernel events)
keywordsAny	Hex	Any keywords (optional, no 0x prefix)
keywordsAll	Hex	All keywords (optional)
properties	Hex	Properties (optional)
level	Hex	Level (optional)
bufferSize	Int	Buffer size (optional)
flushTime	Int	Flush time (optional)
maxBuffer	Int	Maximum buffer (optional)
minBuffer	Int	Minimum buffer (optional)

There are two output tables as shown here.

#Events table schema

Column name	SQL data type	Description
SequenceID	Int NOT NULL	Correlation sequence ID
EventTypeId	Int NOT NULL	Event type ID (refer to [dbo].[EventTypes])
ProcessId	BigInt NOT NULL	Process ID
ThreadId	BigInt NOT NULL	Thread ID
Timestamp	Datetime2 NOT NULL	Timestamp
KernelTime	BigInt NOT NULL	Kernel time
UserTime	BigInt NOT NULL	User time

#EventProperties table schema

Column name	SQL data type	Description
SequenceID	Int NOT NULL	Correlation sequence ID
Name	Nvarchar(100)	Property name
Value	Nvarchar(4000)	Value

ETW schema

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

Run the script on the computer where the corresponding .etl file is collected.
Or run the script on a computer with same operating system and components installed.

tracerpt *.etl -export

Glossary

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.

Send comments about this topic to Microsoft

Share via

Server Performance Advisor Pack Development Guide

SPA Advisor Pack Overview

Advisor pack workflow

Collect data

Import data into the database

Switch to low user rights

Initialize execution environment

Run SQL scripts

Switch to administrative rights

Generate a report

Authoring an advisor pack

Quick guidelines

Defining basic information

Advisor pack version

Script entry point

Other attributes

Defining the data collector set

Collect registry data

Collect WMI

Collect performance counters

Collect files

Defining rules

Threshold

Alerts and recommendations

Defining UI display elements

Sections

Data types

Single value groups

List value tables

Defining charting elements

Static statistics

Dynamic statistics

Developing report scripts

Access raw collected data

Set rule status

Get threshold value

Set or remove the single value

Get data collection information

Set a list value table

Development and debugging

Writing logs

Debug

Best practices

Naming convention and styles

Other recommendations

Advanced topics

Run multiple advisor packs simultaneously

Collect dynamic data

Versioning limitations

Tooltips

Table collation

Collect ETW

ETW schema

Glossary

Additional resources