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.mss||Process elapsed time|
|session_id||The process session id|
|sql_text||The currently running SQL command|
|login_name||The login name associated with the process|
|wait_info||The process wait information (aggregated)|
|CPU||The CPU time|
|tempdb_allocations||Number of Tempdb writes done|
|tempdb_current||Number of Tempdb pages currently allocated|
|blocking_session_id||The blocking session Id|
|reads||Number of reads done|
|writes||Number of writes done|
|physical reads||Number of physical reads done|
|used_memory||The amount of memory used|
|status||The process status|
|open_tran_count||The number of transactions used|
|percent_complete||The query completion percentage|
|host_name||The host machine name|
|database_name||The database name where the query is executed|
|program_name||The application that executed the query|
|start_time||The process start time|
|login_time||The login time|
|request_id||The request Id|
|collection_time||The 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!