Run SP_WhoIsActive in SSIS package to collect data from different SQL Server Instances

Run SP_WhoIsActive in SSIS package to collect data from different SQL Server Instances

Sometime back I have already written about world famous stored procedure “SP_WhoIsActive” created by SQL Server GURU Mr. Adam Machanic (Blog). I used to have this SP in all of my important production server to keep an eye on the query performance. This SP returns huge amount of data based on the workload on the SQL Server so I always wish to maintain separate database in separate server for DBA related activity to keep production database free from DBA related activity as much as possible.

Note:

I have provided link of new version of “SP_WhoIsActive” in the first paragraph of this article but I am still using little older SP as I have already set it up in many of my servers.

I am not able to find that old SP from the blog of Mr. Adam Machanic and hence I am providing it here for the reference of my blog reader. Please download it from here as I have demonstrated this article based on the SP I am using.

I have created one SSIS package which is hosted on my DBA server and executes “SP_WhoIsActive” in each instance of production server in every few seconds or minutes, collects the data from “ SP_WhoIsActive” and stores those data in DBA database. My DBA database is named “DBADB”.

I assume that you already have “SP_WhoIsActive” in one of your production database in one SQL Server Instance and creating package for that. I also assume that you have “DBADB” database which collects and stores data.

Let us create package then:

Create one SSIS project and take “Execute SQL Task” in package.

Have some variables declaration for your package.

1VariableInSSISPackage

Se the connection of “Execute SQL Task” with database of your production server where you have created “SP_WhoIsActive” stored procedure. Once connection is made, have the following TSQL in “Execute SQL Task”.

IF OBJECT_ID('tempdb..#ActiveProcesses') IS NOT NULL
DROP TABLE #ActiveProcesses

CREATE TABLE #ActiveProcesses(
[dd hh:mm:ss.mss] [varchar](15) NULL,
[dd hh:mm:ss.mss (avg)] [varchar](15) NULL,
[session_id] [smallint] NOT NULL,
[sql_text]  NULL,
[sql_command]  NULL,
[login_name] [nvarchar](128) NOT NULL,
[wait_info] [nvarchar](4000) NULL,
[CPU] [varchar](30) NULL,
[tempdb_allocations] [varchar](30) NULL,
[tempdb_current] [varchar](30) NULL,
[blocking_session_id] [smallint] NULL,
[blocked_session_count] [varchar](30) NULL,
[reads] [varchar](30) NULL,
[writes] [varchar](30) NULL,
[physical_reads] [varchar](30) NULL,
[query_plan]  NULL,
[locks]  NULL,
[used_memory] [varchar](30) NULL,
[status] [varchar](30) NOT 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,
[additional_info]  NULL,
[start_time] [datetime] NOT NULL,
[request_id] [int] NULL,
[collection_time] [datetime] NOT NULL,
[seq] [int] IDENTITY(1,1) NOT NULL
)

EXEC sp_WhoIsActive @destination_table='#ActiveProcesses'

SELECT
[dd hh:mm:ss.mss],
[dd hh:mm:ss.mss (avg)],
[session_id],
CONVERT(nvarchar(max),[sql_text]) AS [sql_text],
CONVERT(nvarchar(max),[sql_command]) AS [sql_command],
[login_name],
[wait_info],
[CPU],
[tempdb_allocations],
[tempdb_current],
[blocking_session_id],
[blocked_session_count],
[reads],
[writes],
[physical_reads],
CONVERT(nvarchar(max),[query_plan]) AS [query_plan],
CONVERT(nvarchar(max),[locks]) AS [locks],
[used_memory],
[status],
[open_tran_count],
[percent_complete],
[host_name],
[database_name],
[program_name],
CONVERT(nvarchar(max),[additional_info]) AS [additional_info],
[start_time],
[request_id],
[collection_time],
[seq]
FROM #ActiveProcesses

Here you can find the screen capture of

2ExecuteSQLTaskGeneralProperty

Go to result set property of “Execute SQL Task” and take the output of SELECT query into one dataset variable we have created.

3Dataset

Now, take one Foreach loop task and iterate it for “ADO” with the dataset we have created.

4ForEachADOIterate

Now we have to map the variable to get the value from dataset in Foreach Loop task.

5VariableMapping

Have one more “Execute SQL Task”, inside the “foreach loop taks”, which insert row from dataset to “DBADB” database. Here is the TSQL which we used to use in “Execute SQL Task”.

insert into dba.ActiveProcesses
(
[dd hh:mm:ss.mss],
[dd hh:mm:ss.mss (avg)],
[session_id],
[sql_text],
[sql_command],
[login_name],
[wait_info],
[CPU],
[tempdb_allocations],
[tempdb_current],
[blocking_session_id],
[blocked_session_count],
[reads],
[writes],
[physical_reads],
[query_plan],
[locks],
[used_memory],
[status],
[open_tran_count],
[percent_complete],
[host_name],
[database_name],
[program_name],
[additional_info],
[start_time],
[request_id],
[collection_time]
)
select ?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?

6ExecuteSQLforDBADB

Once you set SQLStatement in “Execute SQL Task”, go to “Parameter Mapping” and set variables there.

7ParameterMappingForDBADB

Now, you are absolutely ready to schedule your SSIS package in JOB or in Schedule task which get executed at every few seconds and collect the data for you in DBADB database which you can analyze at your convenience.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

3 thoughts on “Run SP_WhoIsActive in SSIS package to collect data from different SQL Server Instances

Comments are closed.