Category: Wait types

Cached Plan Analysis, SQL, Wait types

How to identify parallel plans in SQL Server
Few years back, on one of my engagements, identified lots of performance issues with an application. As part of a detailed analysis, it is observed there are lots of queries under performing with wait type “CXPACKET”. In short, CXPACKET wait types are involved in parallel query execution. These wait types indicate that the SPID is waiting on a parallel process to complete or start. There are many reasons your query may undergo a parallel execution like missing index, non optimized sql script etc. But, CXPACKET is not ALWAYS a bad thing.We will see about CXPACKET in detail with another post.

Now, We will see how to identify the queries with parallel execution. Once you identified the queries, you can further examine to identify the actual cause of the issues and optimize.

Script

select p.*,q.*,cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0

Please note that the above query gets information from cached plan in SQL Server. Hence, I will recommend to run a load test with a real time workload and use the script to identify the parallel executing queries. It is also important that the cached plans may get flushed due various factors like memory pressure, recompile of procedures, auto stats updates etc. But, this helped me a lot at times!!!

[Edit – 8th-Sep-202]

How do we know the degree of parallelism of a currently running query?

select session_id, scheduler_id
from sys.dm_os_tasks
where parent_task_address IS NOT NULL
--AND session_id = ? --You can uncomment and give spid as required.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Errors and Exceptions, SQL, Wait types

DBMIRROR_DBM_EVENT wait type causing blocking in SQL Server

Recently, we observed lots of blocking in our production server showing the wait type as DBMIRROR_DBM_EVENT. To brief, the system is an OLTP system with lots of DML actions, ended up with lot of long running transactions and blocking the user transactions. As DBMIRROR_DBM_EVENT wait type is an internal to Microsoft and there is less documentation available on Web, we started our troubleshooting looking at the mirroring set up. Here are few points we started our analysis as below.

1. Verify DB Mirroring is working uninterrupted – if the mirroring is not working for some reason, on synchronous mode, the impact is huge, the principal server has to wait to commit.

2. Verify feasibility of DB Mirroring mode to Asynchronous – Changing Mirror mode from synchronous to Asynchronous is not a solution,however, its a workaround. Ideally, we need to identify if long running transaction/index maintenance etc caused the issue while applying the changes to Mirror.

3. Verify network latency between Principal and Mirror – The latency in the network is one of the reason that can be assessed using the perfmon counter.

Root cause and resolution

In our case, the root cause was the first one. For some reason, the mirroring has been broken, no endpoint was accepting the connection from the principal server. This lead to a situation on the principal server each time a transaction waits for its log (LSN) to be hardened on the mirror.Once we reestablished the mirroring, the wait type is removed and no blocking observed further.

How to get the status of Mirroring:


SELECT
DB_NAME(DATABASE_ID) AS [DATABASENAME],
CASE WHEN MIRRORING_GUID IS NOT NULL THEN 'MIRRORING IS ON' ELSE 'NO MIRROR CONFIGURED' END AS [ISMIRRORON],
[MIRRORING_STATE_DESC],
CASE WHEN MIRRORING_SAFETY_LEVEL=1 THEN 'HIGH PERFORMANCE' WHEN MIRRORING_SAFETY_LEVEL=2 THEN 'HIGH SAFETY' ELSE NULL END AS [MIRRORSAFETY],
MIRRORING_ROLE_DESC,MIRRORING_PARTNER_INSTANCE AS [MIRRORSERVER]
,MIRRORING_PARTNER_NAME AS [PARTNER NAME]
,MIRRORING_ROLE_DESC AS [MIRROR ROLE]  
,MIRRORING_SAFETY_LEVEL_DESC AS [SAFETY LEVEL]
,MIRRORING_WITNESS_NAME AS [WITNESS]
,MIRRORING_CONNECTION_TIMEOUT AS [TIMEOUT(SEC)]
FROM SYS.DATABASE_MIRRORING

See Also:
https://blogs.msdn.microsoft.com/grahamk/2011/01/10/sql-server-blocking-caused-by-database-mirroring-wait-type-dbmirror_dbm_event/