Update statistics

Question

Update statistics

Igor Micev

SSC-Dedicated

Points: 33110
More actions
July 17, 2012 at 7:34 am

#261221

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

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

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

Robert Klimes SSCoach Points: 17865 More actions · Answer 1

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]

Vikrant S Patil SSCrazy Points: 2294 More actions · Answer 2

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?

sqlnaive SSCoach Points: 17437 More actions · Answer 3

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

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 6

GilaMonster (7/17/2012)
Those tables are empty (no rows)

You're correct.

Thanks

Igor Micev,My blog: www.igormicev.com