We doing the stress test where replication hits about 22 minutes of latency. The threshold limits is 15 mins. How to analysis and find the Root cause.
Ran below query and find the output details in the attachment. I personally feel there is I/O issue. Avg.disk queue lenght is about 40. But when we escalated to SAN team, they said this is normal. But in internet blogs says ,avg.disk queue length shouldn't be morethan 2 .
Please help me out of trouble.
SELECT r.session_id ,
SUBSTRING(qt.[text], r.statement_start_offset / 2,
( CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
END - r.statement_start_offset ) / 2) AS [statement_executing] ,
DB_NAME(qt.[dbid]) AS [DatabaseName] ,
OBJECT_NAME(qt.objectid) AS [ObjectName] ,
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
WHERE r.session_id > 50
ORDER BY r.scheduler_id
My question :
Database has around 20 filegroups and each has 1/2 files in it.
How find which files is causing this issue.
Most of time the time writelog thread goes into suspended state.What does it mean?