February 17, 2009 at 11:13 pm
Hi,
We have clustered sql server 2005 instance. We have a full backup every and every 8 hrs log backup.
We a Spot light monitoring tools to monitor Sql server.
We are frequently getting this Log flush wait time alarm as below:
1.
The database WSS_Content has a high Log Flush Wait time (313 ms).InformationDatabases - Log Flush Wait Time Alarm
Is this alarm is due to taking log backups only 2 times a day?what this this log flush wait time?
2.We also getting High disk queue length on C drive. Iam confused why we are getting high disk queue lenght on C drive? I went through event viewer logs and found that Anti virus is started running at the time we are getting this alarm on C drive. What we need to do to aviod getting this alarm on C drive?
Alarm raisedPhysical disk #0 C: containing paging file(s) 'C:\pagefile.sys' is experiencing a high rate of I/O (100% busy) unrelated to paging.InformationPaging Disk Activity Alarm
3. We have Z drive, which used for only as backup drive for the databases. All full backups and log backups are stored on Z drive.We are also getting High disk queue lengthon Z drive too as below.
Alarm raisedPhysical disk #20 Z: containing paging file(s) 'C:\pagefile.sys' is experiencing a high rate of I/O (96.4% busy) unrelated to paging.InformationPaging Disk Activity Alarm
we have D and E drives for mdf and ldf files. My question is Why we are getting High disk queue lenght on C and Z drives But not on D and E.
Plz advice me....
February 17, 2009 at 11:29 pm
What's your disk layout? (No of drives, raid level, DB-related content)?
SAN or local storage?
What do the following two perfmon counters look like (broken down by drive)
Physical disk\ avg sec/read
Physical disk\ avg sec/write
Why only log backups twice a day? It means you could lose half a day's work if the server fails?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2009 at 11:39 pm
Thank you
We have Raid 10 SAN storage drives.
Disk lay out as below:
D:datafiles
E:Secondary data files
F:log files
Z:Backup Drive
and the instance is clustered.
I did not understand why Iam getting high disk Queue length on C and Z drives.
and what is meant by database having High log flush time. Where can I check in SQL Server about this high disk queue length other than monitoring tool?
I will set up the disk counters tommorow and will post the readings...
Why only 2 backups daily? Because, currently there is not much activity going on.In coulpe of weeks we are going to schedule log backup every hour.
Thanks for your help
February 20, 2009 at 4:19 pm
Hi,
What do the following two perfmon counters look like (broken down by drive)
Physical disk\ avg sec/read
Physical disk\ avg sec/write
Drives:
C->OS
D->datafiles
E->Secondary files
F->Log files
G->TempDB
Z->Backup
I attached the excel sheets with above mentioned perfmon counters.
I would like to know from the readings how can we know that disks are performing at optimum level?
Please give me your suggestions
Thank You
Madhu
February 21, 2009 at 3:11 am
Your disk stats are high. Both sec/read and sec/write should be around 10ms. On C, your sec/read reaches 0.5, on D, G and Z the sec/write reaches 0.1. Both of those are far too high.
The spreadsheet doesn't contain stats for E and only has the read for F, so I can't see if these are also affected.
Are the SAN luns composed of dedicated disks (not shared with other luns)?
I would suggest more drives, spread the load out further.
Also, speak to the storage people and see if they have noticed any performance problems. With a SAN, it could be anything from the switch, the cache, the controllers to the disk. The storage people should be able to pinpoint the problem better.
Disk queue length is near-impossible to interpret on a SAN, because of all the caching, etc, etc. Ignore it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2012 at 7:58 pm
First, you should upgrade your Spotlight - the fact that you see "disk queue length" means you are not on the current version. The metric they use now is I/O Wait, which is much more meaningful because - as Gila said - SAN's and disk configurations render that statistic almost meaningless.
The paging alarm just means that the drive your pagefile is on is getting hit rather hard, but not the pagefile itself. It's a "heads up" - nothing more really. Maybe you were doing a file transfer or zipping up some files - something along those lines.
Since you are using Spotlight, you don't have to go to perfmon - you can get your physical reads/writes right from Spotlight. Go to the server in Spotlight, right-click on the drive and check out the Logical Disk Activity - might save you a little time.
February 3, 2012 at 8:12 am
madhu-686862 (2/17/2009)
Thank youWe have Raid 10 SAN storage drives.
Disk lay out as below:
D:datafiles
E:Secondary data files
F:log files
Z:Backup Drive
and the instance is clustered.
I did not understand why Iam getting high disk Queue length on C and Z drives.
and what is meant by database having High log flush time. Where can I check in SQL Server about this high disk queue length other than monitoring tool?
I will set up the disk counters tommorow and will post the readings...
Why only 2 backups daily? Because, currently there is not much activity going on.In coulpe of weeks we are going to schedule log backup every hour.
Thanks for your help
Unfortunately that information is not nearly complete.
How many spindles in the RAID 10 group?
Are there different RAID 10 physical LUNs for each logical drive?
Do other things sit on those same spindles?
How many and what type of HBAs connect server to SAN?
iSCSI or FC? What speed?
I HIGHLY recommend you get a performance tuning professional on board for a few days to give your entire sql server infrastructure a performace/system review.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 3, 2012 at 8:17 am
Please note, the original post is almost 3 years old and the OP has not logged in for over a year.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2012 at 12:25 pm
Aha - newbie mistake friends. I was looking at your last login date, not the thread date.
At any rate, it still pertains to Spotlight users today and the question does still come up in other forums.
August 17, 2015 at 9:04 am
Hey Grasshopper,, your post of 3 years ago helped me today because I TOO have spotlight and was researching same alert. THANKS,,, SO very helpful and pertinent indeed
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply