Daily maintenance plan causing performance problems

  • We have recently started seeing our daily maintenance plan causing performance problems. Specifically, there seems to be an increase in blocking when this job runs. The job usually runs in 4-6 minutes, but when the problem happens it gets hung for hours - until someone kills it or the web servers are rebooted.

    The maintenance plan has these steps:

    Update Statistics

    Reorganize Index

    Cleanup History

    Are there any "usual suspects" I can start with to diagnose and resolve this problem?

    Any help would be greatly appreciated.

    Thanks,

    webrunnner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Web server has nothing to do with the maintenance jobs. Since rebooting the web server “releases” the maintenance jobs, I suspect that you have a blocking chain that the job is caught in the middle of the chain. Did you check if the maintenance job is doing something? Did you check if it is being blocked by another process?

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • When you are doing Update Stats and doing Index maintenance, it does a schema lock. That cause Blocking. That could be why you see that everything is hanging.

    -Roy

  • Thanks for your replies.

    Based on your information and on what we know so far, it does seem like the rebooting of the web servers may have released locks that were in turn keeping the maintenance plan from completing. In other words, it looks like the causation was [something] blocking [maintenance plan], because if it were the other way around ([maintenance plan] blocking [something]), the maintenance plan would have finished in its normal time. Is that reasoning correct?

    It is good to know about the schema lock, too. It seems like we will need to simply make the web site unavailable to users during the maintenance job. Is that what is usually done? Does anyone have advice along those lines to keep other processes from interfering with the maintenance?

    Many thanks again for any help.

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • You could try Isolation level (Row level version[/url]). I am not sure if this will help or not because I have never tried running it with maintenance plan of Statistics Update

    -Roy

  • I’m not sure that the schema locks cause blocking. As far as I know schema locks won’t let you modify the table’s structure but it doesn’t block DML statements such as select, insert, update and delete.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Adi Cohn-120898 (9/24/2009)


    I’m not sure that the schema locks cause blocking. As far as I know schema locks won’t let you modify the table’s structure but it doesn’t block DML statements such as select, insert, update and delete.

    Adi

    Thanks - also, if the maintenance plan is being blocked, its schema locking wouldn't be the culprit, would it? I just want to make sure I understand.

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Adi Cohn-120898 (9/24/2009)


    I’m not sure that the schema locks cause blocking. As far as I know schema locks won’t let you modify the table’s structure but it doesn’t block DML statements such as select, insert, update and delete.

    Adi

    It depends on the type of Schema lock.

    If it is SCH-S, then it will let you access the table data. But if it is SCH-M, it will not.

    -Roy

  • This is taken from a Microsoft article about SQL Server 2000 http://support.microsoft.com/kb/195565%5B/url%5D. I couldn’t find an official article about SQL Server 2005.

    Sch-M-UPD-STATS: Schema Modification Lock

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

    This is a non-blocking lock that is used by the system to ensure that

    only one automatic UPDATE STATISTICS process is run against a table at

    any given point in time. The sp_lock stored procedure will report this

    lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • I think it is much recommended to do the following:

    1-Run profiler and check all locks with their wait times and also deadlocks all over the day until you clarify well the reasons of Hung.

    2-Also I recommend heavily to generate the correct statistics and indexes using Performance tuning plan to be good for performance while updating them .In other words , if the exist statistics and indexes are suitable for performance of web application transactions , then updating them will raise performance heavily and if not may affect some web transactions.

    3-Check scheduling of the job of Reorganize especcially outside working days or least working time as

    long or complex select query will cause share lock with this job causing this job especially hung

    [font="Arial Black"]Performance Guard[/font]

    [font="Courier New"]Just ask me to get 0 sec for all queries[/font]

  • Never do reorganize.  On large tables it will take forever and on small tables why reorganize when you can just rebuild.  Treat all your server maintenance like a man made lake filling up fast with no overflow. Be as PROACTIVE as possible. Log files every 5 minutes, all databases tuned using DTA wizard.  All missing indexes created so the complier asks for none!  You will probably need three or four iterations of  DTA wizard.  Run it and apply indexes until it doesn't ask for any more. The indexes also need to be defragmented daily. Its all been given to you on a platter by the Microsoft software engineers.  Use what they created! I've seen whole DBA groups hold organizations hostage for decades to their own incompetence, all because they don't follow through on proper maintenance as described above. Sad since you could almost have a button pushing monkey do it if you could get them to follow through.

     

     

  • Vernon Jimmerson-306463 wrote:

    Never do reorganize.  On large tables it will take forever and on small tables why reorganize when you can just rebuild.  Treat all your server maintenance like a man made lake filling up fast with no overflow. Be as PROACTIVE as possible. Log files every 5 minutes, all databases tuned using DTA wizard.  All missing indexes created so the complier asks for none!  You will probably need three or four iterations of  DTA wizard.  Run it and apply indexes until it doesn't ask for any more. The indexes also need to be defragmented daily. Its all been given to you on a platter by the Microsoft software engineers.  Use what they created! I've seen whole DBA groups hold organizations hostage for decades to their own incompetence, all because they don't follow through on proper maintenance as described above. Sad since you could almost have a button pushing monkey do it if you could get them to follow through.

    Vernon Jimmerson-306463 wrote:

    Man... be really careful... There's some really bad recommendations in the above...

    Vernon Jimmerson-306463 wrote:

    all databases tuned using DTA wizard.  All missing indexes created so the complier asks for none!

    Good lord NO!!!  You're just creating more sources of page splits, slowing down ALL INSERTs and DELETEs and a good number of updates and not all of the index recommendations are actually going to work correctly.  At the very least, they we substantially increase the amount of required storage and backup/restore times and space.  DTA can provide some good hints but it generally is ineffective and, like I said, the indexes that "clippy" builds are horrible.

    Vernon Jimmerson-306463 wrote:

    The indexes also need to be defragmented daily.

    So... you have the coded demonstrable proof that fragmentation always causes substantial performance issues that you also have the same type of proof that clearly demonstrates that defragmentation fixes it all?  If you say "Yes", post it so I can prove why you're wrong.  If you say "No", then you need to post a retraction for posting such bad information.

    Vernon Jimmerson-306463 wrote:

    I've seen whole DBA groups hold organizations hostage for decades to their own incompetence, all because they don't follow through on proper maintenance as described above.

    Speaking of incompetence, you do realize that MS changed all  of that back on 20 Apr 2020, correct?  And, you also realize that the guy who first came up with "some recommendations to keep MS happy" basically published a retraction in 2008, correct?

    https://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-from/

    And you DO realize that REBUILDING an index at 0/100% for anything that's fragmenting WILL cause massive page splits on the "morning after" AND you do realize that if you're basing your index maintenance only on logical fragmentation that you're actually causing more damage than you fixing, correct?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I thought this must be from years ago but only a few months. Maybe just the reply.  I've had the same discussions, or more like yelling matches with DBA groups in the past, and they just refuse to be competent. Can I assume you have used the top two options in the SSMS tools menu and had bad results?  You do know that most people use a DEV environment to test?  You do know there are system DMVs that will show you what is being used and what isn't and there is something referred to as a drop statement?  Frankly you just sound like one of the DBAs making life miserable for me as a developer with more excuses like it's the disk sub-system when in reality they tried to use AlwaysOn(TheFritz) when they didn't have the resources to do so.  Nothing will change what I have laid out.  The developer complains about unusually slow performance, the DBA defragments the database, the developer is happy. Thats how it works as a competent DBA!  Frankly I don't care what the DB does in the background as long as it performs. I started out low level programming!  I don't need all that complexity now at the end of my career.

  • Vernon Jimmerson-306463 wrote:

    I thought this must be from years ago but only a few months. Maybe just the reply.  I've had the same discussions, or more like yelling matches with DBA groups in the past, and they just refuse to be competent. Can I assume you have used the top two options in the SSMS tools menu and had bad results?  You do know that most people use a DEV environment to test?  You do know there are system DMVs that will show you what is being used and what isn't and there is something referred to as a drop statement?  Frankly you just sound like one of the DBAs making life miserable for me as a developer with more excuses like it's the disk sub-system when in reality they tried to use AlwaysOn(TheFritz) when they didn't have the resources to do so.  Nothing will change what I have laid out.  The developer complains about unusually slow performance, the DBA defragments the database, the developer is happy. Thats how it works as a competent DBA!  Frankly I don't care what the DB does in the background as long as it performs. I started out low level programming!  I don't need all that complexity now at the end of my career.

    I used to be a front-end Developer... that's why I don't rebuild indexes all the time 😉 ... The page splits that came after the rebuilds would slow down the code too much.  Reducing the Fill Factor didn't help in most of the cases because INSERTs on clustered indexes with "ever increasing"keys go in at 100% and then the Updates the people did right after that would cause massive page splits.  Adding DTA indexes that didn't actually get used by the code or caused faster indexes to not be used also suffered from massive page splits.

    Getting back to what you said before...

    Vernon Jimmerson-306463 wrote:

    The indexes also need to be defragmented daily.

    I'll ask the same question I asked you before but in a slightly different way than I did before...

    Do you have coded proof that REBUILDing indexes causes substantial enough improvements in performance beyond what simply rebuilding the statistics will do to actually make the REBUILDs worthwhile?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    I used to be a front-end Developer... that's why I don't rebuild indexes all the time   ... The page splits that came after the rebuilds would slow down the code too much.

    I thought reorganisation of indexes caused page splits but not rebuilds?

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

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