Back in the days, I used to collect a lot of different scripts, tools, and other goodies for troubleshooting SQL Server performance issues. These days, however, I use what is available for free and keep a list of those in my head.

I’ve meant to write that list down for a while, and today Chrissy asked:

Anyone have a list of apps, modules, tools, etc that Microsoft PFE's use? If not, can a PFE write a blog post? 😁 (Partic. interestd in SQL)

Free scripts and tools from Microsoft

These are scripts and tools provided for free by Microsoft that I use. Some of them come “as-is” with no official support.

BPCheck – This is by far my favorite script and one I use all the time. I like to joke that there are one good thing and one bad thing about this script. The good thing is that it gives me a lot of information. The bad thing is that it gives me a lot of information. It can overwhelm the first time you run it, but once you have used it a few times, it is handy.

SQL Nexus – Loads and analyzes performance data collected by Pssdiag. It loads the data into a database and does reporting.

PAL – The Performance Analysis of Logs (PAL) tool reads a perfmon counter log and analyzes it using different thresholds. It generates an HTML based report (or XML, if you prefer), which shows which counters are interesting. It has templates for SQL Server workloads but works with a lot of other workloads too.

RML – The Replay Markup Language (RML) utilities can analyze profiler traces or replay the trace file against another instance of SQL Server. For how to use it, read this blog post.

diskspd – Storage load generator / performance test tool. It replaces SQLIO. I use this if I want to benchmark the IO subsystem of a new machine / VM. There are two good blog posts about how to use diskspd here and here.

SQL Server Management Studio – SSMS is now a standalone application (it used to ship with the SQL Server installation) and got quite a few improvements in the newer versions.

sysinternals – Tools for advanced troubleshooting on Windows, which I use from time to time.

SQL Server Data Tools – Not a tool I use for performance troubleshooting, but it is in my toolbox for when I have to look at an SSIS package.

Free scripts, tools, and resources from the community

The awesome SQL Server community has made a lot of things available.

SQL Server Diagnostic Information Queries – Before I discovered Glenn Berry‘s DMV queries, I used to collect scripts myself. Not anymore. Glenn updates his queries every month. While I prefer to use BPCheck to get information from the DMVs, I find that Glenn’s scripts are better structured and easier to use.

sp_whoisactive – Think sp_who2… but much better. This script by Adam Machanic shows which statements are being executed and can include the estimated execution plan, acquired locks, tempdb resource allocation, and more.