Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

page writes/sec high on mirrored server Expand / Collapse
Author
Message
Posted Tuesday, September 11, 2012 11:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 10:48 PM
Points: 36, 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
Post #1357793
Posted Wednesday, September 12, 2012 3:07 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 4:28 PM
Points: 115, Visits: 402
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.

Cheers ,
Pooyan D
________________________________________________
Microsoft Certified Technology Specialist : SQL Server 2008
Post #1358226
Posted Wednesday, September 12, 2012 7:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 10:48 PM
Points: 36, 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?
Post #1358292
Posted Wednesday, September 12, 2012 10:24 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 4:28 PM
Points: 115, Visits: 402
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.

Cheers ,
Pooyan D
________________________________________________
Microsoft Certified Technology Specialist : SQL Server 2008
Post #1358332
Posted Wednesday, October 24, 2012 3:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 24, 2014 3:16 PM
Points: 159, Visits: 189
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
Post #1376694
Posted Thursday, October 25, 2012 10:28 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 4,470, Visits: 6,399
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 at GMail
Post #1377119
Posted Thursday, October 25, 2012 10:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 24, 2014 3:16 PM
Points: 159, Visits: 189
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.
Post #1377137
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse