Post reply

Auto Update Statistics is On

  • November 4, 2008 at 8:24 pm

    #195450

    Hello DBA

    Anyone knows how to:

    1. Logging detect auto update statistics

    2. any queries can capture the data, if the auto update statistics is running

    3. I have run profiler but this one cannot see any object name, what is updated.

    because i dont want to used manual update. i need to know how to capture the auto update statistics that is on in sql 2000

    Previously a run job every other day for sp_updatestats but this one is cause of slowness in my production database they changed the execution plan of the indexes and table. I stopped the job, now is back to normal.

    Now my worry is how will I know if the auto update statistics is running, and anyone have a query to capture this thing. tested already in profiley but not accurate to get info.

    DBA Anyone can help me.

    Thanks

    Ayie

  • November 5, 2008 at 1:59 pm

    #894518

    The autoupdating of stats usually improves execution plan.

    Perhaps you can try this in a test-environment

    CHECKPOINT --writes dirty blocks to disk

    DBCC DROPCLEANBUFFERS --cleans the cache

    DBCC FREEPROCCACHE --cleans the stored proc cache.

    Then test the troublesome statements. (Perhaps a stored procedure needs the WITH recompile option)

  • November 5, 2008 at 2:41 pm

    #894543

    Hi,

    There is an AUtostats event that ýou can capture with Profiler (under Object in 2000 and under Performance in 2005).

    Does this answer your question?

    /Elisabeth

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

  • June 27, 2011 at 1:28 am

    #1344328

    Hi Friends,

    Can anyone please suggest, using UPDATE STATISTICS is a good idea working on PRODUCTION environment or not??

  • June 27, 2011 at 5:32 am

    #1344419

    Ayie

    2. any queries can capture the data, if the auto update statistics is running

    Try using sp_autostats for some representative table(s) it will return information on wether aut_stats in on or off and the date time last updated. For example:

    sp_autostats 'Person.Contact';

    Result:

    Index Name Autostats Last updated

    [PK_Contact] ON 2011-06-27 07:15:39.063

    [_WA_Sys_00000003_0425A276]ON2011-06-27 07:15:33.663

    http://msdn.microsoft.com/en-us/library/ms188775(v=sql.90).aspx

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • June 27, 2011 at 5:41 am

    #1344426

    shilpa.shankar87 (6/27/2011)

    Hi Friends,

    Can anyone please suggest, using UPDATE STATISTICS is a good idea working on PRODUCTION environment or not??

    Plz start a new thread. And yes it should be on UNLESS you have a very specific problem. I'd suggest a possible workaround for it but this is not available in sql 2K.

Viewing 6 posts - 1 through 5 (of 5 total)

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