page writes/sec high on mirrored server

Question

page writes/sec high on mirrored server

matthew.peters

Ten Centuries

Points: 1138
More actions
September 11, 2012 at 11:36 pm

#261779

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pooyan_pdm Hall of Fame Points: 3007 More actions · Answer 1

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 Ten Centuries Points: 1138 More actions · Answer 2

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 Hall of Fame Points: 3007 More actions · Answer 3

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 Mr or Mrs. 500 Points: 511 More actions · Answer 4

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 SSC Guru Points: 134017 More actions · Answer 5

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 Mr or Mrs. 500 Points: 511 More actions · Answer 6

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.