SQL Server: Important DMV scripts to troubleshoot the issue in SQL Server

Please find the below important DMV scripts to troubleshoot the issue in SQL Server.

sys.dm_exec_requests

sys.dm_exec_sql_text

sys.dm_os_waiting_task

sys.dm_os_wait_stats

sys.dm_exec_sessions 

sys.dm_tran_locks

--Here is a sample script that shows wait information and the T-SQL currently running in each session where available:

SELECT      er.session_id,

            er.database_id,

            er.blocking_session_id,

            er.wait_type,

            er.wait_time,

            er.wait_resource,

            st.text

FROM sys.dm_exec_requests er

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

--Here is a sample script that shows all the information for waiting tasks with the T-SQL currently running where there is a session_id available:

SELECT      wt.*,

      st.text

FROM sys.dm_os_waiting_tasks wt LEFT JOIN sys.dm_exec_requests er

ON wt.waiting_task_address = er.task_address

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

ORDER BY wt.session_id

--The following sample script from Microsoft is a great way to check for cpu pressure by comparing signal wait times (cpu wait) with resource wait times:

Select signalWaitTimeMs=sum(signal_wait_time_ms)

      ,'%signal waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))

      ,resourceWaitTimeMs=sum(wait_time_ms - signal_wait_time_ms)

      ,'%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))

from sys.dm_os_wait_stats

-- wait stats workload script

DBCC sqlperf ('sys.dm_os_wait_stats',clear)

GO

exec usp_loopmarriageupdate

GO

SELECT * FROM sys.dm_os_wait_stats

ORDER BY wait_time_ms DESC

GO

SELECT session_id,cpu_time,total_elapsed_time

FROM sys.dm_exec_sessions

WHERE session_id = @@SPID

--Move your t.log

USE master;

GO

ALTER DATABASE people

MODIFY FILE(NAME = people_log,FILENAME = N'h:\people_log.ldf')

GO

ALTER DATABASE people SET OFFLINE

GO

ALTER DATABASE people SET ONLINE

--Locking demo

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

GO

BEGIN TRANSACTION

SELECT * FROM people

WHERE personid = 'B95212DB-D246-DC11-9225-000E7B82B6DD'

--view the locks

SELECT request_session_id AS Session,

       resource_database_id AS DBID,

       Resource_Type,

       resource_description AS Resource,

       request_type AS Type,

       request_mode AS Mode,

       request_status AS Status

FROM sys.dm_tran_locks

--open a new window

UPDATE people SET dob = 0

-- check the locks again

-- check sys.dm_os_waiting_tasks

SELECT session_id,wait_duration_ms,wait_type,

blocking_session_id,resource_description

FROM sys.dm_os_waiting_tasks

WHERE session_id = 54