question

Waqar_lionheart avatar image
Waqar_lionheart asked

Writelog wait

Hi all, Small problem. We have upgraded our entire network to use mega network bandwidth and SSDs. Except after a week of super fast stuff now i have log waits on my databases. Its currently only one database and its saying in activity monitor TM Request transaction suspended then it disappears. Forgot to mention that we are using 2008 standard anx also what is the standard for write log waits Any help is appreciated as bulk inserts are now deathly slow. Regards, Waqar
wait-typewritelog
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do you have any type of monitoring tool like Red Gate or Sentry One? If not, I would start with this blog post from Paul Randal, https://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/. His script should help to identify where latency is. If there isn't any disk latency, then the problem is elsewhere. What is the storage under the hood? Do you have memory/CPU pressure?
2 Likes 2 ·
I agree with JohnM s comment about looking at Paul Randal's article about latency. Another thing you can do without any SQL code or even without using Management Studio is look at Resource Monitor, open the Disk tab. There you can filter to show only activity from SQL Server process. Look at the response times for individual files, and also check Disk Queue Length. Are response times long? How long is "long"? Well, it depends, if you don't have a baseline you have nothing to compare to. But I'd say if you see response times on 50ms or so for a period of time, you have a latency problem. Disk Queue Length should be a small number. How small will depend on the amount of activity and the number of CPUs. But it should ideally be 0 or close to 0. I have been in situations when Disk Queue Length was 25000. That's a quite obvious problem. But let's say if you see Disk Queue Lengths higher than your number of CPUs for more than just a short moment, you have a latency problem.
1 Like 1 ·
That's a whopping big "it depends". Of course you want to have the log writes to happen as quickly as possible. Have you looked at the execution plan of the inserts to see what it's really doing?
0 Likes 0 ·
I know that's a ginormous it depends. However i would like to find out whivh log is incurring these waits. I know i said its only one database but it really isn't everything else is now slow too. Taking back ups, restoring backups etc. So my only hope is to find out exactly what is going on
0 Likes 0 ·
We recently tried out a couple of monitoring tools and Solarwinds Database Monitoring makes a massive deal about monitoring wait times. They offer trial periods so you should be able to find the issues within 14 days :D
0 Likes 0 ·
Show more comments

1 Answer

· Write an Answer
Waqar_lionheart avatar image
Waqar_lionheart answered
please see screen shot below. Its showing a consistent spike in I/O buffer as well as writelog wait type. Any help please? ![alt text][1] [1]: /storage/temp/4611-capture.jpg

capture.jpg (75.3 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.