August 16, 2019 at 4:47 pm
I have 2000 sp_locks processes and they are waiting on TransName = sqlsource_transform, METADA Res Type
Also the ReqMode is Sch-S and ObjectName is null.
I am not seeing what this is any ideas?
August 16, 2019 at 5:52 pm
It's difficult to tell offhand what is happening on your system. If you are having a lot of blocking happening, here's a query to help find the lead blocker(s) of transactions on your instance:
--ultimate blocking session
SELECT s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.status AS session_status, db_name(s.database_id) AS database_name, s.last_request_start_time, s.last_request_end_time, s.open_transaction_count
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests br ON s.session_id = br.blocking_session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON s.session_id = er.session_id
WHERE er.blocking_session_id IS NULL OR er.blocking_session_id = er.session_id
August 16, 2019 at 6:35 pm
There is no blocking just sp lock has over 2000 entries and so who2 show suspended
August 16, 2019 at 6:58 pm
Can you tell what the session is waiting on? suspended just means it's waiting, likely page I/O
SELECT s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.status AS session_status,
db_name(er.database_id) AS database_name, er.status AS request_status, er.command, er.percent_complete,
er.wait_type, er.wait_time / 1000.0 AS wait_sec, er.last_wait_type, er.wait_resource,
s.memory_usage / 128.0 AS memory_meg, mg.used_memory_kb, mg.granted_memory_kb, mg.requested_memory_kb, mg.ideal_memory_kb,
er.blocking_session_id, bs.host_name AS blocking_host, bs.program_name AS blocking_program, bs.login_name AS blocking_login,
er.transaction_id, er.open_transaction_count,
er.cpu_time / 1000.0 AS cpu_sec, er.total_elapsed_time / 1000.0 AS total_elapsed_sec, er.start_time, er.reads, er.writes, er.logical_reads,
CASE er.transaction_isolation_level WHEN 1 THEN 'ReadUncomitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' ELSE 'Unspecified' END AS isol_level,
OBJECT_NAME(st.objectid, st.dbid) AS object_name, qp.query_plan, er.sql_handle, er.plan_handle, st.[text]
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions s ON er.session_id = s.session_id
LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg ON er.session_id = mg.session_id AND er.request_id = mg.request_id
LEFT OUTER JOIN sys.dm_exec_sessions bs ON er.blocking_session_id = bs.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE s.is_user_process = 1
AND s.session_id <> @@SPID;
August 16, 2019 at 7:28 pm
It completed. I will again tomorrow
August 16, 2019 at 8:03 pm
It may help to look at the execution plan. In the query I posted in the far right there is a column query_plan that would have a hyperlink to look at the plan.
If you know the query or stored procedure that was running, then you might still be able to find the plan in the cache. Here's a query to show the worst read queries, again there is a query_plan column with a hyperlink you can click to see the execution plan.
/* run this in the database you want to analyze reads */
SELECT TOP 25
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.total_worker_time / qs.execution_count / 1000000.0 AS avg_cpu_seconds,
qs.total_worker_time / 1000000.0 AS total_cpu_seconds,
qs.total_logical_reads, qs.total_logical_writes,
qs.total_logical_reads / qs.execution_count AS average_logical_reads,
qs.total_logical_writes / qs.execution_count AS average_logical_writes,
qs.execution_count, qs.last_execution_time, qs.creation_time,
OBJECT_SCHEMA_NAME(qt.objectid, qt.dbid) AS schema_name, OBJECT_NAME(qt.objectid, qt.dbid) AS object_name, o.modify_date,
qp.query_plan, qs.sql_handle, qs.plan_handle,
DB_NAME(qt.dbid) AS database_name--, qt.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
WHERE qt.dbid = DB_ID()
ORDER BY average_logical_reads DESC;
August 17, 2019 at 1:31 pm
Thanks, I was able to run the two above during the busy times, and I could see all the suspending threads, and then what it was waiting for as I got the wait_type and the page_id, data_file_id, page_number from this and then did a DBCC Page on command
to get the database, and Table etc.
That is a better way to figure out what is going on at a glance, as you see all the processes, and what they are waiting for.
There was a lot running processes 20+ with status of runnable and then 300 with suspended.
How can you tell that your max out and this is why they are being suspended, I know it maybe that your waiting on a table that is being updated as this is in sp_lock
Also, I am going to write this into a table every 5 minutes during the hours of 2 am and 5 am. Then I can view it in the morning at 8
Is that something you do ?
August 21, 2019 at 4:02 pm
I was able to find the issue and moved a sp into another window and the time to run when from 3 hours to 4 minutes.
Amazing
Thanks
August 21, 2019 at 7:40 pm
I've typically only used these queries when there is a problem happening at the moment instead of constantly. I have other software doing general performance monitoring that I look at to determine that there is a problem right now.
To answer your question about knowing what items are being waited on because of locks, this might be helpful:
SELECT tl.request_session_id, tl.resource_type, tl.request_mode, h1.TEXT AS request_text,
wt.blocking_session_id, OBJECT_NAME(p.OBJECT_ID) blocked_object_name, h2.TEXT AS blocking_text
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
OUTER APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
August 21, 2019 at 8:02 pm
Thank you
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply