SQL Server Performance

SQL2K / W2003 / Memory Swapping during backup

Discussion in 'Performance Tuning for Hardware Configurations' started by vrobert, May 25, 2004.

  1. vrobert New Member

    I've a sql server 2000 std edition on windows 2003 std with 2GB of memory .<br /><br />The setup of sql server is:<br />minimum memory: 128 MB<br />maximum memory: 1500 MB<br />set working set size=0<br /><br />We use Netbackup to do the backup of the server.<br /><br />In day the server functions well. SQL occupies has little close 1500 MB.<br /><br />When the backup is starting, a process of netbackup needs a lot of memory, about 600 MB during few seconds.<br /><br />Windows or sql takes the decision to swap the process sqlserver.exe, in order to accord a maximum of physical memory to netbackup.<br /><br />The system is very slow during the "swapping". And a lot of request to sql server receives timeouts. (Our web site becomes Inaccessible)<br /><br />And the events are logged in the system log:<br />Event ID 119 - DISK - delayed non-paging Io requests <br />The driver for device DeviceHarddisk0DR0 delayed non-<br />paging Io requests for 10ms to recover from a low memory<br />condition.<br /><br /><br />What can I do to prevent the swapping of sqlserver process ?<br /><br />I've try to set min memory=max memory=1500 MB and set working set size=1, but nothing has changed.<br /><br /><br /><br />PS: excuse my bad english, i'm a little french user <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  2. satya Moderator

    Why not use memory settings as dynamic on the server?
    Are there any other applications sharing the server resources?

    Are you performing backup to a network location?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. vrobert New Member

    Why not use memory settings as dynamic on the server?

    It's a good question ... i don't know, we have juste migrated from sql7/win NT4, and it was the setup of the old system.
    I can test that. What must I do ? Min memory=0 and max memory=2GB ?


    Are there any other applications sharing the server resources?

    We have many scripts .vbs planned in sql and all services installed by default on windows 2003 standard.


    Are you performing backup to a network location?

    We have a Netbackup Server. It's the master and it control the DLT robotics. On others servers (IIS & SQL), there is an agent of Netbackup.
  4. satya Moderator

    Its a general recommendation to leave the SQL Memory settings to Dynamic instead of controlling with Min and Max values. You can allow in the cases if other applications are sharing the server resources.

    How about PERFMON counters such as Physical disk, memory, processor, process and SQL Server Memory, get them to assess.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. derrickleggett New Member

    If you don't leave it at Dynamic, you need to stop using Netbackup. I'm curious as to why you use that anyway??? Use SQL Server backup to backup to a share. Then use netbackup to backup that directory. You will have much less problems doing it that way.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  6. vrobert New Member

    We use Netbackup to backup all the server, including OS. We want to reinstall quickly in case of crash.
  7. satya Moderator

    I second Derrick's suggestion with regard to performing backup from SQL perspective and then copy physical file using Netbackup.

    Collect the perfmon counters specified above.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. derrickleggett New Member

    It doesn't matter if you use if for all of your servers. It's a BAD idea for SQL Server. There is plenty of information out there to support this if you search for it. You can backup your OS, but you need to skip the directories for the data and log files for SQL Server. You need to perform SQL Server full and possibly transaction log backups. You need to use Netbackup to backup that backup directory.

    If you ever go to recover from a disaster, you'll be glad you did.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page