T-SQL Tuesday #135: The outstanding tools of the trade that make your job awesome

Welcome to my first T-SQL Tuesday blog and thanks to Michael Bronowski (b|t) for hosting and picking a great topic. Not familiar with T-SQL Tuesday? Started by Adam Mechanic (b|t) back in 2009, the idea is that each month we have a different host who selects the theme and on the second Tuesday each month bloggers from around the world share their knowledge on the topic. Let the blog party start now!

I still remember the first time when a user contacted me with the question: “The database is running slow. Can you help?”. Wouldn’t it be amazing if you have a tool that can help you capture detailed information on what’s running on your instance in situations like that? There is! Today I want to share with you one of my favourite tools- the stored procedure sp_whoisactive developed by Adam Mechanic. Sp_whoisactive allows you to get a lot of information about the active session of a SQL Server instance such as the query being executed, the user who is executing, the wait event, runtime, CPU usage, Tempdb usage and more. You can get even more information if you pass a parameter.

Getting started with sp_WhoIsActice is easy. To download the procedure go to the website downloads page and select the relevant release. Open who_is_active.sql file using SQL Server Management Studio and execute the script. You will see that by default sp_WhoIsActive only returns running user processes.

The procedure provides the following information for each process:

dd hh:mm:ss.mssProcess elapsed time
session_idThe process session id
sql_textThe currently running SQL command
login_nameThe login name associated with the process
wait_infoThe process wait information (aggregated)
CPUThe CPU time
tempdb_allocationsNumber of Tempdb writes done
tempdb_currentNumber of Tempdb pages currently allocated
blocking_session_idThe blocking session Id
readsNumber of reads done
writesNumber of writes done
physical readsNumber of physical reads done
used_memoryThe amount of memory used
statusThe process status
open_tran_countThe number of transactions used
percent_completeThe query completion percentage
host_nameThe host machine name
database_nameThe database name where the query is executed
program_nameThe application that executed the query
start_timeThe process start time
login_timeThe login time
request_idThe request Id
collection_timeThe time that this last select was run

Looking for some additional information? You can obtain more information from the procedure by passing additional parameters. For example, if you want to find information not only for the running user processes but for the system processes as well you can use the following command:

Exec sp_whoisactive @show_system_spids = 1

One of my favourite things about sp_whoisactive is how well it is documented- so much useful information plus all of the additional parameters and their description. Use this command to get the information:

Exec sp_whoisactive @help = 1

Using Azure Data Studio? Then you should check out the sp_whoisactive extension. Search for sp_whoisactive in the market place and install. Connect to your SQL Instance is Azure Data Studio, right-click on the instance and select Manage. The sp_whoisactive output is in a graphical format and by clicking on any of the blocks you will automatically get a query that includes all required parameters.

sp_whoisactive has helped me so many times dealing with performance issues. I strongly recommend adding it to your collection of tools – it will make your life easier!

3 thoughts on “T-SQL Tuesday #135: The outstanding tools of the trade that make your job awesome

  1. Pingback: T-SQL Tuesday #135: The outstanding tools of the trade that make your job awesome - write-up - Mikey Bronowski - Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s