Tag Archives: wait stat

Mirroring SYNCHRONIZING status and DBMIRROR_DBM_EVENT wait type

noWaitMirroring SYNCHRONIZING status and DBMIRROR_DBM_EVENT wait type

Few days back one of the DBA in our company setup mirroring for one of our production database hosted in SQL Server 2008 R2. After setting mirroring up, our peak business hour started and we have started facing heavy blocking issues. Thanks to customized monitoring application we have developed which shows me that mirroring was in “Synchronizing” status not “Synchronized” and there were so many wait type “DBMIRROR_DBM_EVENT” which was blocking many sessions. Apart from that I have found that mirroring was having “High Safety” mode.

In highly transactional database, I prefer to have asynchronous mirroring “High Performance”. As soon as I changed the mirror safety mode and kill some heavy blocking process everything was up and running fine.

As per MSDN, High-performance operates asynchronously. Asynchronous operation supports only one operating mode—high-performance mode. This mode enhances performance at the expense of high availability. High-performance mode uses just the principal server and the mirror server. Problems on the mirror server never impact the principal server. On the loss of the principal server, the mirror database is marked DISCONNECTED but is available as a warm standby.

So, High-performance comes at the cost. If it would be “High Safety” it wouldn’t release until data successfully reached to mirror but it take lot of resources so I used to keep “High Performance” for one of our highly transaction database only.

BTW, for checking mirroring safety mode and status, I have already written one script earlier in my blog post which is give here again:

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
FROM sys.database_mirroring
GO

For wait_type, you can execute following DMV, which I have already explained in my blog post “CXPACKET wait stats in SQL Server”.

select * from sys.dm_os_wait_stats where wait_type='DBMIRROR_DBM_EVENT'
GO

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

CXPACKET wait stats in SQL Server

WaitStatCXPACKET wait stats in SQL Server

“Wait” is one of the very familiar word in human’s life. Nobody likes to wait though it is not always bad to wait for a while for something good, at the same time long unnecessary waiting period resulted into frustration.

When I am about to finish my day in office, I always gets eager to meet my 2 year old son “Teerth”. I enjoy this wait period, if I would be with him for day, I wouldn’t get pleasure of meeting him at the end of day, though I enjoy his company whole day.

I don’t like to wait for upload/download something due to slow internet speed, it is unproductive and unnecessary wait time. Who likes to wait for 1 minute video on YouTube.com gets 5 minutes to load (streaming)?

This is how I evaluate wait period and SQL Server apply almost same kind of rule. It is up-to you to know wait stat, identify whether it is good (necessary) or bad and resolve it if possible.

In simple words, you would like to see result of your request immediately. You expect, you execute query in SQL Server and it finishes operation immediately. In normal scenario SQL Server even do it but there are many cases and real time scenario which wouldn’t let operation go smooth.

There are so many reasons which makes your query waiting, whether it is memory issue, CPU pressure, blocking, locking, dead locking to name a few among so many.

There is one DMV which you can use to monitor wait stats. “sys.dm_os_wait_stats” is your weapon to deal with wait stats.

You can execute simple SELECT query on “sys.dm_os_wait_stats” to see all wait stats along with the value in your system.

Yesterday I got an email from one of the reader this blog, he was asking that he is facing big values in CXPACKET wait stats. Is it good or bad? If bad, how can I can resolve it? This email from the reader insist me to write this blog post.

As per MSDN, CXPACKET occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem. To find detail about different wait stats, click here.

Generally parallelism is being used to execute your process so for example, if you have executed SELECT query and four processor is executing your SELECT query now three processor has finished their part of job but those processors can’t show you the result until last one processor return the result because request result of SELECT query can only be completed if all four processors return result and clubbed together. Since your last processor was still working, three processors had to wait which can raise CXPACKET wait stat.

To overcome this problem, you can decrease value of MAXDOP or Max degree of parallelism. I have written article on this topic earlier which you can refer by following link:

““Max degree of parallelism” or “MAXDOP”, good guy or bad guy?

Note: This is just a generic advice to handle CXPACKET wait type. For more accurate implementation, need to study the environment in detail. Generally in Online Transactional Pr0cessing (OLTP) should have low number of value (near to zero) for MAXDOP as it used to have quick and short transaction and Online Analytical Processing (OLAP) should have higher number of MAXDOP.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.