SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


page writes/sec high on mirrored server


page writes/sec high on mirrored server

Author
Message
matthew.peters
matthew.peters
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 349
Hello
I have a mirrored server in which the SQLServer:Buffer Manager: 'page writes/sec' is high, it averages around 280 with the maximum being 2068. All other counter under buffer manager look ok. The reason I am querying this is that we have a 3rd party monitoring tool that send me an email everytime it goes over the threshold of 100.

So is this a normal situation on a mirrored server, or is it something I should be looking into

Cheers
pooyan_pdm
pooyan_pdm
SSC-Addicted
SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)

Group: General Forum Members
Points: 476 Visits: 422
It depends on several factors.First of all to the Principal database transaction load, if this value is almost the same at the principal server it's totally normal.Then to the disk subsystem on the Mirror.Clearly if the disk subsystem on the mirror can't cope with transactions shipped from the principal the write/sec would be enven lower on the mirror instance.

Pooyan
matthew.peters
matthew.peters
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 349
Thanks for the reply Pooyan

To answer your questions, the writes/sec is quite low on the principal server, averages around 30 and the disk subsytem is the same as the principal server.
Wouldn't the disk writes/sec be naturally higher on the mirror server because each log record sent from the principal is getting written straight to disk?

Are there any other counters that I can look at to narrow down what the issue is?
pooyan_pdm
pooyan_pdm
SSC-Addicted
SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)

Group: General Forum Members
Points: 476 Visits: 422
If the Mirror server is dedicated to the mirror db and no other IO intensive process is running on it , then your guess logicaly make scence. But in larger time intervals the write/sec should be almost the same because the checkpoint is also applied on the principal db and all the dirty buffer pages are flushed to disk. So comparing the write/sec in a long enough period of time(long enough for a checkpoint to run on the principal) should result in relatively similar values.You can also use sys.dm_io_virtual_file_stats to capture the volume of writes on a specific file in different intervals.

Pooyan
krefior
krefior
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 199
I'm seeing consistently higher write times on all my mirror servers in comparison to the principals. More specifically the Log writes are almost identical, but the data write is 1-30 times higher depending on the server.

On a server with a mirror send rate of 500KB/s, the Principal '% Disk Time' is 0 and the 'Disk Write Bytes /sec' is 0, However the mirror server '%Disk Time' is %30 and the 'Disk Write Bytes /sec' is 1.4MB/s
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30392 Visits: 8667
Well, the mirror has to have writes to affect the changes brought over from the tlog on the primary so I would expect actual writes to be higher than the shipped volume. As for disk busy metrics, perhaps the mirror secondary hardware isn't as good as the primary - or has other activity on it?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
krefior
krefior
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 199
Identical hardware. Virtual machines, and same SSD / Raid type on the SAN.

Looks to me like this is actually by design.

The Log is always being restored to the Mirror database in recovery. If it didn't do this failover would have to complete a Log restore. (This still happens, but it's only from the unrestored LSN, not the full log)

A full Log restore would make failover take minutes, not seconds.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search