Post reply

Backup slows system down too much

  • December 12, 2003 at 11:48 am

    #107550

    We have 200 databases (totalling about 20GB), and counting. I use maintenance plans for backup--seems very convenient, and I don't have to change anything when we add another DB.

    Every night we do disk-to-disk backup at 9:30 (which runs screamingly fast--only 10 minutes--because we backup to another disk), and then a separate disk-to-tape backup occurs.

    The problem: With the increased workload leading up to Christmas, the users are saying the system slows down too much during those 10 minutes. They are trying to use the system during live phone calls, and the caller can get impatient--so they end up writing things on paper, which has to be entered into the DB later. (Lots of extra work because somebody else has to replay the tape of the call to do this.)

    My main question is: Is there a way I can tell the backup to run at a lower CPU priority?

    I don't think we can backup directly to tape (thus reducing the load), because other backup software needs to use the same tape after the SQL backup is done. I suppose I could backup to the SAME disk, which would almost certainly get rid of the CPU contention, but then there would be disk contention instead, which may not be any improvement (it could even be worse). And I'd rather not move away from using the maintenance plans because of all the automatic stuff they do for me, like logging, deletion of old files on my desired schedule, etc. Moving the backup later into the night is problematic because (1) other backups need to happen afterward, and some of them run most of the night--and sometimes the verify phase continues well into the next day; and (2) nightly maintenance jobs and DB loads also run later in the night.

  • December 12, 2003 at 1:23 pm

    #486030

    If you moved the ten munites backup time to lunch hour would that make a difference?

    Mike

  • December 12, 2003 at 5:09 pm

    #486049

    I would verify whether the performance decrease was caused by the backup.

    You may consider to perform full database on weekly basis and do the differential backups for the rest days. Perform differential backup should be fast in general than the full backup. But you can't use maintenance wizard to do it, you have to code T-SQL.

  • December 12, 2003 at 6:20 pm

    #486053

    quote:

    If you moved the ten munites backup time to lunch hour would that make a difference?

    We don't have a lull during lunch--not much, anyway. We take calls from at least 4 time zones. Besides, that would leave 10 hours or so before the disk-to-tape backup, and part of the point is to have them close together.

    Although now that I think about it, I don't know if it's that important to have the disk-to-tape backup right afterward. I'll have to consider further--I might be able to move the backup to the wee hours, but I have to find another workable time window because there are other maintenance things going on most of the night.

    quote:

    I would verify whether the performance decrease was caused by the backup.

    I'm going to go up there and observe it tonight. But they say things always suddenly slow down at exactly that time every night, so it looks pretty clear-cut.

    quote:

    You may consider to perform full database on weekly basis and do the differential backups for the rest days.

    Possibly. I might look into it. But the full backup is so fast, I never considered there to be much reason for a differential. And I would really like to stay using the maintenance plans.

    But what about the big question I asked? Can I run the backup at a lower priority, or not?

  • December 14, 2003 at 4:04 pm

    #486080

    The backup priority can't be changed.

    What options have you used in setting up the maintenance plan? The 'Reorganize Data...' option can cause timeouts while sqlmaint performs a dbreindex.

    It sounds as if you have all your databases under one maintenance plan. You might want to consider splitting them out into multiple plans to spread the load.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • December 14, 2003 at 10:36 pm

    #486096

    Or, you may need to spread your backups across diffrent times i.e. backup the largest db's alone on a diffrent maint plan and schedule that an hour or two away from your slowdown period. I would really look at doing diffs and/or tlog backups to cut the time down though. To be honest with you, I haven't used the maint plans in years.

    Wes

  • December 15, 2003 at 9:32 am

    #486191

    If you don't have any options to move the time the only other thing you can do is make the backups faster. Take a look at the SQ Litespeed product. I was amazed how much faster and less CPU intense it was on full and transation backups.

    Sean Breyer

  • December 17, 2003 at 1:44 pm

    #486825

    quote:

    What options have you used in setting up the maintenance plan? The 'Reorganize Data...' option can cause timeouts while sqlmaint performs a dbreindex.

    It's just a backup. Here's the command line:

    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 268C1883-68A5-47D6-8B4D-9AC0C671390A -Rpt "D:\MSSQL7\LOG\DB Backups4.txt" -DelTxtRpt 1WEEKS -WriteHistory -BkUpMedia DISK -BkUpDB "H:\MSSQL7\BACKUP" -DelBkUps 25HOURS -CrBkSubDir -BkExt "BAK"'

    quote:

    It sounds as if you have all your databases under one maintenance plan. You might want to consider splitting them out into multiple plans to spread the load.

    Sure they're on one plan. But there are basically only two choices: All databases, or a set of individually-enumerated databases. I already said we have 200 DBs and more are added all the time, so any partitioning would have to continually updated (with a high probability of errors creeping in). Too bad wildcards, or other selection criteria (such as size) aren't allowed.

    quote:

    Or, you may need to spread your backups across diffrent times i.e. backup the largest db's alone on a diffrent maint plan and schedule that an hour or two away from your slowdown period.

    Same problems as above. Besides, as I've already detailed, I have limited options for moving backups to any other time slot.

    quote:

    I would really look at doing diffs and/or tlog backups to cut the time down though.

    As I said, we already do tlog backups every half hour. One day's worth of those per DB seems to be quite enough files to deal with. No diffs at this point, but maint. plans don't support them anyway.

    quote:

    To be honest with you, I haven't used the maint plans in years.

    So you wrote your own code to create individual subdirectories, generate file names, keep track of the file names, log status messages, and delete old backups and log files as new ones are created?

  • December 17, 2003 at 1:51 pm

    #486828

    quote:

    Take a look at the SQ Litespeed product. I was amazed how much faster and less CPU intense it was on full and transation backups.

    I have noticed the ads for that product, but since it does compression I figured it would be slower--and fairly CPU-intensive. (I'm amazed at how fast the SQL Server backups are.)

    Were you backing up to the same disk, or a different disk, or to tape?

    I could look at it, but I'm not the one that can sign the check....

  • December 17, 2003 at 2:17 pm

    #486837

    Are your database files in same drivers which your database backup files have to go to?

  • December 17, 2003 at 3:36 pm

    #486861

    wodom, given your answers to our questions and suggestions, I'd say you'll have to "roll your own" maintenance plan.

    In a past life I had to do this for SQL 6.5 to maintain a warm standy server for a 50gb 24x7 database.

    The table I used looked like this,

    

    CREATE TABLE dbo.tblDBaseMaint (

    ProcessOrder int NOT NULL ,

    DBase varchar (30) NOT NULL ,

    Active tinyint NOT NULL ,

    DBBkpPath varchar (100) NOT NULL ,

    DBBkpAge tinyint NOT NULL ,

    RestoreDB tinyint NOT NULL ,

    LogBkpPath varchar (100) NOT NULL ,

    LogBkpAge tinyint NOT NULL ,

    ChkDB tinyint NOT NULL ,

    ChkAlloc tinyint NOT NULL ,

    ChkTxt tinyint NOT NULL ,

    ChkCat tinyint NOT NULL ,

    UpdStats tinyint NOT NULL ,

    ReBldIndx tinyint NOT NULL ,

    MaintRptAge tinyint NOT NULL ,

    PreCmd varchar (255) NULL ,

    PostCmd varchar (255) NULL ,

    DfltEntry tinyint NOT NULL ,

    CONSTRAINT PK_tblDBaseMaint_ProcessOrder PRIMARY KEY CLUSTERED 

    (

    ProcessOrder

    ),

    CONSTRAINT UNQ_tblDBaseMaint_DBaseActive UNIQUE NONCLUSTERED 

    (

    DBase,

    Active

    )

    )

    With this table I was able to re-order the sequence of the backup by altering the ProcessOrder field. On a database by database basis, direct the database and log backups to different file paths, using the DBBkpPath and LogBkpPath fields. Control how many backups were kept via the DBBkpAge and LogBkpAge fields. And build my own SQLMAINT command using the ChkDB, ChkAlloc, ChkTxt, ChkCat, UpdStats and ReBldIndx fields. As I mentioned this was a SQL 6.5 database, so some of the maintenance options will be different in SQL 2000.

    When my maintenance job ran it added any databases that weren't in the table with the values form the record that was flagged as the Default (using the DfltEntry field). It also marked databases that didn't exist as inactive using the Active field.

    I also had the PreCmd and PostCmd fields to execute stuff before and after the backups, eg: In order to restore the msdb database on the standby server I had to stop the SQL Agent service and start it again after the restore had completed.

    All up, the entire maintenance process consisted of 10 procedures and two tables. One of these days I might get around to updating it to run on SQL 2000. At the moment I don't have any need as our largest databases are only 3-4gb and the 2-3 that are 24x7 are only 400-500mb.It also helps that our 90 odd databases are spread acros half a dozen servers.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • December 18, 2003 at 10:07 am

    #486995

    quote:

    Are your database files in same drivers which your database backup files have to go to?

    No, the backup goes TO the 2nd disk, not FROM it. As my original post noted, this is why the backups are so fast.

  • December 18, 2003 at 10:11 am

    #486997

    quote:

    given your answers to our questions and suggestions, I'd say you'll have to "roll your own" maintenance plan.

    Yuck. Not that I couldn't do it, but I have other things that need doing. (Sound familiar?)

    Maybe you, or someone, could recommend a particular script or set of scripts from the sqlservercentral library. Does anyone have a favorite they've used?

  • December 18, 2003 at 10:13 am

    #486998

    Have you run Performance Monitor to track the resource's usage in CPUs, Disk I/O, Network etc? What are counter numebrs if you had?

  • December 18, 2003 at 3:27 pm

    #487102

    quote:

    quote:

    given your answers to our questions and suggestions, I'd say you'll have to "roll your own" maintenance plan.

    Yuck. Not that I couldn't do it, but I have other things that need doing. (Sound familiar?)

    Maybe you, or someone, could recommend a particular script or set of scripts from the sqlservercentral library. Does anyone have a favorite they've used?

    I couldn't recommend any particular one, I might upset someone and of course my favourite is my own

    Here's a few of the ones posted in the script library,

    http://www.sqlservercentral.com/scripts/contributions/90.asp

    http://www.sqlservercentral.com/scripts/contributions/71.asp

    http://www.sqlservercentral.com/scripts/contributions/468.asp

    http://www.sqlservercentral.com/scripts/contributions/66.asp

    http://www.sqlservercentral.com/scripts/contributions/847.asp

    I'll leave it to you to work out which one best suits your needs.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

Viewing 15 posts - 1 through 15 (of 25 total)

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