Update Statistics

Hello, during the execution of UPDATE STATISTICS to the tables of my BD, as it is the impact that produces the execution. Thanks _________________________________________________________________ MSN Amor: busca tu ½ naranja http://latam.msn.com/amor/

I believe that you are asking: "What is the impact on my database server of executing UPDATE STATISTICS commands?" If that is it, here is my answer: Overall there is little impact of running UPDATE STATISTICS. Follows details on what effects/impacts I have observed: - Since update statistics has to read all or at least a significant percentage of the rows in a table, and sort the data, there will be contention for IO bandwidth resources and memory and temporary disk space for intermediate sort results. - The table being updated is not locked, however, there will be brief row level locks taken on rows in the systables, syscolumns, sysdistrib, and sysindexes or sysindices tables at the time that the calculated results are saved. - If you have stored procedures that reference the updated table, they will have to be recompiled by executing UPDATE STATISTICS FOR PROCEDURE/ FUNCTION ...; for each. If this is not done the procedure/function will be recompiled the next time it is called which will at least slow it down and may return an error. - If you have any running processes with prepared statements that reference the updated tables, their query plans may become invalid in which case the tasks will receive a -710 error the next time the prepared statement is executed either by an execute statement or an OPEN cursor statement. The task either has to reprepare the statements in response to the -710 or be killed and restarted. -- Note: This last problem has finally been resolved in IDS 11.10 which automatically reprepares such statements when they are executed without any error. FYI: Consider using my dostats utility for running update statistics on your tables. It automatically implements the recommended suite of commands to update your statistics to optimum levels with minimum resource use. It also contains options that help you manage when to update stats on tables and to automate the process using cron. Dostats is downloadable from the IIUG Software Repository in the package utils2_ak. Art S. Kagel ----- Original Message ----- From: Juan Pablo <ids@iiug.org> To: ids@iiug.org At: 9/03 19:36:10 Hello, during the execution of UPDATE STATISTICS to the tables of my BD, as it is the impact that produces the execution. Thanks _________________________________________________________________ MSN Amor: busca tu ½ naranja http://latam.msn.com/amor/ ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.