locked
Database Mirroring and VAS Pressure RRS feed

  • Question

  • Hi All

     

    We've recently implemented high-performance (i.e. asynchronous, manual failover) database mirroring as our availability (and reporting) solution, but we have started experiencing issues with our backups on the principal server that seemingly relate to VAS pressure.

     

    Approximately 1.5 weeks after restarting the SQL Server service the instance gets to the point whereby the maximum contiguous chunk of VAS is less than 64KB, we then start to get error 18210 (%BackupVirtualDeviceSet::Initialize: Request large buffers failure on backup device 'SQLBACKUP_64FDE05C-AC1E-4A3A-8859-2C38030F6D95'. Operating system error 0x8007000e(Not enough storage is available to complete this operation.)) during backups. The instance then has to be restarted and ~1.5 weeks later the same happens. Additionally the CPU usage can be seen to jump up to approx 17% which appears to be due to a high value of the 'Compilations/sec' counter, again this is fixed by restarting the service.

     

    After disabling mirroring we have found that the Total Free VAS levels out and no longer decreases.

     

    We do use linked servers (extensively), third-party extended procedures and the occasional sp_OACreate call, but we've been unable to replicate the VAS problem on our secondary server by repeatedly using these objects, which led us to conclude that mirroring was causing the problem, and indeed this seems to be the case.

     

     

    Server specs are as follows:

     

    2 x Quad-Core Intel Xeon E5345 CPUs @ 2.33GHz

    16GB RAM

    Windows Server 2003 Enterprise SP2 (32-bit)

    PAE Enabled]

    /3GB switch not used  [EDIT: the problem is also experienced when using the /3GB switch]

    Principal and Mirror identically specced and on same LAN segment

     

     

    SQL Server specs are as follows:

     

    2005 SP2 (9.0.3042) Enterprise

    @@VERSION (Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86)   Feb 26 2008 18:15:01   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) )

    AWE Enabled

    Min server memory 10000MB

    Max server memory 13000MB

    -g startup switch not used [EDIT: the problem is also experienced when using -g512]

    8 Databases being mirrored

     

     

    We have been tracing the 'Total' and 'Maximum' values of free space in the VAS using Perfmon by using the following queries:

     

    Code Snippet

    DECLARE @newvalue   INT

    SELECT  @newvalue = SUM(region_size_in_bytes)
    FROM sys.dm_os_virtual_address_dump
    WHERE region_state = 0x00010000

    EXEC [master].[sys].[sp_user_counter1] @newvalue = @newvalue


    SELECT  @newvalue = MAX(region_size_in_bytes)
    FROM sys.dm_os_virtual_address_dump
    WHERE region_state = 0x00010000

    EXEC [master].[sys].[sp_user_counter2] @newvalue = @newvalue

     

     

     

    ...and here's a Reporting Services chart showing the gradual decrease in total free addresses within VAS (purple line) and the largest contiguous chunk of free addresses within VAS (green line):

     

    http://www.zen154829.zen.co.uk/DBMirroringVASPressure20080917.jpg

     

    The y-axis scale is in % to get the three traces to fit on the same chart.

     

    I guess that I'm looking for any advice or pointers that would help us identify why mirroring is causing VAS pressure and whether the problem can be fixed. We may have the opportunity to move to the 64-bit versions of Windows and SQL Server, which will alleviate VAS pressure, but we feel that this should be un-necessary and that there is an issue (possibly a memory leak?) within the mirroring process, or the way in which we have configured mirroring.

     

    Any advice would be greatly appreciated.

     

    Thanks

    Chris

     

     

     

    Wednesday, September 17, 2008 8:35 AM

Answers

  • Just to complete this thread in that the issue turned out to be nothing to do with database mirroring at all, rather we were using a monitoring application (Idera Diagnostic Manager) that uses sp_OAMethod calls.

    There is a known memory leak issue that is resolved in this hotfix (http://support.microsoft.com/kb/937277), the application of which has now resolved the issue were were having with VAS pressure.

    It so happened that when we we stopped the mirroring we were also having issues with the monitoring app, which meant that the sp_OAMethod calls were being made less frequently and so the VAS pressure problem did not surface!

    Thanks to all for the assistance.

    Chris
    MCTS : SQL Server 2005 ; MCITP : Database Developer
    • Marked as answer by Chris Howarth Saturday, December 13, 2008 10:17 AM
    Saturday, December 13, 2008 10:16 AM

All replies

  • Hi Chris,

     

    What all switches are you using in boot.ini and sql memory configuration  ?

     

    Vishal

    Tuesday, October 7, 2008 1:36 AM
  • Hi Vishal

     

    Thanks for your reply - specs are as follows:

     

     Chris Howarth wrote:

    Server specs are as follows:

     

    PAE Enabled

    /3GB switch not used  [EDIT: the problem is also experienced when using the /3GB switch]

     

    SQL Server specs are as follows:

     

    Min server memory 10000MB

    Max server memory 13000MB

     

    Thanks

    Chris

    Thursday, October 9, 2008 1:22 PM
  • Just to complete this thread in that the issue turned out to be nothing to do with database mirroring at all, rather we were using a monitoring application (Idera Diagnostic Manager) that uses sp_OAMethod calls.

    There is a known memory leak issue that is resolved in this hotfix (http://support.microsoft.com/kb/937277), the application of which has now resolved the issue were were having with VAS pressure.

    It so happened that when we we stopped the mirroring we were also having issues with the monitoring app, which meant that the sp_OAMethod calls were being made less frequently and so the VAS pressure problem did not surface!

    Thanks to all for the assistance.

    Chris
    MCTS : SQL Server 2005 ; MCITP : Database Developer
    • Marked as answer by Chris Howarth Saturday, December 13, 2008 10:17 AM
    Saturday, December 13, 2008 10:16 AM