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, but perfmon part can be used on any other Windows machine to log perormance data, even without SQL Server. This lightweight logging is already in use by many productions with mission-critical workload. Advantage is – you are NOT installing any new tools: we exploit native, already existing features of windows and SQL, by configuring them to capture performance-relevant information.
There are two parts to install:
1. Perfmon counters– they log CPU, RAM, disk, number of TCP connections. As well as some SQL instance-level counters like lock wait time, memory, plan cache, batch requests, etc. SQL counters are added for every auto-detected sql instance on the machine. Instally by running powershell script “sqlcollector.ps1“. Can be used on any windows machine even without SQL Server, eg on Web server.
2. Extended Events – on SQL (Azure or classic) they log slow queries, blocked/deadlocked queries, and SQL errors.
- LongRunningStmt.sql – logs slow queries (eg, duration > 10s)
- BlockedProcessReport.sql – logs blocked queries (eg. blocked > 10s)
- ErrorEvents.sql – logs SQL errors
Examine the scripts before running, and adjust paths on disk. Make sure disk has enough space (>2GB).
Supports SQL2012 SP4+ till SQL2019. Older SQL2008R2 is 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.
Change path and execute that SQL. It creates Extended Events session that logs slow queries.
Impact on production
This is super-lightweight logging. Overhead is zero or close to zero. It is safe to leave it running even after performance tuning is finished. Because app code changes, and when new issue arrives, you are ready with logged event. The only thing to keep eye on is disk space. Because perfmon log creates around 40MB per day and will not auto-delete old logs. Extended event logs are limited to 1GB (10 files x 100MB) and will auto-delete and not grow. One might want to adjust the thresholds for extended events to a higher value (parameters at beginning of each script), if extended events fills 1GB in less than 24h. Not because impact, but to have at least 24h of history recorded.
Why is impact so low? Because perfmon is already part of Windows. We just set which counters to track every 30 seconds (eg CPU%). Extended events are also existing part of SQL Server, we do not install any new tool. Extended events are lightweight by nature. We configure it to capture slow queries, blocked queries and errors. Those events are rare by nature, making this logging even more lightweight, with almost zero overhead. Runs on many production environments non-stop, because it is so light that stopping it would make no difference, and the data it logs is valuable. Even environments with super-heavy workload handle it flawlessly without a single “overhead” issue found so far.
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