update stats with user on systems - big bad or little bad?

[WiccaChic]

I am sure its counter productive, but is it bad for the databases health to update statistics in the course of the day while users are using the database? Aside from a performance standpoint?

 

[vpshriyan]

Hi,

I would suggest you NOT to fire update statistics while there are many users active for a database. While updating statistics for a table, the database server puts a shared lock on the table, resulting in non-availability of data for update purpose. Hence, applications will be hit hard in an OLTP environment. So far, I've not encountered any other problems beside performance degradation and application waiting for lock to be released, while updating statistics while table is being used by the users. But, one never knows, potentially there could be hidden problems yet to see the surface.

A point to be noted that a syntax like:
update statistics;

will take longer to execute against individual statements issued against tables. To generate such statement you may fire the SQL which generates a script containing table wise statements.

output to aa without headings select 'update statistics low for table '||tabname||';' from systables where tabid>99 ;

Regards,
Shriyan
"We cannot direct the wind, but we can adjust the sails."