June 21, 2013 at 8:40 am
What is the useful dmv's as dba needs in daily life ?
DMVto check the blocking other than sp_who2
June 21, 2013 at 8:45 am
Used to use sys.dm_os_wait_stats a lot before we got Idera - the reality is though that I use so many of them!!! Index usage stats and physical stats for fragmentation and usage reports, erm dm_os_performance counters, all sorts! 🙂
I guess like many, I've gathered a load of scripts over the years that follow me everywhere that get continually used in day to day admin,
heres a blocking one for example:
SELECT
s.spid, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid),
s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid, s.dbid), Definition = CAST(text AS VARCHAR(MAX))
INTO #Processes
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE
s.spid > 50;
WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow)
AS
( SELECT
s.SPID, s.BlockingSPID, s.Definition,
ROW_NUMBER() OVER(ORDER BY s.SPID),
0 AS LevelRow
FROM
#Processes s
JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
WHERE
s.BlockingSPID = 0
UNION ALL
SELECT
r.SPID, r.BlockingSPID, r.Definition,
d.RowNo,
d.LevelRow + 1
FROM
#Processes r
JOIN Blocking d ON r.BlockingSPID = d.SPID
WHERE
r.BlockingSPID > 0 )
SELECT * FROM Blocking
ORDER BY RowNo, LevelRow
drop table #Processes
'Only he who wanders finds new paths'
June 29, 2013 at 4:28 pm
Download sp_WhoIsActive and follow the 30-day series and not only may you never use sp_who2 again but you'll learn a ton about DMOs in the process.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply