2014-03-02

SQL Server performance counters

This is some notes on a basic performance baseline. The baseline is created by some Performance Counters.
In general I am interested in these key resources
  • CPU
  • Memory
  • Storage
  • Network
These resources I look at from both Windows Server and SQL Server Database Engine.
A quick and broad collection can be done by using these Performance Counter objects at top level
  • Server
  • System
  • Processor
  • Memory
  • LogicalDisk
  • NetworkInterface
Use the <All instances> selection of instances to get all counters on each object. Define the collection with * (star) as this is less expensive.

The Collection can be automated using Logman. This I might look into later...

Data Collector

A User Defined Data Collector Set can be created in the Performance Monitor by a right-click on
Data Collector Sets \ User Defined
and select to create the data collector set manually.

Performance Counters

The sample interval is set to 5 seconds and the data are logged in a binary (.blg) file on a local drive. Set the maximum size of the blg-file to 1024 MB. This is to reduce the risk of the drive to be filled and to make it easier to handle for moving around and like. If you need logging for a longer period then set the collector to automatic restart. Keeping the file size low you are able to offload blg-files and analyse offline without stopping the collector.
To further reduce the risk of hurting the computer by the collector I usually put the blg-file(-s) on another drive than the system drive or drives holding active database files (mdf, ndf, ldf etc).

Never do a performance measure remote as you will create more preasure by the measure and then get a blurred result.
Also logging direct in a Performance Monitor graph might give you a performance penalty. Even if it is done local on the server.
Disk performance counters are on individual drives where user data (.mdf & .ndf), transaction log (.ldf) and tempdb data (.mdf & .ndf) are placed. If there are more drives installed for one of theese all drives should be included in the measure.
Windows Server
LogicalDisk \ % Disk Read Time (data; translog; tempdb; backup )
LogicalDisk \ % Disk Write Time (data; translog; tempdb; backup )
LogicalDisk \ Avg. Disk Queue Length ( data; translog; tempdb; backup )
LogicalDisk \ Avg. Disk Reads/sec ( data; translog; tempdb; backup ) - read throughput (IOPS)
LogicalDisk \ Avg. Disk Writes/sec ( data; translog; tempdb; backup ) - write throughput (IOPS)
LogicalDisk \ Avg. Disk sec/Read ( data; translog; tempdb; backup ) - latency on read
LogicalDisk \ Avg. Disk sec/Write ( data; translog; tempdb; backup ) - latency on write
LogicalDisk \ % Disk Idle Time (translog, tempdb, data, backup)
Logical Disk \ Split IO/sec (data, tempdb, backup) - Fragmentation indicator when one IO operration is split in multiple disk requests.
Memory \ Cache Bytes
Memory \ Available Mbytes
Memory \ Page Faults/sec
Memory \ Pages/sec
Memory \ Pool Nonpaged Bytes
Memory \ Pool Paged Bytes
Memory \ Standby Cache Core Bytes
Memory \ Standby Cache Normal Priority Bytes
Memory \ Standby Cache Reserve Bytes
PhysicalDisk \ Avg. Disk Queue Length ( data; translog; tempdb; backup )
PhysicalDisk \ Avg. Disk sec/Read ( data; translog; tempdb; backup )
PhysicalDisk \ Avg. Disk sec/Write ( data; translog; tempdb; backup )
PhysicalDisk \ Disk Reads/sec ( data; translog; tempdb; backup )
PhysicalDisk \ Disk Writes/sec ( data; translog; tempdb; backup )
Processor \ % Privileged Time (All instances)
Processor \ % Processor Time (All instances)
NUMA \ total %
Network Interface \ Output Queue Length ( <physical interface> )
Paging File(_Total) \ % Usage
System \ Context Switches/sec
System \ Processor Queue Length

If you are looking for connectivity issues then you could add these counters:

TCPv4\Connections Established
TCPv4\Connection Failures
TCPv4\Connections Reset

Please take a look at the Microsoft documentation (link) for a description on the three counters.

Please notice that when you select <All instances> on Processor counters, you will get a total and not numbers on each core. You have to select the cores of the machine to get individual numbers on the cores.
This is also the case with the disk drives where each drive must be selected for collection.

Disk counters are nicely described in the article "Windows Performance Monitor Disk Counters Explained" (link).
SQL Server Database Engine
Also use the performance counters for Windows Server mentioned above.

Process(sqlservr) \ % Processor Time
Process(sqlservr) \ Page Faults/sec
Process(SQLAGENT) \ % Processor Time
Process(SQLAGENT) \ Page Faults/sec
MSSQL : Access Methods \ Forwarded Records/sec
MSSQL : Access Methods \ Full Scans/sec
MSSQL : Access Methods \ Index Searches/sec
MSSQL : Access Methods \ Page Splits/sec
MSSQL : Access Methods \ Table Lock Escalations/sec
MSSQL : Buffer Manager \ Buffer cache hit ratio
MSSQL : Buffer Manager \ Page life expectancy
MSSQL : Buffer Manager \ Page reads/sec
MSSQL : Buffer Manager \ Page writes/sec
MSSQL : General Statistics \ User Connections
MSSQL : Latches \ Average Latch Wait Time (ms)
MSSQL : Locks(_Total) \ Average Wait Time (ms)
MSSQL : Locks(_Total) \ Lock Timeouts/sec
MSSQL : Locks(_Total) \ Number of Deadlocks/sec
MSSQL : Locks(_Total) \ Locks Waits/sec
MSSQL : Memory Manager \ Total Server Memory (KB)
MSSQL : Memory Manager \ Target Server Memory (KB)
MSSQL : Plan Cache \ Cache Hit Ratio (_Total)
MSSQL : SQL Errors(_Total) \ *
MSSQL : SQL Statistics \ Batch Requests/sec
MSSQL : SQL Statistics \ SQL Compilations/sec
MSSQL : SQL Statistics \ SQL Re-Compilations/sec
MSSQL : Wait Statistics(Average wait time (ms)) \ Network IO waits
MSSQL : Wait Statistics(Average wait time (ms)) \ Page IO latch waits
SQL Server Integration Services
Also use the performance counters for Windows Server and SQL Server Database Engine mentioned above.

Process(MsDtsSrvr) \ % Processor Time
Process(MsDtsSrvr) \ Page Faults/sec
SQLServer : Databases \ Bulk Copy Rows/sec \ DW database (KB)
SQLServer : Databases \ Bulk Copy Throughput/sec \ DW database (KB)
SQLServer : SSIS Pipeline 12.0 \ Buffer Memory
SQLServer : SSIS Pipeline 12.0 \ Buffters in use
SQLServer : SSIS Pipeline 12.0 \ Buffers spooled
SQLServer : SSIS Pipeline 12.0 \ Rows read
SQLServer : SSIS Pipeline 12.0 \ Rows written
SQL Server Master Data Services
Also use the performance counters for Windows Server and SQL Server Database Engine mentioned above.

Process(???) \ % Processor Time
Process(???) \ Page Faults/sec
SQL Server Analysis Services
Also use the performance counters for Windows Server mentioned above.

Process(msmdsrv) \ % Processor Time
Process(msmdsrv) \ Page Faults/sec
Process(msmdsrv) \ Private Bytes
MSAS12 : Proc Aggregations \ Current Partitions
MSAS12 : Proc Aggregations \ Rows created/sec
MSAS12 : Proc Aggregations \ Temp file bytes written/sec
MSAS12 : Proc Indexes \ Current partitions
MSAS12 : Proc Indexes \ Rows/sec
MSAS12 : Processing \ Rows read/sec
MSAS12 : Processing \ Rows written/sec
MSAS12 : Threads \ Processing pool busy I/O job threads
MSAS12 : Threads \ Processing pool busy non-I/O threads
MSAS12 : Threads \ Processing pool idle I/O job threads
MSAS12 : Threads \ Processing pool idle non-I/O threads
MSAS12 : Threads \ Processing pool job queue length

Thomas Kejser, John Sirmon & Denny Lee: „Microsoft SQL Server 2008 R2 Analysis Services Operations Guide“.
SQL Server Reporting Services
Also use the performance counters for Windows Server and SQL Server Database Engine mentioned above.

Process(ReportingServicesService) \ % Processor Time
Process(ReportingServicesService) \ Page Faults/sec

Analysis

A quick view on one baseline measure series is easy done with a User Defined Report in Performance Monitor.
When you want to do some statistics a spreadsheet is a common tool.
To export the data from Performance Monitor to a spreadsheet you open the logged data in Performance Monitor and right-click in the graph area. Then you click Save Data As and save the data as tabulator seperated values (.tsv).
To import the data in a Excel spreadsheet you create a new sheet and select the tab Data. Click the item From Text in the panel, and do the proper selections. In Denmark where I live we use comma as decimal seperator and dot as thusand seperator. This is one of the details that could trick you. The first time you do a import you might not get it right, but I am sure you will get it right after a few tries.

Another way to post-process PerfMon files is to use Relog. This Tool can extract data, combine or split PerfMon files and export to database or other file formats. Using this Tool can enable automation on handling more complex or long-running PerfMon runs.

To analyse PerfMon data together with Windows Logs and other data you can use Performance Analysis of Logs (PAL), which is a free and open tool. It can generate some rather neat reports with impressive and usefull graphs. But be aware that it tend to aggregate numbers and then it can hide performance issues as these usually are in spike values and not in aggregated values.

When you have more sheets from several measure series you can do some delta compares and produce some nice diagrams for management. Most diagrams I create as X-Y diagrams, but the type depends on what you are looking for or trying to show.

Some basic statistics that are usefull are
  • Average = AVERAGE()
  • Median = MEDIAN()
  • Most Frequent Number = MODE.SNGL()
  • Standard Deviation = STDEV.S()
  • Min = MIN()
  • Max = MAX()
  • Percentile = PERCENTILE.EXC(). It is different from one organization to another if 95- or 98-percentile is preferred. Or another.
    Sometimes you have to look at the other end of the data. Then you should look at 5- or 2-percentile matching the high percentile.
    I recommend you to use the same (high) percentile as the rest of the organization.
The calculation I usually add in rows at the top of the sheet.

Discussion

Be aware when
(To Be Continued...)

Reference

Jonathan Kehayias: „The Accidental DBA (Day 21 of 30): Essential PerfMon counters“.
Microsoft Core Team blog: "Measuring Disk Latency with Windows Performance Monitor".
SmarterTools blog: "Windows Perfmon, Disk I/O and Possible Disk Issues".

History

2014-02-03  Blog post created with initial list of performance counters.
2015-08-06  Performance counters added: Table Lock Escalations/sec and Context Switches/sec.
2015-08-12  Performance counter on Logical Disk % Disk Time added and backup disk for all disk counters.
2017-05-15  Notes on Relog, Logman and PAL added.
2019-10-29  Counter on Disk Idle Time added on LogicalDisk.
2023-08-21  Percentile analysis added.
2025-01-30  Two counters added (Memory \ Pool Nonpaged Bytes and Memory \ Pool Paged Bytes)

2014-01-19

DoD DISA SQL Server 2012 STIGs

The american (USA) Department of Defence (DoD) has released some Security Technical Implementation Guides (STIGs) for Microsoft SQL Server 2012.
There are two principal documents:
  • "Microsoft SQL Server Database Instance Security Technical Implementation Guide"
  • "Microsoft SQL Server 2012 Database Security Technical Implementation Guide"
Also DISA has composed a general "Database Security Requirements Guide", that can be used across database managers and versions.
In general the documents are a collection of discussions about issues like:
  1. Least Privilege.
  2. Separation of Duties (SoD).
  3. Least Service.
  4. Role Based Access Control (RBAC).
  5. Security Classification.

Least Privilege

In the STIG Least Privilege is refered to as a concept, where other places it is refered to as a principle. I will not discuss the difference here as it might take this text off course.
This principle sounds simple and easy to implement, but if you really asks the same question to every acces or right it is really complicated to construct a solution that implements this principle to the core.
If you are expected to implement a system build with a framework like SharePoint or Entity Framework, it might be rather difficult - like "impossible" -  to figure out the really needed privileges.
A good sub-principle is to avoid the builtin server roles and database roles in SQL Server. This forces everybody to define the actual privilege.

Seperation of Duties

As a DBA in a financial organisation this is a very important. Not only to comply with formal rules but also to ensure a robust installation. This concept (principle?) is very often tightly integrated with Role Based Access Control in the construction and implementation.

Least Service

To contribute to at stable and secure installation it is a good idea only to install the needed services. Other nice-to-have services increases the risk of security holes and also increases the need for upgrades. Such upgrades might reduce the service availability while restarting og likewise stopping the installation.

Role Based Access Control

To ensure Seperation of Duties and meet audit requirements it is usually expected that the Security Plan is based on Role Based Access Control. Mostly the roles are business functional roles, but they can also be based on duties in a system.
There is a lot of litteratur on this complex subject. If you are expected to enforce this principle I think you should spend some time on the theories behind.
Also there is a huge administrative benefit of using Role Based Access Control, in common opposition to individual user access and rights.
If you are asked to give a user "the same rights as <existing user>" this is a worring and sure sign of no security plan and no us of Role Based Access Control. Such a request could make the day very long rather suddenly.
The subject of Role Based Access Control easily leads to the subject of Discretionary Access Control (DAC), that also is refered to several times in the documents. This is important when setting a policy of ownership.
Enforcing Role Based Access Control is not only implementing functional roles, but also to handle the faces of SQL Server that might break this principle if handled without proper awareness. A classical situation is the database owner, where you might have a database owned by a subject that should have limited rights on some object in the database.

Security Classification

This issue is mentioned in several of the documents, sometimes as security labels on the data.
Such a rule is very usefull to implement especially when dealing with sensitive data. But it requires the organisation to define both the sensitive data and the security levels shown by the labels. These defnintions must be precise and unique enough to be implemented.

History

2014-01-19 Blog post created.
2017-05-29 Link for DISA database STIGs updated.

2014-01-08

Audit Log for Analysis Services

The SQL Server Database Engine has the feature Login Auditing, that can create a simple audit log. This log contains among other things service start and stop and logins.
I was approached with a similar requirement on SQL Server Analysis Services (SSAS), but this does not have something like Login Auditing.
SSAS does have tracing features, and can be monitored with Extended Events. The documentation is mostly written for the Database Engine, but with some trial-and-error it can be implemented on SSAS with a XMLA statement.

The documentation from Microsoft is not good, but I found a great blogpost with a working example (http://byobi.com/blog/2013/06/extended-events-for-analysis-services/). To this example I have added some features to meet requirements.

Auto Restart

Auto Restart is required to ensure that the trace is running even after a restart of the SSAS service or the server. This is implemented by adding a AutoRestart element to the XMLA statement.

Limited number of tracefiles

To ensure that the tracing does not run wild, we decided to limit the size of the logfiles and the number of logfiles. This is by the configuration of the Event File Target.
The size of each logfile is defined with the option "max_file_size" with a value i MBs (MiBs).
The number of logfiles is defined by the option "max_rollover_files".

Implementation

The XMLA statement to implement the Audit Log:
<Create
  xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
  xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
  xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"
  xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"
  xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
  <ObjectDefinition>
    <Trace>
      <ID>SQLAdmin_Audit</ID>
      <Name>SQLAdmin_Audit_2014-W02</Name>
      <AutoRestart>true</AutoRestart>

      <ddl300_300:XEvent>
        <event_session name="xeas"
          dispatchLatency="1"
          maxEventSize="4"
          maxMemory="4"
          memoryPartitionMode="none"
          eventRetentionMode="allowSingleEventLoss"
          trackCausality="true">

        <event package="AS" name="AuditLogin" />
        <event package="AS" name="AuditLogout" />

        <event package="AS" name="ExistingConnection" />
        <event package="AS" name="ExistingSession" />
        <event package="AS" name="SessionInitialize" />

        <target package="Package0" name="event_file">
          <parameter name="filename" value="L:\MSSQL_AS_Log\SQLAdmin_Audit.xel" />
          <parameter name="max_file_size" value="1024" />
          <parameter name="max_rollover_files" value="3" />
        </target>
      </event_session>
    </ddl300_300:XEvent>
    </Trace>
  </ObjectDefinition>
</Create>

Operation

This DMX statement shows what traces that are running:
SELECT *
FROM [$System].[DISCOVER_TRACES];


This XMLA statement deletes the trace. The logfiles (.xel) will not be deleted.
<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <TraceID>SQLAdmin_Audit</TraceID>
  </Object>
</Delete>


A logfile can be browsed with SQL Server Management Studio. The file can be accessed on a UNC path.
The original blogentry mentions that the logfile can be read with the T-SQL function sys.fn_xe_file_target_read_file, but this is a Database Engine function.
To use this function to get the contents of the logfile – in XML – the file must be moved or copied to a location where a Database Engine can acces the file.

2013-12-11

Temporary Windows file

Sometimes it can be usefull to create a temporary Windows file, that is more general available than a personal temporary user file.
If you use the environmental variablesTEMP“ or „TMP“ you get your personal folder for temporary files, e.g. „C:\Users\Niels\AppData\Local\Temp“. Both variables gives the same folder name.

To get the path of the more general folder of temporary files „C:\Windows\Temp“ you can use the environmental variables „SystemRoot“ or „windir“ and add the name of the Temp-folder in PowerShell like
"$Env:SystemRoot\Temp"

To create a unique and identifiable temporary file I usually add a timestamp to the filename like
.$(("{0:s}Z" -f $([System.DateTime]::UtcNow)).Replace(':', '_')).
The Z is to indicate that the timestamp is UTC. That is timezone Z also called Zulu time.
The Replace method of the formatted String-object is used to get rid of the colons in the standard format. A colon is not acceptep by Windows in a file name.

The name of a temporary file can be created in a single PowerShell statement
$TempFile = "$Env:SystemRoot\Temp\_temp.$(("{0:s}Z" -f $([System.DateTime]::UtcNow)).Replace(':', '_')).ps1"

Writing to the temporary file can be done in PowerShell with the cmdlet Out-File or PowerShell redirection operators.
The first line can be written without any terms, but the following lines must be added to preserve the existing contents of the temporary file.
The first line, that also creates the temporary file, can be written in PowerShell like
'1st line' | Out-File -FilePath $TempFile
A second line can be added using the cmdlet Out-File like
'2nd line' | Out-File -FilePath $TempFile -Append
A third line can be added with the PowerShell appending redirection operator like
'3rd line' >> $TempFile

With the three examples above a temporary file is created
C:\Windows\Temp\_temp.2013-12-11T15_33_50Z.ps1
The contents of the temporary file is
1st line
2nd line
3rd line


This simple technique can be used to create other temporary Windows files like text files or data (csv) files.

2013-09-10

xp_instance_regwrite syntax

The stored procedure "master.sys.xp_instance_regwrite" is not documented in SQL Server Books Online (BOL), but is usefull. In general the syntax is
EXECUTE [master].[sys].[xp_instance_regwrite]
  @rootkey = N'<name of root key>'
 ,@key = N'<key name>'
 ,@value_name = N'<key name value>'
 ,@type = N'<key type>'
 ,@value = N'<key value>';


For example the number of SQL Server Errorlog files can be updated by the statement
EXECUTE [master].[sys].[xp_instance_regwrite]
  @rootkey = N'HKEY_LOCAL_MACHINE'
 ,@key = N'Software\Microsoft\MSSQLServer\MSSQLServer'
 ,@value_name = N'NumErrorLogs'
 ,@type = N'REG_DWORD'
 ,@value = 42;


Use with caution!

The parameter @type takes a value without N'' around (@type = <key type>), but I like the Unicode framing. This way I try to avoid difficulties when building the values outside the call.

The type of the value for the parameter @value depends on the registry value type.
If the registry value type is a integer like REG_DWORD, the parameter value must be a integer.
 ,@value = 42;
If the registry value type is a string like REG_SZ, the parameter value must be a string.
 ,@value = N'John';

PowerShell usage

If the usage of this (undocumented) procedure should be included in a PowerShell script, it could be with ADO.NET in a function like this
function Write-MsSqlDbInstanceRegistry
{
  [CmdletBinding()]
  Param (
    # help description
    [Parameter(Mandatory=$true, Position=0)]
    [System.Data.SqlClient.SqlConnection]$MsSqlDbCnn,

    # Registry key path
    [Parameter(Mandatory=$true, Position=1)]
    [String]$RegKey,

    # Registry key name
    [Parameter(Mandatory=$true, Position=2)]
    [String]$RegName,

    # Registry key type
    [Parameter(Mandatory=$true, Position=3)]
    [String]$RegType,

    # Registry key value
    [Parameter(Mandatory=$true, Position=4)]
    [String]$RegValue
  )

  Begin {
    "{0:s}Z Write-MsSqlDbInstanceRegistry( '$RegKey', '$RegName', '$RegType', '$RegValue')" -f $([System.DateTime]::UtcNow) | Write-Verbose
  }

  Process {
    $MsSqlDbCmd = New-Object System.Data.SqlClient.SqlCommand
    $MsSqlDbCmd.Connection = $MsSqlDbCnn
    $MsSqlDbCmd.CommandText = '[master].[sys].[xp_instance_regwrite]'
    $MsSqlDbCmd.CommandType = [System.Data.CommandType]::StoredProcedure

    $MsSqlDbCmd.Parameters.Add("@rootkey", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@rootkey'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@rootkey'].Value = 'HKEY_LOCAL_MACHINE'

    $MsSqlDbCmd.Parameters.Add("@key", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@key'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@key'].Value = $RegKey

    $MsSqlDbCmd.Parameters.Add("@value_name", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@value_name'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@value_name'].Value = $RegName

    $MsSqlDbCmd.Parameters.Add("@type", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@type'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@type'].Value = $RegType

    switch -CaseSensitive ($RegType) {
      'REG_DWORD' {
        $MsSqlDbCmd.Parameters.Add("@value", [System.Data.SqlDbType]::Int) | Out-Null
        $MsSqlDbCmd.Parameters['@value'].Value = [Int]$RegValue
      }
      'REG_SZ' {
        $MsSqlDbCmd.Parameters.Add("@value", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
        $MsSqlDbCmd.Parameters['@value'].Value = $RegValue
      }
      default {
        "{0:s}Z Unknown Registry Value Type '$RegKeyType'." -f $([System.DateTime]::UtcNow) | Write-Error
        return
      }
    }
    $MsSqlDbCmd.Parameters['@value'].Direction = [System.Data.ParameterDirection]::Input

    $MsSqlDbCnn.Open()
    [Int]$RowCount = $MsSqlDbCmd.ExecuteNonQuery()
    $MsSqlDbCnn.Close()
  }

  End {}
}

The PowerShell function can be used like this
$Cnn = New-Object System.Data.SqlClient.SqlConnection
$Cnn.ConnectionString = 'Data Source=(local);Integrated Security=SSPI;Application Name=MsSqlDbInstanceRegistry'

Write-MsSqlDbInstanceRegistry `
  -MsSqlDbCnn $Cnn `
  -RegKey 'Software\Microsoft\MSSQLServer\MSSQLServer' `
  -RegName 'NumErrorLogs' `
  -RegType 'REG_DWORD' `
  -RegValue '42' `
  -Verbose


History

2010-09-15  First entry.
2013-09-09  PowerShell added.
2013-11-19  Registry Value Types handled correct.