January 9, 2017 at 12:48 am
Hi All,
I am using Adam Mechanic's sp_whoisactive stored procedure to capture resource intensive queries.
My requirement is, I want to capture queries consuming tempdb_allocations > 100MB.
What would be the values for @filter_type = ? and @filter = ? in sp_whoisactive stored proc?
How to do that??
Basically, I want to store the results into a table as below.
EXEC dbo.sp_WhoIsActive
@show_sleeping_spids= 1,
@get_plans =1,
@get_transaction_info =1,
@get_outer_command =1,
@get_additional_info=1,
@get_task_info=1,
@get_locks=1,
@find_block_leaders=1
--,@filter_type = 'session', @filter = '87', -- specify the spid
--,@destination_table = 'dbo.WIA_Output';
go
Thanks,
Sam
January 9, 2017 at 2:05 am
Sam
You can't filter on that, as far as I can tell. Why not filter out the rows you don't need from the destination table instead?
John
January 9, 2017 at 5:34 am
Hi John,
Thanks for the response.
Yeah, I can do that but I want to limit those rows before itself.
January 9, 2017 at 10:38 am
For what it's worth, a few days back, I posted a script to query details about objects are allocated in TEMPDB, including the SPID, application, procedure name, and SQL statement that allocated it.
http://www.sqlservercentral.com/scripts/tempdb/151252/
SET LOCK_TIMEOUT 10000;
SET DEADLOCK_PRIORITY LOW;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
USE tempdb;
SELECT * FROM
(
SELECT DISTINCT
DB_NAME() AS DatabaseName
, ps.object_id AS ObjectID
, o.type_desc AS ObjectType
, o.name AS ObjectName
, o.create_date AS ObjectCreated
, si.name AS IndexName
, CASE si.index_id
WHEN 0 THEN 'HEAP'
WHEN 1 THEN 'CLUSTERED'
ELSE 'NONCLUSTERED'
END AS IndexType
, ps.row_count AS RowsCount
, ((ps.reserved_page_count * 8024) / 1024 / 1024) AS ReservedMB
, trace.SPID
, er.start_time AS RequestStartTime
, trace.ApplicationName
, OBJECT_NAME( qt.objectid, qt.dbid ) AS ProcedureName
, SUBSTRING(CHAR(13) + SUBSTRING (qt.text,(er.statement_start_offset/2) + 1
,((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)
,1,8000) AS StatementText
, trace.HostName
, trace.LoginName
FROM sys.dm_db_partition_stats ps
JOIN sys.tables AS o ON o.object_id = ps.OBJECT_ID AND o.is_ms_shipped = 0
LEFT JOIN sys.indexes si ON si.object_id = o.object_id AND si.index_id = ps.index_id
LEFT JOIN
(
SELECT HostName, LoginName, SPID, ApplicationName, DatabaseName, ObjectID
, ROW_NUMBER() OVER (PARTITION BY ObjectID ORDER BY StartTime DESC) MostRecentObjectReference
FROM fn_trace_gettable(
(
SELECT LEFT(path, LEN(path)-CHARINDEX('\', REVERSE(path))) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1
), DEFAULT)
WHERE ObjectID IS NOT NULL
) trace
ON trace.ObjectID = ps.object_id
AND trace.DatabaseName = 'tempdb'
AND MostRecentObjectReference = 1
LEFT JOIN sys.dm_exec_requests AS er
ON er.session_id = trace.spid
OUTER APPLY sys.dm_exec_sql_text( er.sql_handle) AS qt
) AS T
WHERE ReservedMB > 0
ORDER BY ReservedMB DESC;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply