Today we will write a simple script which will generate UPDATE STATISTICS for all the database on current SQL Server. Statistics in SQL Server refers specifically to information that the server collects about the distribution of data in columns and indexes. This data is used by the query optimizer to determine the plan of attack for returning results when you run a query. While in the majority of cases SQL Server takes care of this statistical recordkeeping for you automatically, it's useful to have some understanding of what's going on, and to know when you might need to intervene.
Automatic Statistics
By default, SQL Server databases automatically create and update statistics. Although you can disable this (by setting a property of the database), you should almost certainly leave it on. SQL Server needs these statistics to do a good job with query processing, and it's likely to be smarter than you about what and when to update. The information that gets stored includes:
- The number of rows and pages occupied by a table's data
- The time that statistics were last updated
- The average length of keys in a column
- Histograms showing the distribution of data in a column
- String summaries that are used when performing LIKE queries on character data
SQL Server's engine will update the statistic when:
- When data is initially added to an empty table
- The table had > 500 records when statistics were last collected and the lead column of the statistics object has now increased by 500 records since that collection date
- The table had < 500 records when statistics were last collected and the lead column of the statistics object has now increased by 500 records + 20% of the row count from the previous statistics collection date
Set nocount on
DECLARE @minId int, @maxId int
DECLARE @DB Nvarchar(100)
Create table #tmp
(
ID int Identity,
Name varchar(100)
)
INSERT INTO #tmp
SELECT [name]
FROM master..sysdatabases
WHERE [name] NOT IN ('model', 'tempdb')
ORDER BY [name]
Select @minId = 1, @maxId = count(1) from #tmp
While @minId <= @maxId
Begin
SELECT 'Use ' + name + CHAR(13) + 'Exec sp_UpdateStats ' from #tmp Where Id = @minId
Set @minId+=1
END
drop table #tmp
This code yields following results:Use AdventureWorks
Exec sp_UpdateStats
Use AdventureWorksDW
Exec sp_UpdateStats
Use LearningCT
Exec sp_UpdateStats
Use master
Exec sp_UpdateStats
Use msdb
Exec sp_UpdateStats
Use Northwind
Exec sp_UpdateStats
Use ReportServer$MSSQL2008
Exec sp_UpdateStats
Use ReportServer$MSSQL2008TempDB
Exec sp_UpdateStats
Use SensitiveDB
Exec sp_UpdateStats
Use testdb
Exec sp_UpdateStats
Now copy this text & paste it into a query window in SQL Server Management Studio, then execute it.
When you execute the code, you will see the output like this:
This shows which all indexes are updated.
Happy SQL Coding
