Collect Performance Data

Introduction

Server is sometimes slow and you want to know why? Here is a lightweight performance logging kit which will give you the answer. It supports SQL Server and SQL Azure Databases. Perfmon part also can be used on any Windows machine (eg, web/app server) to log peformance data, SQL Server is NOT required. Scripts are NOT installing any new software. They configure components you already have there: perfmon (existing windows feature) and extended events (existing SQL server feature). You could click-and-configure all of that manually without downloading anything, but these scripts save your time do that for you. This super-lightweight logging is already in use by many productions with mission-critical workload. It is very minimalistic, capturing a minimal set of performance-relevant information, but wide enough for full diagnostics.

The details

There are two parts to install:

1. Perfmon counters– they log CPU, RAM, disk, etc. Run the powershell script “sqlcollector.ps1“. Powershell configures perfmon to log these metrics, and adds a scheduled task to start-up perfmon after a machine restart so you do not have a gap in data.

2. Extended Events – they log slow SQL queries, blocks/deadlocks, errors, and timeouts. Each SQL script creates SQL extended events session filtered to capture only relevant information and log that into a file.

Adjust paths at the top of each script to point to location where log file will be placed. Account that runs SQL engine must have permission to read and write to that folder. Make sure if has at least 2GB of free space. Oldest files are deleted in rotational scheme therefore space usage does not grow.

Download SQL Performance Logger v2.9

Supports SQL2012 SP4+ till SQL2019. SQL2008R2 and older are not supported, but you could comment-out parts that throw error, because older SQL versions do not have some of events/attributes used in the script.

Installation Screenshots

Troubleshooting

• if powershell fails, check this.

Sql scripts

Creates Extended Events (XE) session that logs slow queries, blocks&deadlocks, and SQL errors:

Troubleshooting:

• Set destination path before executing! Make sure SQL has permission to write there.
• Make sure you have supported version of SQL. If your version is below supported, upgrade to latest Service Pack and Cumulative Update before installing this XE.
• SQL2012 fails if dot (.) is in the name of the xe file, so dot is removed from the file name in the script

History

2020-02-22 Version 2.4

• Fix: Perfmon – more succesfull install. Now asks for credentials, instead of failing miserably. Also fixes execution policy if needed. Added instructions.
• Fix: LongRunningStmt now logs abortied queries independent of “slowness threshold” value. Every aborted query (timeout) is logged, not just ones above threshold.
• Improved: BlockedProcessReport analysis script now can decode waited resource names and has accurate local time. Deadlock analysis added.
• Improved: ErrorEvents now logs tsql_stack so we can find who caused the error. Analysis part is improved, extracting error per hour, statement, object.
• Improved: LongRunningStmt now logs client process ID so we can identify exact process on the client machine that called a slow statement. Analysis is also improved with a summary row at the top so we can see how much each query participates in total.

2019-08-29 Version 2.2

• Improved: Perfmon now has Page writes/sec, and removed 3 useless counters around transaction log (info visible anyway from other counters).

2019-01-04 Version 2.0

• New: SQL Azure Database support, XE sessions adjusted to work on Azure SQL Databases
• New: DBA.ErrorEvents – captures SQL errors for later analysis
• Improved: blockage XE by adding deadlocks
• Improved: Configurable XE session names

2018-09-08 Version 1.3

• New: Added BlockedProcessReport.sql
• Improved: LongRunningStmt

2018-05-25 Version 1.2