Update statistics

  • Dear All,

    I run a stored procedure and it executes “infinite”. Stopped it and then I decided to update the statistics, because I’ve noticed for some tables they had never been updated.

    I run a simple ssis package only to update the statistics for the database and the run is "infinite". It was advancing up to 37% and then froze.

    Then I thought something is wrong with the database, and run DBCC CHECKDB and it runs “infinte” also.

    The database size is 202GB

    Available space is 2GB

    So I don’t know what to do next?

    Can anyone suggest something?

    Regards,

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • check for blocking while any of the processes (SP, dbcc, stats) is running. you can use this to identify any blocking SPIDs.

    SELECT s.session_id AS spid

    ,s.[status]

    ,s.login_name AS loginName

    ,r.blocking_session_id as BlkBy

    ,COALESCE(DB_NAME(COALESCE(r.database_id,u.database_id)),'') AS dbName

    ,r.wait_type AS waitType

    ,r.wait_time AS waitTime

    ,s.login_time as loginTime

    ,s.last_request_end_time AS lastBatch

    ,s.[program_name] AS programName

    ,t.[text] AS lastSQLText

    FROM sys.dm_exec_sessions AS s

    LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id

    LEFT JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id

    LEFT OUTER JOIN

    (SELECT request_session_id,database_id = MAX(resource_database_id)

    FROM sys.dm_tran_locks

    GROUP BY request_session_id) u

    ON s.session_id = u.request_session_id

    CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t

    WHERE s.is_user_process = 1

    and r.blocking_session_id is not null

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • ok try running sp_updatestats as well if you have not done that already.

    sp_updatestats is relatively lighweight because:

    1) it only uses default sampling.

    2) and it only updates statistics for tables \ indexes which are not out of date.

    Further, sp_updatestats gives a nice readable output to confirm as to which tables \ indexes were actually updated and which were skipped because the statistics were in place already.

    if it freezes again you can try running dbcc inputbuffer(spid), where spid is the value sql server process id listed in your query editor window from which the sp_updatestats was executed..

    BTW what is your server version \ service pack \ cumulative update version?

  • Also check the fragmentation of the tables. Probably those need to be defragmented.

  • Thank you All

    All helped.

    I finally managed to update the statistics using SP_updatestats.

    But, what i noticed, that some statistics were updated, some not. When i right click on some statistics, it still shows "Statistics for these columns were last updated: (never) "

    Thank you so far

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • Those tables are empty (no rows)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (7/17/2012)


    Those tables are empty (no rows)

    You're correct.

    Thanks

    Igor Micev,My blog: www.igormicev.com

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

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