October 28, 2008 at 9:39 am
You will need 'VIEW SERVER STATE' permission to run the query that is in the proc.
October 28, 2008 at 9:54 am
This was a good one , Helped me quite a bit.
October 28, 2008 at 10:26 am
Very nice article and very useful query.
Just out of curiosity, why did you decide to make it a procedure instead of a view?
Naturally you would lose that order by, but since you may wish to see it ordered by other things such as User or Status anyway it seems the view would be more flexible.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
October 28, 2008 at 10:48 am
Hi,
I’m glad you liked the article, it’s quite interesting to see what is happening with SQL Server and SQL.
I hadn’t really thought about creating a view, I typically create utilities as procedures by default, but maybe the advantages of permissioning and sorting make a view more appropriate…
Thanks
Ian
October 28, 2008 at 2:17 pm
I am planning on using a bit of a modified version that will filter to a specific DB. As a developer I am in need of just seeing what is on the DB I am working on. Here is a crack at a revision to the code to allow this.
IF OBJECT_ID(N'[dbo].[dba_WhatSQLIsExecuting]') IS NULL
BEGIN
EXEC ('CREATE PROCEDURE
[dbo].[dba_WhatSQLIsExecuting]
AS BEGIN SELECT ''STUB'' END');
END;
GO
ALTER PROC [dbo].[dba_WhatSQLIsExecuting]
--Inputs
@specificDB nvarchar(128) = NULL
AS
/*--------------------------------------------------------------------
Purpose: Shows what individual SQL statements are currently executing.
----------------------------------------------------------------------
Parameters: None.
Revision History:
24/07/2008 Ian_Stirk@yahoo.com Initial version
28/10/2008 added filter for a specific DB
Example Usage:
To get data from all DBs on that instance
1. exec YourServerName.master.dbo.dba_WhatSQLIsExecuting
To get data from a specific DB
2. DECLARE @thisDB nvarchar(128); SET @thisDB = DB_NAME();
exec YourServerName.master.dbo.dba_WhatSQLIsExecuting @thisDB
---------------------------------------------------------------------*/
BEGIN
DECLARE @thisSPID int;
SET @thisSPID = @@SPID;
-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- What SQL Statements Are Currently Running?
SELECT
session_Id [SPID]
,ecid [ecid]
,DB_NAME(sp.dbid) [Database]
,nt_username [User]
,er.status [Status]
,wait_type [Wait]
,SUBSTRING (qt.text,
er.statement_start_offset/2,
(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)
[Individual Query]
,qt.text [Parent Query]
,program_name [Program]
,Hostname
,nt_domain
,start_time
FROM sys.dm_exec_requests [er]
INNER JOIN
sys.sysprocesses [sp]
ON er.session_id = sp.spid
CROSS APPLY
sys.dm_exec_sql_text(er.sql_handle) [qt]
WHERE session_Id > 50 -- Ignore system spids.
AND session_Id NOT IN (@thisSPID) -- Ignore this current statement.
--when a specific DB is supplied filter for it, ohterwise get all DBs
AND CASE WHEN @specificDB IS NULL THEN N'' ELSE DB_NAME(sp.dbid) END
= CASE WHEN @specificDB IS NULL THEN N'' ELSE @specificDB END
ORDER BY session_Id, ecid
END
October 28, 2008 at 4:28 pm
Great code, thanks!
I would also propose creating a view for this instead of a proc. It fits in with the DMVs provided by SQL and allows for easier joining to other system data and extensibility (ORDER BY, Filtering by DB, etc.)
thanks,
Creighton
October 28, 2008 at 11:08 pm
Very useful and relevant article ...:)
October 29, 2008 at 9:18 pm
If you want to use this query going forward you should join on sys.dm_exec_sessions instead of sys.sysprocesses. This is a backwards compatibility view and is scheduled for deprecation. I wrote a similar article about 2 years ago for SSWUG (Custom Scripts to get DMV results fast). It shows you how to pull the query plan for currently executing requests as well for further debug.
For more info use: sys.dm_exec_query_stats which has a plan_handle to do a cross apply on sys.dm_exec_query_plan to get the XML format of the execution plan.
You can look all this up in books online.
November 5, 2008 at 3:23 am
It really helped me.
Thank you very much 🙂
Regards, Babu
November 28, 2008 at 11:45 pm
Very interesting and knowledge increasing article.
Thanks.
Chandresh Kumar Chhatlani
3 PA 46
Prabhat Nagar
Sector-5, Hiran Magari
UDAIPUR (Rajasthan) - India
313002
December 11, 2009 at 8:49 am
I've just read this article, copied the code and created the SP in my master database. Using the code I created the SP dba_WhatSQLIsExecuting, and ran it. However, nothing showed up. Is it because I ran it, in the master database? Do I have to be in one of our user databases in order for something to show up?
Kindest Regards, Rod Connect with me on LinkedIn.
December 11, 2009 at 9:11 am
No it's not because you ran it in the master db - it will report activity across all databases.
It could be that nothing was actually running at that point. Try running over and over to see if you get anything.
Alternatively remove the line that ignores the current statement - then at least you should see yourself.
December 11, 2009 at 12:08 pm
If I'm wondering what's running, I usually want a little extra information about those queries. Here's a very similiar version with some of the extra information I like to see:
set transaction isolation level read uncommitted
select s.[host_name]
, s.login_name
-- , s.is_user_process
, s.program_name
, r.command
, r.status
, s.session_id
, r.blocking_session_id
-- , r.request_id
, datediff( ms, r.start_time, getDate() ) as age_ms
, r.total_elapsed_time
, r.cpu_time
, r.percent_complete
, r.row_count
, r.granted_query_memory
, r.logical_reads
, txt.[text] as query_text
from sys.dm_exec_requests as r
join sys.dm_exec_sessions as s on (s.session_id=r.session_id)
cross apply sys.dm_exec_sql_text( r.sql_handle ) as txt
where s.is_user_process=1 AND s.session_Id NOT IN (@@SPID)
order by datediff( ms, r.start_time, getDate()) desc
December 11, 2009 at 1:30 pm
kevriley (12/11/2009)
No it's not because you ran it in the master db - it will report activity across all databases.It could be that nothing was actually running at that point. Try running over and over to see if you get anything.
Alternatively remove the line that ignores the current statement - then at least you should see yourself.
OK, I did as you suggested, and at first didn't see any activity. But it is Friday, not all of our users are here, and so I decided to get into one of our apps and run a Crystal Report there that I know is a hog. Sure enough, that SP started showing me the line being executed, what database, etc. Cool. I like it!
Kindest Regards, Rod Connect with me on LinkedIn.
December 11, 2009 at 2:51 pm
Thanks for the script. I can see that it will be useful.
This is the kind of script that I will create as a .sql file in a regular network directory for sql scripts. That way you can execute it on any one of your servers just by pulling it up in Management Studio and you can easily change it as needed and either save the changes or not. If you make it a stored proc or a view, then you are limited to one server or you have to create and maintain it in many places.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply