I work with multiple customer databases in several SQL Server 2008 and SQL Server 2005 production database servers. The data in our production environment constantly changes. Updating the statistics on the tables where a data modification took place is important because it helps the queries that rely on those tables run faster.
Our databases range in size from 50GB to more than 1TB, so running the UPDATE STATISTICS statement on an entire database is time-consuming. Although some data changes involve many tables (and hence millions or even billions of rows) in a given database, other data changes involve only a handful of tables. Why run UPDATE STATISTICS on all the tables when only a handful of tables had data modifications?
To save time and resources, I wrote a stored procedure, sp_DBARunUpdateStats, that updates statistics only on those tables where the data has been recently changed by INSERT, DELETE, or UPDATE statements. (I also wrote a related stored procedure, sp_DBAGetUpdateStats, that reports on the last time the UPDATE STATISTICS statement was run against all the table indexes in a database. You can read about this stored procedure in the sidebar "Stored Procedure Provides UPDATE STATISTICS Report.") After I show you how to run sp_DBARunUpdateStats, I'll explain how it works.
How to Run sp_DBARunUpdateStats
The sp_DBARunUpdateStats stored procedure is designed to reside in the master database. That way, you can invoke this stored procedure from the Query Analyzer regardless of the database the current session is using. You can also invoke sp_DBARunUpdateStats from a SQL Server Agent job. (If desired, you can store sp_DBARunUpdateStats in another database as long as the call to the stored procedure is fully qualified.)
The stored procedure takes four parameters:
- @DBName. You use this mandatory first parameter to specify the name of the database that contains the tables you want to update.
- @ModifiedTableOnly. You use this optional second parameter to indicate the scope of the update. You specify 'Y' when you want to update the statistics on only those user tables where the data was recently modified (i.e., modified since the last time UPDATE STATISTICS was run).You specify 'N' when you want to update the statistics on all the user tables. The default value is 'N'.
- @RunSPRecompile. You use this optional third parameter to indicate whether you want to run the sp_recompile system stored procedure on the updated tables ('Y') or not ('N'). The default value is 'Y'.
- @DisplayOutput. You use this optional fourth parameter to indicate whether you want the output displayed ('Y') or not ('N'). The default value is 'Y'.