BlitzWho, oh how I love thee! This stored procedure is, by far, my favorite way of figuring out who is doing what on my servers (so that I can ask why).
I recently needed a little bit more out of it, though. I was working with a vendor who needed to understand degraded performance in our environment and wanted these sorts of metrics at different times in the process, and as quickly as possible. I didn’t want to count on being constantly available to run it at the drop of a hat, and I really didn’t want to have to compile all the different results manually into one place. I needed to automate this to run every 30 seconds and send the output to the table of my choice, freeing me up to just monitor the processes and be available to answer questions.
This is the point where I need to confess that I was running an outdated edition of the sproc (the latest and greatest one includes the export to table option already). That day though, I didn’t have time to go out and get the newest version, get it reviewed, get it approved, get it through change management and get it into production. I had to think fast.
Here is what I did. First, I promised myself that I would update BlitzWho at the first opportunity. While I hope you are keeping more current on this much-loved stored procedure than I was, this trick can be used for any sproc where you need to put the output into a table and don’t want to go through the pain of parsing the code (along with eleventy billion CONVERTs), only to find that you wind up with an error converting varchar to INT. I love that one. You usually only have n hundred varchar columns to go through to find the offender.
First, I am going to do something to keep the AdventureWorks2012 database busy. (Yes, that needs to be upgraded as well. It’s on the list.) I quickly wrote a loop to do table row counts over and over for tables with over 5000 rows. That ought to do it….
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#IndexInfo') IS NOT NULL
DROP TABLE #IndexInfo;
IF OBJECT_ID('tempdb..#HoldingTable') IS NOT NULL
DROP TABLE #HoldingTable;
IF OBJECT_ID('tempdb..#RowCountTablePrelim') IS NOT NULL
DROP TABLE #RowCountTablePrelim;
IF OBJECT_ID('tempdb..#RowCountTableFinal') IS NOT NULL
DROP TABLE #RowCountTableFinal;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
CREATE TABLE #RowCountTablePrelim
(
TableName sysname NULL,
[RowCount] BIGINT NULL
);
CREATE TABLE #RowCountTableFinal
(
TableName sysname NULL,
[RowCount] BIGINT NULL
);
First, let's grab the row counts for all the tables on the server. We only want tables big enough to consider (> 5000 rows). This will spit out separate row counts for each table partition as a separate entry, but we'll deal with that in a minute.
USE AdventureWorks2012;
GO
DECLARE @i INT = 0;
WHILE @i < 200
INSERT INTO #RowCountTablePrelim
SELECT '[' + DB_NAME() + N']' + '.' + '[' + SCHEMA_NAME(schema_id) + ']' + '.' + '[' + t.name + ']' AS TableName,
SUM(ps.row_count) AS [RowCount]
FROM sys.tables AS t
INNER JOIN sys.dm_db_partition_stats AS ps
ON t.object_id = ps.object_id
AND ps.index_id < 2
AND ps.row_count > 5000
GROUP BY t.schema_id,
t.name,
ps.row_count;
SET @i = @i + 1;
Let's clean up the counts by adding all the partition counts for each table into one final number.
INSERT INTO #RowCountTableFinal
(
TableName,
[RowCount]
)
SELECT TableName,
SUM([RowCount])
FROM #RowCountTablePrelim
GROUP BY ROLLUP(TableName);
SELECT *
FROM #RowCountTablePrelim
ORDER BY [RowCount] DESC;
DROP TABLE #RowCountTableFinal;
DROP TABLE #RowCountTablePrelim;
I fired that off, then ran sp_BlitzWho.
DECLARE @return_value INT ; EXEC @return_value = [dbo].[sp_Blitzwho] @expertMode = 1; GO
Right click the box in your results to the left of the first column and choose Script as INSERT.
You will get something like this:
Your CREATE TABLE is right there now for you in the first line. Instead of a temp table, let’s create it as a permanent one:
CREATE TABLE dbo.BlitzWhoResultsTable
(
[run_date] DATETIME,
[elapsed_time] VARCHAR(41),
[session_id] SMALLINT,
[database_name] NVARCHAR(128),
[query_text] NVARCHAR(MAX),
[query_plan] XML,
[live_query_plan] XML,
[query_cost] FLOAT(8),
[status] NVARCHAR(30),
[wait_info] NVARCHAR(MAX),
[top_session_waits] NVARCHAR(MAX),
[blocking_session_id] SMALLINT,
[open_transaction_count] INT,
[is_implicit_transaction] INT,
[nt_domain] NVARCHAR(128),
[host_name] NVARCHAR(128),
[login_name] NVARCHAR(128),
[nt_user_name] NVARCHAR(128),
[program_name] NVARCHAR(128),
[fix_parameter_sniffing] NVARCHAR(150),
[client_interface_name] NVARCHAR(32),
[login_time] DATETIME,
[start_time] DATETIME,
[request_time] DATETIME,
[request_cpu_time] INT,
[request_logical_reads] BIGINT,
[request_writes] BIGINT,
[request_physical_reads] BIGINT,
[session_cpu] INT,
[session_logical_reads] BIGINT,
[session_physical_reads] BIGINT,
[session_writes] BIGINT,
[tempdb_allocations_mb] DECIMAL(38, 2),
[memory_usage] INT,
[estimated_completion_time] BIGINT,
[percent_complete] REAL,
[deadlock_priority] INT,
[transaction_isolation_level] VARCHAR(33),
[degree_of_parallelism] SMALLINT,
[last_dop] BIGINT,
[min_dop] BIGINT,
[max_dop] BIGINT,
[last_grant_kb] BIGINT,
[min_grant_kb] BIGINT,
[max_grant_kb] BIGINT,
[last_used_grant_kb] BIGINT,
[min_used_grant_kb] BIGINT,
[max_used_grant_kb] BIGINT,
[last_ideal_grant_kb] BIGINT,
[min_ideal_grant_kb] BIGINT,
[max_ideal_grant_kb] BIGINT,
[last_reserved_threads] BIGINT,
[min_reserved_threads] BIGINT,
[max_reserved_threads] BIGINT,
[last_used_threads] BIGINT,
[min_used_threads] BIGINT,
[max_used_threads] BIGINT,
[grant_time] VARCHAR(20),
[requested_memory_kb] BIGINT,
[grant_memory_kb] BIGINT,
[is_request_granted] VARCHAR(39),
[required_memory_kb] BIGINT,
[query_memory_grant_used_memory_kb] BIGINT,
[ideal_memory_kb] BIGINT,
[is_small] BIT,
[timeout_sec] INT,
[resource_semaphore_id] SMALLINT,
[wait_order] VARCHAR(20),
[wait_time_ms] VARCHAR(20),
[next_candidate_for_memory_grant] VARCHAR(3),
[target_memory_kb] BIGINT,
[max_target_memory_kb] VARCHAR(30),
[total_memory_kb] BIGINT,
[available_memory_kb] BIGINT,
[granted_memory_kb] BIGINT,
[query_resource_semaphore_used_memory_kb] BIGINT,
[grantee_count] INT,
[waiter_count] INT,
[timeout_error_count] BIGINT,
[forced_grant_count] VARCHAR(30),
[workload_group_name] NVARCHAR(128),
[resource_pool_name] NVARCHAR(128),
[context_info] VARCHAR(128)
);
Now, the only thing you need is to put the following into a job to run however frequently you need it:
USE AdventureWorks2012; INSERT INTO dbo.BlitzWhoResultsTable EXEC [blitz].[sp_BlitzWho] @ExpertMode = 1;
It will work beautifully, and better yet, it’s very quick and easy to set up! Hope this helps. Even better, don’t be like me. Keep your BlitzWho up to date!
