Tag Archives: DMV

My good friend Allen White is hosting this months installment of #TSQL2sDay so I am motivated to jump in. #TSQL2sDay is the creation of Adam Machanic. The concept is simple, about a week before the second Tuesday of the month a theme will be posted.  Any blogger that wishes to participate is invited to write a post on the chosen topic and any post that is related to both SQL Server and the theme is fair game.The challenge for this month’s T-SQL Tuesday is: What T-SQL tricks do you use today to make your job easier?

What’s Currently Running?

One of my favorite tricks is actually just a little script I have in my toolbox to find out what queries are currently running right now. In fact I have had quite a few people ask me the for this script so I am glad to share it in this blog post.  With SQL Server 2005 and above  SQL Server provides Database Management Views that give you direct access to executing requests and running process. The following query uses sys.dm_exec_request, sys.sysprocesses. We will also use cross apply to get the query text from sys.dm_exec_sql_text and the execution plan from sys.dm_exec_query_plan.

The Good Stuff…

{UPDATE: 1/1/2012 – Replaced sysprocesses with sys.dm_exec_sessions as recommended by Phil in the comments}

-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT [Spid] = sp.session_Id
,er.request_id
,er.command
,[Database] = DB_NAME(er.database_id)
,[User] = login_name
,er.blocking_session_id
,[Status] = er.status
,[Wait] = wait_type
,CAST('<?query --'+CHAR(13)+SUBSTRING(qt.text,
(er.statement_start_offset / 2)+1,     ((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)    ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)+CHAR(13)+'--?>' AS xml) as sql_statement
,[Parent Query] = qt.text
,p.query_plan
,er.cpu_time
, er.reads
, er.writes
, er.Logical_reads
, er.row_count
, Program = program_name
,Host_name
,start_time
FROM sys.dm_exec_requests er INNER JOIN sys.dm_exec_sessions sp ON er.session_id = sp.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
cross apply sys.dm_exec_query_plan(er.plan_handle) p
WHERE sp.is_user_process = 1
/* sp.session_Id > 50
-- Ignore system spids. -- */
AND sp.session_Id NOT IN (@@SPID)
ORDER BY 1, 2

Yes, every once in a while when I am deploying an application I ask myself the following question.  What database objects (tables, stored procedures, functions etc..) did I modify with this release?  

Ideally this is documented in the release plan but I will admit I have been known to slip every once in a while.  Therefore, I am showcasing a query that can provide help.  This query was written by Gordon Bell and it can be found here.   It uses the sys.objects DMV that are included in SQL 2005 & 2008. 

I will defiantly throw this script into my bag of tricks. 

select name, modify_date,
case when type_desc = 'USER_TABLE' then 'Table'
when type_desc = 'SQL_STORED_PROCEDURE' then 'Stored Procedure'
when type_desc in ('SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION',
'SQL_TABLE_VALUED_FUNCTION') then 'Function'
end as type_desc
from sys.objects
where type in ('U', 'P', 'FN', 'IF', 'TF')
and is_ms_shipped = 0
order by 2 desc