Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

What do you use to find out what's slowing down your instance right now Expand / Collapse
Posted Wednesday, January 22, 2014 2:19 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, November 11, 2015 7:38 AM
Points: 576, Visits: 1,700
This is not a DMV I wrote (can't remember where I found it, honestly) but it is the 1st thing I ran to find out what query is slowing down my system:

SELECT [Spid] = session_Id
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text, er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
ELSE er.statement_end_offset END - er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, 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) as qt
WHERE session_Id > 50 -- Ignore system spids.
AND session_Id NOT IN (@@SPID) -- Ignore this current statement.

I like it because it shows the spid plus the actual T-SQL statement.
Post #1533833
Posted Wednesday, January 22, 2014 4:56 PM



Group: General Forum Members
Last Login: Yesterday @ 12:21 PM
Points: 38,950, Visits: 36,039
GilaMonster (1/21/2014)
sys.dm_exec_requests join sys.dm_exec_sessions cross apply sys.dm_exec_sql_text.

That and a quick trip to the desk of the person who invoked the rogue query along with the appropriate pork chop launcher and a bat to help tenderize the target.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Favorite Quotes:
"Has anyone ever told you that a query you have written runs too fast?" - Dwain Camps - 6 Mar 2014

Helpful Links:
How to post code problems
How to post performance problems
Post #1533881
Posted Thursday, January 23, 2014 2:47 AM



Group: General Forum Members
Last Login: Yesterday @ 11:39 PM
Points: 43,442, Visits: 40,570
sql-lover (1/22/2014)

WHERE session_Id > 50 -- Ignore system spids.

Careful, spid < 50 indicating a system process hasn't been true since SQL 2000.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1533949
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse