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.
There are two parts:
- 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.
- 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.
Examine the scripts before running, and adjust paths on disk. Make sure disk has enough space (>2GB).
Version updated: 2018-02-06
Change path and execute that SQL. It creates Extended Events session that logs slow queries.