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”.

[sourcecode language=”sql”]
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] [xml] NULL,
[sql_command] [xml] 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] [xml] NULL,
[locks] [xml] 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] [xml] 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
[/sourcecode]

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”.

[sourcecode language=”sql”]
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 ?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?
[/sourcecode]

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.

Keep eye on the health of your SQL Server database

HealthKeep eye on the health of your SQL Server database

SP_WhoIsActive” stored procedure is one of my favorite SP which I used to create in my almost all production database from the day one since last few years. A very BIG THANKS goes to Mr. Adam Machanic (Blog) who is very well-known celebrity in the field of SQL Server since long. He is one of my favorite author and I used to follow his blog very closely.

Today I have created “SP_WhoIsActive” in one more database and thought to salute original creator of this wonderful SP.

I can not keep watch on all my databases on 24*7 basis but “SP_WhoIsActive” do it for me. Whenever I get any complain about slow running query/SP I ask for the approx date and time. When I have SP name, Date & Time, I used to query the table which is being populated by “SP_WhoIsActive” for that particular SP along with approx date & time so that I can get following crucial information about SP which it has faced during that time.

–>Session_ID
–>TSQL of the SP
–> Parameter of SP for which it ran
–> Login_Name
–> Wait_Info if there was any
–> CPU
–> TempDB allocation if it had
–>blocking session_id and count if there was any|
–>reads
–>writes
–>physical_reads
–>execution plan of SP
–>database name
–>execution start time and many more important information

Once I have all these information, I can start my debugging. Without having all these information I feel helpless as I can’t watch all databases personally in each instance.

Any DBA can understand the importance of all these information which “SP_WhoIsActive” provides. This is the reason “SP_WhoIsActive” is my one of the favorite SP which I used to have in each of my SQL Server instances.

Once again, I want to give all the credit to Mr. Adam Machanic. Hats off to you Adam as you have spent hundreds of hours for “SP_WhoIsActive” to help SQL Server community.

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 server.

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.

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

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