Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Aug 2002
    Posts
    21

    Unanswered: which is a better way to backup, maintenance plan or scheduled job?

    We are running sql server 2000. The performance is degrading pretty badly. We are running scheduled jobs for backup of both logs and datafiles. I wonder if I should use maintenance plan instead.

    The slowest database is of 64GB total size, with datafile being 55GB and Tran Log 8GB. For some reasons, the space available is 0. I wonder if that's the reason for slow performance. The slowest table in this database has about 30 million records and 40 fields. This table has about 10 indexed fields.

    Can some of you gurus pin point what we are doing wrong here? A select statement on that slowest table takes way too long. Any help will be appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The maintenance plan wizard creates a scheduled job for the SQL Server Agent. You won't get any different performance using one option of the other, since any task you can perform with the maintance plan you can also perform through a custom script. You'll see that the wizard's script consists of a call to a utility (sql_maint somthing or other), with a long string of parameters. You can create your own job using this command, and you can find it described in books on line.

    As a matter of fact, once the wizard creates the job it is no longer truly linked to the maintance plan. If you modify the parameters or schedule of the job, these changes are not reflected in the plan, and if you then modify the plan it overwrites your changes.

    The one thing I do use the maintance plan wizard for is defining sets of databases that require different maintance strategies. For instance, I create plans titled System_Databases, User_Databases, and All_Databases and assign different databases to them. The maintenance plans do nothing at all, but I reference them when I call the SQL_Maint utility.

    The big question is whether your custom jobs are doing all the maintenance tasks necessary. Update statistics, for instance.

    blindman

  3. #3
    Join Date
    Aug 2002
    Posts
    21
    Thank you, blindman.
    Now that you clarified the maintenance plan, can you please tell me whether the available space being 0 is the cause of slow performance? If so, how do I fix it?
    Thanks again for your help.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Is your database set to grow automatically?

    blindman

  5. #5
    Join Date
    Aug 2002
    Posts
    21
    Yes, it was set on grow by 20 percent automatically.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What steps are you performing in your maintenance plan, and what is the schedule?

    When you say the performance is degrading, do you mean transactio processing, data analysis queries, or the backups themselves?

    blindman

  7. #7
    Join Date
    Aug 2002
    Posts
    21
    Originally posted by blindman
    What steps are you performing in your maintenance plan, and what is the schedule?

    We have log back up job for every two hours, differential back up for once a day, and full back up once a week.

    When you say the performance is degrading, do you mean transactio processing, data analysis queries, or the backups themselves?

    The complaint about slow performance is mainly on execution of stored procedures, especially on one update query. In that query, two tables inner join, one has about 30million rows, the other 5 million. Update one field in the bigger table based on a field in the smaller one.

    blindman
    Thank you very much for your help so far and future help in advance.

  8. #8
    Join Date
    Aug 2002
    Posts
    21
    blindman,
    My last post has my reply mixed in your post. The font is the same. Please don't be confused.
    Thanks.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should run UPDATE STATISTICS on tables which undergo frequent updates and inserts. Run it on the two tables you mentioned, and see if your performance improves.

    Some other questions:

    When you say the performance has been degrading, how quickly and what kind of performance are you getting versus what you were getting.

    Does this stored procedure run during production hours when other users are logged on and may be changing data?

    Have you viewed your query's excution plan to see where the delay is occuring?

    Post your SQL procedure so that other members and I can take a look at it.

    blindman

  10. #10
    Join Date
    Aug 2002
    Posts
    21
    This is the code:
    --- beginning of procedure
    CREATE PROCEDURE sp_RJ_CalculateDeferred(@BeginDate smalldatetime, @EndDate smalldatetime) AS

    Declare @RowsAffected INT
    Declare @ProcessDate smalldatetime
    Declare @ProcName varchar(50)

    /*First, flush the table for deferred, then re-create the table */
    TRUNCATE TABLE tbl_deferred

    /*insert all records from master table where journaldate is null */
    INSERT INTO tbl_deferred (apflag, id, fromdate, todate, amount)

    SELECT APFlag, RecordID, FromDate, ToDate, Amount

    FROM dbo.vw_RJ_MasterView

    WHERE (JournalDate IS NULL) AND (FromDate IS NOT NULL) AND
    (ToDate IS NOT NULL) AND (APFlag = 0) AND (RecordType = 'R')

    SET @RowsAffected = @@rowcount
    SET @ProcessDate = getdate()
    SET @ProcName = 'ME-LoadDeferredEntries'

    INSERT INTO vw_ProcessingLog(ProcessDate ,CallingJob,DBName,ProcedureCalled,RowsAffected)
    VALUES (getdate(),'sp_RJ_CalculateDeferred','R&J','Step1' ,@RowsAffected) ;


    /*update daysinperiod tbl_deferred where [apflag = 0] */
    Update tbl_deferred

    Set tbl_deferred.daysinperiod = 31

    Where tbl_deferred.apflag = 0


    /*update daysearned in tbl_deferred where fromdate < @begindate*/
    Update tbl_deferred

    Set tbl_deferred.daysearned = 31

    where fromdate < @begindate


    /*update daysearned in tbl_deferred where fromdate >= @begindate*/
    Update tbl_deferred

    Set tbl_deferred.daysearned = (31- (Day(FromDate)) + 1)

    where fromdate >= @begindate


    /*if If DaysEarned > DaysInPeriod Then DaysEarned = DaysInPeriod*/
    Update tbl_deferred

    Set tbl_deferred.daysearned = daysinperiod

    where daysearned > daysinperiod


    /*if If Fromdate > @enddate Then it's 100% unearned*/
    Update tbl_deferred

    Set tbl_deferred.deferredamount = amount, Calc = 1

    where fromdate > @enddate

    SET @RowsAffected = @@rowcount
    SET @ProcessDate = getdate()
    SET @ProcName = 'ME-ProcessDeferredEntries-NoneEarned'

    INSERT INTO vw_ProcessingLog(ProcessDate ,CallingJob,DBName,ProcedureCalled,RowsAffected)
    VALUES (getdate(),'sp_RJ_CalculateDeferred','R&J','Step2' ,@RowsAffected) ;


    /*If ToDate <= EndDate then it's all earned*/
    Update tbl_deferred

    Set tbl_deferred.deferredamount = 0, Calc = 2

    where todate <= @enddate

    SET @RowsAffected = @@rowcount
    SET @ProcessDate = getdate()
    SET @ProcName = 'ME-ProcessDeferredEntries-AllEarned'

    INSERT INTO vw_ProcessingLog(ProcessDate ,CallingJob,DBName,ProcedureCalled,RowsAffected)
    VALUES (getdate(),'sp_RJ_CalculateDeferred','R&J','Step3' ,@RowsAffected) ;


    /*Calculate Deferred on the rest*/
    Update tbl_deferred

    Set tbl_deferred.deferredamount = ROUND((Amount - ((DaysEarned / DaysinPeriod) * amount)), 2), Calc = 3

    where deferredamount is null

    SET @RowsAffected = @@rowcount
    SET @ProcessDate = getdate()
    SET @ProcName = 'ME-ProcessDeferredEntries-PartialEarned'

    INSERT INTO vw_ProcessingLog(ProcessDate ,CallingJob,DBName,ProcedureCalled,RowsAffected)
    VALUES (getdate(),'sp_RJ_CalculateDeferred','R&J','Step4' ,@RowsAffected) ;

    /*Update Deferred Amount in vw_RJ_MasterView from tbl_deferred*/
    Update vw_RJ_MasterView

    Set vw_RJ_MasterView.deferredamount = ROUND(tbl_deferred.deferredamount, 2)

    FROM dbo.vw_RJ_MasterView INNER JOIN
    dbo.tbl_deferred ON
    dbo.vw_RJ_MasterView.RecordID = dbo.tbl_deferred.ID

    WHERE ( dbo.vw_RJ_MasterView.JournalDate IS NULL) AND ( dbo.vw_RJ_MasterView.RecordType = 'R')


    SET @RowsAffected = @@rowcount
    SET @ProcessDate = getdate()
    SET @ProcName = 'ME-ProcessDeferredUpdateMaster'

    INSERT INTO vw_ProcessingLog(ProcessDate ,CallingJob,DBName,ProcedureCalled,RowsAffected)
    VALUES (getdate(),'sp_RJ_CalculateDeferred','R&J','Step5' ,@RowsAffected) ;
    GO
    ---end of procedure

    As you can see, there are 5 steps in this procedure. This procedure runs monthly. The first 4 steps run a few minutes each, the past two months run a bit more slower than usual. The problem is on the fifth step, which runs 40min in Jan, 6hours in Feb, 5hours March, 26minutes April, 2h May, 6h June, 11h Jul, and 12h Aug. It fluctuated pretty badly, while the other 4 steps are more consistent.

    Thanks for your time and effort on this.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Is this the step that is causing problems:

    Update vw_RJ_MasterView
    Set vw_RJ_MasterView.deferredamount = ROUND(tbl_deferred.deferredamount, 2)
    FROM dbo.vw_RJ_MasterView INNER JOIN
    dbo.tbl_deferred ON
    dbo.vw_RJ_MasterView.RecordID = dbo.tbl_deferred.ID
    WHERE ( dbo.vw_RJ_MasterView.JournalDate IS NULL) AND ( dbo.vw_RJ_MasterView.RecordType = 'R')

    If, as I suspect, vw_RJ_MasterView is a view, then rewrite your query so that it reference the tables directly. Sure, views can be updateable, but I avoid it and I suspect that this is where your problem is occuring.

    With certain exceptions, execution plans for view are not stored, and so the optimizer may use different execution plans in evaluating your statement on different occasions, contributing to variances in execution time.

    Since I avoid running updates on views, especially updates that involve the view in the join, I can only guess what kind of circular locking may be going on in the background to execute this, and which could be contributing to long processing times.

    Anybody else got any comments on this?

    blindman

  12. #12
    Join Date
    Aug 2002
    Posts
    21
    blindman,
    You hit it on the nail. Thanks.
    My question is why execution plan for views isn't stored. Do you avoid using views in select statement with jion on other tables?
    Do others have any comments on this?

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Execution plans for views are not stored unless the view contains a clustered index, and there are restrictions as to what views can contain clustered indexes.

    These days I do not use views a lot. I use stored procedures for user output, because it gives greater flexbility with regards to parameters and security.

    I believe views are not precopiled because the optimizer wants the freedom to choose different execution strategies based on how the view is being used in secondary stored procedures and sql code. The optimizer may find it advantageous to use indexes on the underlying tables that are not returned in the view result set.

    Personally, I don't use views a lot because they hide from me what they are doing while I am programming, and they make code more convoluted.

    I DO use user-defined functions (both scalar and table) a lot. I think functions do retain their execution plans, plus they can take parameters and can be much more complex than a view. They are not updatable, but like I said before I avoid updating views for the same reason I don't cook in my kitchen with dark glasses on. If you can't see what you are working with, sooner or later you are going to get burned.

    blindman

  14. #14
    Join Date
    Aug 2002
    Posts
    21
    I like 'the dark glasses in the kitchen' stuff. But on the second thought, you're blindman. Just joking.
    Your point is well taken. Personally, I don't use views a lot. This is something developed by somebody else.
    Can you recommend some books or articles, regarding to the behind scene stuff in SQL Server?

    Thanks again!

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I've pretty much just read up on technical details in Books Online, and picked apart system stored procedures to see how they work.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •