Collect Performance Data

Introduction

Server is sometimes slow and you want to know why? Here is a lightweight diagnostic SQL kit which will give you the answer. You need to put it on your server to start logging of performance info. This solution is already in use by many productions with mission-critical workload.

The details

There are several 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.
  2. LongRunningStmt.sql – Extended Events session – logs slow queries on that SQL instance. Requires 1GB of disk space (rolling 10 files x 100MB). Any query above 5 seconds will be logged. If you have a ton of such queries, adjust the threshold a bit higher (eg 20 sec) to avoid catching too much events. Because we want to concentrate on the slowest ones first.
  3. BlockedProcessReport.sql – Extended Events session – in case a process is blocked for longer time (>10s), logs which statement was blocked by which statement, and other details about it.

Examine the scripts before running, and adjust paths on disk. Make sure disk has enough space (>2GB).

Download SQL Performance Logger

2018-09-08 Version 1.3: Added BlockedProcessReport.sql, improved LongRunningStmt

2018-05-25 Version 1.2

Installation Screenshots

Sql script:

Change path and execute that SQL. It creates Extended Events session that logs slow queries.

Tagged with:

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.