index and column statistics

Question

Hello all,

What is the TSQL for updating both index and column statistics using sp_updatestats procedure?

Thanks.

Answer 1

Update Statistics SchemaName.TableName

---

Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.

Answer 2

I want to use sp_updatestats as I want to update stats for all tables in the database. So if I just use sp_updatestats without giving any other option will it by default update both index and column statistics?

Answer 3

It will update only statistics that are required based on sys.indexes.

http://msdn.microsoft.com/en-us/library/ms173804.aspx

sp_updatestats executes UPDATE STATISTICS, by specifying the ALL keyword, on all user-defined and internal tables in the database. sp_updatestats displays messages that indicate its progress. When the update is completed, it reports that statistics have been updated for all tables.

sp_updatestats updates statistics on disabled nonclustered indexes and does not update statistics on disabled clustered indexes.

sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.

sp_updatestats can trigger a recompile of stored procedures or other compiled code. However, sp_updatestats might not cause a recompile, if only one query plan is possible for the tables referenced and the indexes on them. A recompilation would be unnecessary in these cases even if statistics are updated.

---

Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.

Answer 4

Thanks for the information. I found some missing column statistics problems from the profiler trace, can this problem be solved by regularly running sp_updatestats for the dataabases?

Answer 5

I am regularly reorganizing indexes to reduce fragmentation, but I still found many missing column statistics events (with auto create stat option on). So I am wondering if using sp_updatestats more frequently would help.

Answer 6

check this out: http://msdn.microsoft.com/en-us/library/ms190397.aspx

Query Has Missing Statistics

If an error or other event prevents the query optimizer from creating statistics, the query optimizer creates the query plan without using statistics. The query optimizer marks the statistics as missing and attempts to regenerate the statistics the next time the query is executed.

Missing statistics are indicated as warnings (table name in red text) when the execution plan of a query is graphically displayed using SQL Server Management Studio. For more information, see Displaying Graphical Execution Plans (SQL Server Management Studio). Additionally, monitoring the Missing Column Statistics event class by using SQL Server Profiler indicates when statistics are missing. For more information, see Errors and Warnings Event Category (Database Engine).

If statistics are missing, perform the following steps:

• Verify that AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS are on.

• Verify that the database is not read-only. If the database is read-only, the query optimizer cannot save statistics.

• Create the missing statistics by using the CREATE STATISTICS statement.

---

Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.