WRITELOG wait time is large while transaction log is put on Fusion IO

[former_member211576]

Hi experts,

Our users complain SAP on SQL becomes slow sometimes recently. I check wait

event and find WRITELOG become top 1 in wait events. This never happened

before. I use Perfmon to check Avg. disk sec/Write is zero(<0.001). It does

not seem to be a storage issue. What happened?<o:p></o:p>

By the way, I use SQL 2012 with AlwaysOn(Sync mode), HADR_SYNC_COMMIT = 2.58

and it seems to be ok. What is the Amount of outstanding log I/O limit in SQL

server 2012? ( Diagnosing Transaction Log Performance Issues and Limits of the Log Manager - Microsoft SQL Server ... )

• --- Wait event since DB start ---

  
• ---Wait event when system is slow ---

  PS:first row: wait event name

  second row: Total Wait Time(ms)

  third row: Total Wait Time(%)

  fourth row: Avg. wait. (micro sec)

  

[xymanuel · Accepted solution]

Hi Dennis,

there is an Blogpost from the MSSQL on SAP Team, maybe you have this problem?

Can you doublecheck this?

Message misaligned log IOs which required falling back to synchronous IO in SQL Server Error Log - ...

What is your MaxDop setting?

Max degree of parallelism (MaxDOP) - Running SAP Applications on Microsoft Platform - Site Home - M...

Check this article

Customer Proof of Concept on New HP DL980 - Running SAP Applications on Microsoft Platform - Site H...

Regards

Manuel

[former_member211576 · Accepted solution]

Hi Manuel,

  Thanks for your reply. I know cameron and I have read all posts you mentioned.

Sorry, I think I have found the source of the problem
( WRITELOG wait time is large while transaction log is put on Fusion IO )

and I should close this post.

By the way, Cameron has replied to me yesterday.

" The only thing I can think of is simply a HUGE amount of log writing "

And this is exactly what as Uri mentioned.

Read this  artcile

http://blogs.msdn.com/b/sqlsakthi/archive/2011/04/17/what-is-writelog-waittype-and-how-to-troublesho...

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

Hi Uri,

  I had read that post. Please confirm if I understand it correctly.

The SQL server is freeze because there is a very long transaction(maybe update more than 300GB data into database without a commit statement). So while this transaction is finished and commmit, it will block all later on transactions so I should avoid any long running transactions and force them to commit more frequently with smaller dataset. Is that correct?

I'm not even the slightest some kind of expert, but according to graphs above, is that like all background processes running? Secondly, to me, it seems that this would cause a lot of problems because it looks like many are fragmented and will cause CPU to become so cluttered that many processes wouldn't function properly without optimizing some of that junk. I could even foresee it possibly crashing because of this. It reminds me of going to 3 places to eat and ordering biggest meal at all three, and the try cramming them all in at once. Na mean?

I just seen post below and correct me if I'm wrong, but would this occur by running updates and stopping unwanted ones and then accidentally repeating same process causing the larger load?

Yes, that is correct, look for the sql procedures/batches/statements that perform lot of DML  operation within a loop such as described in the article, that's a first indication of high write log waits.

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

At a glance, maybe you don't really have a problem, just a busy system.

[luisdarui]

Hi Dennis,

Have you checked in DBACockpit transaction, Performance > I/O Performance?

Usually when I'm addressing IO issues, I look there first. In your case it seems that it is affecting the Transaction Log. In my experience, when WRITELOG times are too much high, usually the IO subsystem cannot keep up with the rate of the log flush.

You should double-click the Transaction Log file to get more detailed times for those time stamps. Maybe it give you more detailed information.

In the following blog post I address some of the most common IO performance issues, seeing your case I believe that you can check the point 2.

It contains the relevant SAP Note for IO performance issues and also the link to Juergen's whitepaper.

Best Regards,

Luis Darui

[former_member211576]

Hi Luis,

  Thanks for your reply. I create incidents to SAP and Microsoft many times. Most of the time, they will merge DMV with perfmon data to analyze.

  In my case, I can see writelog[ms]/request is high(259ms) at 11:20am but why can't I see such data in the perfmon. THAT IS THE QUESTION. The possible reason maybe just as Uri mentioned ( WRITELOG wait time is large while transaction log is put on Fusion IO ). What does you think?

Uri: "Read this  artcile

http://blogs.msdn.com/b/sqlsakthi/archive/2011/04/17/what-is-writelog-waittype-and-how-to-troublesho..."

Dennis: "Hi Uri,

  I had read that post. Please confirm if I understand it correctly.

The SQL server is freeze because there is a very long transaction(maybe update more than 300GB data into database without a commit statement). So while this transaction is finished and commmit, it will block all later on transactions so I should avoid any long running transactions and force them to commit more frequently with smaller dataset. Is that correct?"

Uri: "Yes, that is correct, look for the sql procedures/batches/statements that perform lot of DML  operation  within a loop such as described in the article, that's a first indication of high write log waits."

[former_member211576]

• --- current disk queue length ---