SQLStarters

This is definitely one of the most used tools in my toolkit. Besides being incredibly useful when you need to figure out issues like blocking and locking and what the transactions are doing, you should take a few long moments to look at the code. The code extensively commented making it easy to follow and to understand what different parameters do. Also Adam has some serious T-SQL skills so going through the code is a great way to improve your own skills as well!

Installation

It really can’t get any more simple than it is. You download the latest version by going here. Then you run the script against a database you want to install it, which is by default master, and you’re done. My personal recommendation is to create a new database called Utilities, DBATools or something similar and install it there rather than use system databases.

Usage

The most basic use is to just execute the Stored Procedure and it’ll give you a whole load of relevant information about transactions going on in your server. The basic version will give you information about blocking, for example, how long have the queries been running, how much I/O they have used, etc. With parameters you can get outer and inner T-SQL, plans, detailed lock information and plenty of other information. Since this is just a review, I’ll not go into all the details but point you here, but behind the link above you can also find Adams 30 part blog series about sp_WhoIsActive. Yeah, you read that right, a 30 part blog series about features.

My favorite features

One of my favorite features in sp_WhoIsActive is the ability to use it to capture data and then save it to database. As an added bonus the stored procedure will also generate the T-SQL to create a table to hold the information, how cool is that! This is also very simple to do, here’s a quick example how to do it when you want to capture transaction information (such as transaction log writes etc).

DECLARE @schema VARCHAR(MAX) EXEC sp_WhoIsActive @get_transaction_info = 1, @return_schema = 1, @schema = @schema OUTPUT SELECT REPLACE(@schema, ‘<table_name>’,’TransactionInfo’)

This will generate a CREATE TABLE statement such as this:

CREATE TABLE TransactionInfo (
[dd hh:mm:ss.mss] varchar(8000) NULL,
[session_id] smallint NOT NULL,
[sql_text] xml NULL,
[login_name] nvarchar(128) NOT NULL,
[wait_info] nvarchar(4000) NULL,
[tran_log_writes] nvarchar(4000) NULL,
[CPU] varchar(30) NULL,
[tempdb_allocations] varchar(30) NULL,
[tempdb_current] varchar(30) NULL,
[blocking_session_id] smallint NULL,
[reads] varchar(30) NULL,
[writes] varchar(30) NULL,
[physical_reads] varchar(30) NULL,
[used_memory] varchar(30) NULL,
[status] varchar(30) NOT NULL,
[tran_start_time] datetime NULL,
[open_tran_count] varchar(30) NULL,
[percent_complete] varchar(30) NULL,
[host_name] nvarchar(128) NULL,
[database_name] nvarchar(128) NULL,
[program_name] nvarchar(128) NULL,
[start_time] datetime NOT NULL,
[login_time] datetime NULL,
[request_id] int NULL,
[collection_time] datetime NOT NULL
)

To send results to table, just use @destination_table parameter. Pretty nice, huh? One thing to notice is that the table is created without any indexes, so you might want to consider adding one if you’re planning on collecting lot of information. You still need to collect data though. This can be basically done in few different ways, such as creating a WHILE loop to run it for a time or using SQL Server Alerts to run it as a response to performance condition.

You can and will get a lot of information out of sp_WhoIsActive even when running it without one of the many parameters. To help with deciphering all that Adam has included a parameter aptly named @help. So running:

EXEC sp_WhoIsActive @help=1

gets you, you probably guessed this already, help! This includes printing out the header as well as the parameters and returned columns with explanations on what they mean.