Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: run sp_updatestats in production hours?

    Hi,

    We are experiencing performance problems. With Google I've found a few references toward the use of sp_updatestats.

    My users are now facing frequent time-outs. Will running sp_updatestats make it worse?
    In other words, can I run sp_updatestats during production hours, when all the users are working on our system?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you sure that will fix your issue?

    Is there a specific bottleneck/query that could be optimised?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Running update statistics probably won't hurt, if the users are already getting timeouts. Have you isolated what query(ies) are timing out? If they share a single table in common, then updating statistics on just that one table would likely be the best option. You may also need to run sp_recompile on that table to make (more) sure that old plans get flushed.

    Also, remember to get a look at the query execution plan before, and after, so you can see something has changed.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by gvee
    Are you sure that will fix your issue?
    The problem is, this is a MS Dynamics CRM database. In other words, one big black box.

    I admit, I'm clueless. I'm learning SQL Server Profiler as we speak.
    You could help me a lot with some good tutorial URLs on how to find and solve bottlenecks (using SQL Server Profiler).
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    To find queries that timeout with profiler, monitor
    Stored Procedures: RPC Completed
    T-SQL: SQL batchCompleted

    Put a filter on Duration for 30,000 (30 seconds).

    Anything that appears will likely have timed out. 30 seconds is the default, but that can be overridden.

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Do you reorg/rebuild indexes regularly ?

    Blocking ? Perhaps need nolock hints in some queries ? Not sure what you can do with vendor CRM system SQL.

    Do you see a lot of self blocking, perhaps you need to adjust maxdop setting ?

    I found post SQL 2000 statistics seem to stale with minimum DML operations and stats need to be updated frequently, found ourselves having to snap data for many operations against large tables (even with indexed queries).

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I have run sp_updatestats. It took about 1 min. 30 without increasing the CPU usage noticeable.
    Do you reorg/rebuild indexes regularly ?
    No.
    Do you see a lot of self blocking?
    No. I ran sp_who2 for a while, no blocks or just for a very short time.
    "To find queries that timeout with profiler, monitor
    Stored Procedures: RPC Completed
    T-SQL: SQL batchCompleted
    Put a filter on Duration for 30,000 (30 seconds)."
    I found 2 queries that way. One of the views they use has a FROM clause with 22 LEFT OUTER JOINs and ends with a CROSS JOIN.
    I was overwhelmed by the execution plan of this view (any good references on how to interpret it?). So I started looking at the SQL script itself. The WHERE clause contained
    Code:
        Quote.QuoteId in 
        (
            select  POA.ObjectId from PrincipalObjectAccess POA 
            join SystemUserPrincipals sup on POA.PrincipalId = sup.PrincipalId
                where sup.SystemUserId = u.SystemUserId and
                    POA.ObjectTypeCode = 1084 AND 
                    ((POA.AccessRightsMask | POA.InheritedAccessRightsMask) & 1)=1
        )
    I replaced it by
    Code:
    	EXISTS 
        (
            select  1
    		from PrincipalObjectAccess POA 
    			join SystemUserPrincipals sup on 
    				POA.ObjectId = Quote.QuoteId AND
    				POA.PrincipalId = sup.PrincipalId
            where sup.SystemUserId = u.SystemUserId and
                  POA.ObjectTypeCode = 1084 AND 
                  ((POA.AccessRightsMask | POA.InheritedAccessRightsMask) & 1)=1
        )
    Thereby also using an index on (POA.ObjectId, POA.PrincipalId). Average execution time went from 58 seconds to 23 seconds. Though I still think 23 second is a very long wait to get someone's identification data (name, birth date, address, ...) giving his id.

    But this hasn't solved the performance problem of course. 22 left outer joins and a cross join in 1 view ... who can do better? Glacial execution times are no problem.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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