Log Shipping – NORECOVERY vs. STANDBY mode

This may provide some light on the usage of DBCC UPDATEUSAGE and sp_updatestats

DBCC UPDATEUSAGE corrects the rows, used, reserved, and dpages columns of the sysindexes table for tables and clustered indexes. Size information is not maintained for nonclustered indexes.

If there are no inaccuracies in sysindexes, DBCC UPDATEUSAGE returns no data. If inaccuracies are found and corrected and the WITH NO_INFOMSGS option is not used, UPDATEUSAGE returns the rows and columns being updated in sysindexes.

Use UPDATEUSAGE to synchronize space-usage counters. DBCC UPDATEUSAGE can take some time to run on large tables or databases, so it should typically be used only when you suspect incorrect values returned by sp_spaceused. sp_spaceused accepts an optional parameter to run DBCC UPDATEUSAGE before returning space information for the table or index. ( BOL )


What is UPDATE STATISTICS ?

SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index(es) to use in query processing. Users can create statistics on nonindexed columns by using the CREATE STATISTICS statement. Query optimization depends on the accuracy of the distribution steps:
- If there is significant change in the key values in the index, rerun UPDATE STATISTICS on that index.
- If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated, use UPDATE STATISTICS.

Statistics can be created or updated on tables with computed columns only if the conditions are such that an index can be created on these columns.


Why we need to use UPDATE STATISTICS while we use as the part of SQL Server database maintenance?


If you have a performance issue with a query, the very first step you should take before analyzing it is to update the statistics on all the tables in your database. This is because many query performance issues are due to outdated statistics, and updating them before troubleshooting query performance may save you a lot of time. If the query is still giving you trouble after updating the statistics, then you can begin your troubleshooting.

It is recommended that "Auto update statistics" should be disabled. While we do disable this option, we should have a maintenance plan for this activity in day-to-day basis.

It is recommended that "Auto update statistics" should be disabled. While we do disable this option, we should have a maintenance plan for this activity in day-to-day basis. The best practice says do the UPDATE STATISTICS for all tables and for all databases every night. This is because the auto-update option is less than perfect, and accurate and up-to-date statistics is so important to overall performance of SQL Server.

Of course, if this is not possible because of time constraints, then you will have to perform this step less often, or even not at all, and rely on the auto-update feature. I recommend this has to be done manually.

What does this activity do?

For each table in your database, SQL Server automatically maintains this information on them:
- The number of data pages used by each table.
- The number rows in each table.
- The number of INSERTS, UPDATES, and DELETES that affect the keys of the table since the last statistics update.

Besides the table statistics, SQL Server can maintain statistics on all of the indexes in your database. SQL Server maintains this index statistics information:
- A histogram of the distribution of the data in the first column of the index.
- The densities of all column prefixes.
- The average key length of the index.

SQL Server can also collect the above statistics for any column you specify. This data can be used by the Query Optimizer to make better decision. Column statistics aren't automatically collected unless you tell SQL Server to collect them.

In most cases, you will probably allow SQL Server to update index statistics automatically. This feature can be changed by setting the database option "Auto Update Statistics" to either true of false. Be default, this feature is set to true, which means that index statistics are automatically updated, which is not recommended.

Why it is not recommended is, in some very busy SQL Servers, this feature can interfere with normal daily activity. This is because this feature may start its own, at times when the server is already very busy, which may degrading performance. In these cases, it is often better to turn this feature off and to manually or schedule an update statistics during off-peak time in database usage.


How does SQL Server know when to update statistics?

SQL Server follows a very specific set of rules on when it should update the statistics of an index. Here they are:
- If the number of rows in a table are greater than 6, but less than or equal to 500, then statistics are automatically updated when there have been 500 modifications made.
- If the number of rows in the table are greater than 500, then updates are automatically made when (500 plus 20 percent of the number of rows in the table) have been modified.

If you like, you can check to see how many modifications have been made to a table, and at the same time estimate when an automatic statistics update will occur. If you go to the sysindexes table of the database in question, and look at the rowmodctr column, it will show you what the count is. From this number, you can estimate when the next automatic update of statistics will occur.



How we can do the UPDATE STATISTICS in SQL Server?

If you want to find out if an index has had its indexes updated or not, you can use the command, like this:
DBCC SHOW_STATISTICS (table_name , index_name)
This command will not only tell you when statistics were last updated, but also provide you information on the kind of statistics that has been collected for the index you are examining.

You can update the statistics using this command:
USE
EXEC sp_updatestats

SP_UPDATESTATS refers to work against all user-defined tables in the current database.
For the stored procedures performance you need to recompile them in order to take the new plan in to the memory, so run SP_RECOMPILE for all or required stored procedures to compare the sequence of performance