Free SQL Tools to make your life easier

I’ve written and spoken about free SQL tools to make your life easier on a few occasions. It was the subject of my presentation at SQLBits 2020 so I was delighted to see Mikey Brownowski (b|t) choose tooling as the subject for February’s T-SQL Tuesday #135. #tsl2sday.

In this month’s t-SQL Tuesday, Mikey asks us to “Write a blog post about the most helpful and effective tools you use or know of.” I use quite a few free tools. I love the way the SQL Community embraces sharing and I know I wouldn’t have achieved a fraction of my career success without free tools. In my SQLBits talk, I discussed the following free tools:

1. sp_whoisactive
2. First Responder Kit
3. Statistics Parser
4. Ola Hallengren’s Maintenance Solution
5. DBATools
6. DLM Dashboard

For this post, I will focus on Statistics Parser, written by Richie Rump (b|t). My blog has info on some other free sql tools as well.

Statistics Parser

Legend

Legend has it that Richie Rump wrote it during a Brent Ozar conference session. I asked him about this and he told me:

Well, I started it in one of his training classes. It was in Atlanta. It was the last day and it was mentioned that there was an Excel spreadsheet that parsed out statistics io output. I found that as interesting but odd. So I started out writing out a web based parser right there in class. The core of it was finished on the plane ride home.

Richie Rump 2021

So what does Statistics Parser do?

If you run SET STATISTICS TIME,IO ON before you run a query in SSMS, you will get information back on how much data was accessed and how long it took. Things like logical reads, physical reads, CPU time and elapsed time etc.

If you are only querying one or two tables, it is easy enough to just read this in the messages window. But what about those complex stored procs or queries hitting multiple tables and views that return a very long list of outputs? The output can be long and intimidating and certainly hard to understand at a glance.

Statistics Parser is a web page which allows you to paste in the statistics time,io output from the SSMS messages tab, and it formats into neat tables showing how much IO happens for each table. It is immediately easier to read and you get a handy % column on the right hand side showing you which tables are being read the most. I find this really useful for query tuning because it lets me know where my biggest pain points are. For complex queries, which touch a lot of tables, it just makes it easy to see at a glance where you should initially focus your attention. It also shows worktable and workfile tables which serves as a handy hint that tempdb is in play.

A really handy feature is that you can open multiple browser tabs, but give each tab it’s own name. Then you can paste the output from the original query in to a tab you have named as “before” or “original”, then give a name to each tab as you try something new. e.g. “index_abc_added” or “fixed_cursors”. I like to do this when working through query tuning options against a restored copy of production. I use it for tuning CPU and reads. I quite often find that if I can make a big enough impact on the reads, the CPU will also come down.

via GIPHY

How to use Statistics Parser

1. Run the following query in the same SSMS window as a query you are about to troubleshoot:
   1. SET STATISTICS TIME,IO ON
2. Run your query
3. Copy/paste the message from SSMS into statisticsparser.com
4. Click Parse button
5. Open more tabs as needed

Thanks Richie

Thanks for making a great tool, free and easy to use. And thanks for answering my questions.

FYI

Attendees of SQLBits 2020 can still login using their personal link and see all the recorded content so my talk is there. In it, I spend a few minutes demonstrating Statistics Parser. I’m not sure if they will make it free to everyone else in future, like they’ve done in previous years.

Featured Image

Image by Lu Lettering from Pixabay