Saturday, July 12, 2014

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






 




Like and Share to SQL Integrity Blog

No comments:

Post a Comment

Thank You !!!!