Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
update stats with user on systems - big bad or little bad?
- Thread starter WiccaChic
- Start date
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."
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."
- Status
Similar threads
- Locked
- Question