Post reply

Update Stats Maint Plan and Service Pack 2a

  • March 9, 2007 at 8:57 am

    #177113

    We installed Service Pack 2a on our SQL Server 2K5 boxes a couple of days ago, and immediately the duration times for the UpdateStats maintenance plans increased.  Some show HUGE increases in duration times (2, 3, and 4 times the old duration) and some not so much, but ALL of the boxes show measurable increases in duration times.

    Has anyone else experienced this, and is there anything that can be done about it?

     

  • March 12, 2007 at 2:55 am

    #694309

    Does this affect you?

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=263&messageid=349578

  • March 12, 2007 at 7:25 am

    #694351

    Andrew: Thank you for your reply. No, it does not apply. My message says "duration" of a single update stats maintenance execution, not the time interval between separate executions of the update stats maintenance.

  • March 16, 2007 at 2:29 pm

    #695545

    Try this.

    Update the Maintainence plan and save the changes ( do not make any ).

    Make sure that the Jobs etc, it creates are run by "sa" ( or if you are using the default that the maintainence plan is giving )

    Sp2a for SQK2K should not increase the execution times that high ( 3-4 times ). There must be something else going on.

    Kindest Regards,

    Kunal Gandre
    >
    Snr. SQL DBA

  • March 16, 2007 at 2:42 pm

    #695547

    kunal, thank you for your reply. We have already looked at all of the obvious causes tried all of the obvious fixes and they didn't work, which is why I posted on this forum, hoping that maybe someone else has had the same problem and found a fix for it.

  • March 16, 2007 at 3:15 pm

    #695556

    Can i ask you why are you using Maintenance plans anyways ?

    There are good only for small applications, i have seen them crumble against some big databases even in SQL 2000 and 7.0.

    Was your Db in Full mode before the SP2a was applied ?

    What was the average mem utilization before ? is it a major difference now ?

    Which maintenance plan is taking long ?

    Can you try to create maintainence plans for different tasks ? like 1 for re-indexing...........etC ? and also at 1 database per plan ?

    Can you explain ur maintainence plan ? What exactly you are doing ? Are you shrinking the database ? How many databases ? How big are they ?

     

     

    Kindest Regards,

    Kunal Gandre
    >
    Snr. SQL DBA

  • March 28, 2007 at 7:45 am

    #697394

    I have the same issue, in SP2 the added FULLSCAN to the criteria, which was not there before with no SP.  So maybe full scan was not the default value before?

    Before SP2:

    UPDATE STATISTICS [dbo].[TableName]

    After SP2:

    UPDATE STATISTICS [dbo].[TableName]

    WITH FULLSCAN

     

    Bigger problem I have is when taking off the FULLSCAN and giving it a percent, it fails due to having to have FULLSCAN and NORECOMPUTE on indexes, with no way to seperate them out (that I have found).

    So any suggestions for that, or am I just going to have to go back and hand craft my maintenance again?

  • May 9, 2007 at 2:09 pm

    #705384

    I am experiencing the exact same issue. I upgraded two development databases with SP2a.UPDATE STATISTICS used to take 1.5 hours to to run. Now they are taking 5 days.

  • May 10, 2007 at 11:03 am

    #705584

    interesting

    we have a few SP2 boxes for testing and i have never noticed it, but they are blazing fast. i run update stats via script and dump the command to a separate DB for later viewing to see how long each step takes

  • May 11, 2007 at 12:59 pm

    #705970

    Just wondering what the build # is for SP2a?

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • May 14, 2007 at 8:31 pm

    #706372

    I ran into the same problem. Changed the sample size to 20% and still no luck so I finally just put in a T-SQL task and used sp_updatestats which is supposed only update statistics needing updated. Runs much faster now.

  • January 1, 2008 at 1:41 pm

    #763875

    problem : timeout expired

    this solution is magic

    I try to configure the timeout parameter by right clicking on ther SERVER NAME

    and change the report manager file

    it does not work

    ===== but this one work magic (reminder: table has not even been index yet), so this is wonderful ===

    use AdventureWorks

    go

    UPDATE STATISTICS Sales.SalesTerritory

    WITH FULLSCAN

    go

    UPDATE STATISTICS Sales.SalesPerson

    WITH FULLSCAN

    go

    UPDATE STATISTICS HumanResources.Employee

    WITH FULLSCAN

    go

    UPDATE STATISTICS Person.Contact

    WITH FULLSCAN

    go

    UPDATE STATISTICS Sales.SalesOrderHeader

    WITH FULLSCAN

    go

    =========== and then I run this sample report (territory) on AdventureWorks again ===

    use AdventureWorks

    go

    SELECT ST.Name, SP.SalesPersonID, C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue

    FROM Sales.SalesTerritory ST INNER JOIN

    Sales.SalesPerson SP ON ST.TerritoryID = SP.TerritoryID INNER JOIN

    HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID INNER JOIN

    Person.Contact C ON E.ContactID = C.ContactID INNER JOIN

    Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID

    GROUP BY ST.Name, SP.SalesPersonID, C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue

    ORDER BY ST.Name

    ------ I think this happen again and again in SQL Server, esp new, not performance tuned database, but people always overlook the UPDATE STATISTICS -- thanks

    xx3xxx

Viewing 12 posts - 1 through 11 (of 11 total)

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