SQL Statistics

In most cases it is wise to let SQL Server automatically update statistics.

To turn on automatic updates for all statistics in a database, if not already on, execute the following statement:

USE master
EXEC sp_dboption ‘MyDatabase’, ‘ auto update statistics’, ‘true’

To turn on automatic updates for all statistics on a specific table, such as Clients, execute the following statement:

USE MyDatabase
EXEC sp_autostats Clients, ‘ON’

To manually update the statistics on specific table, such as Clients, execute the following command:

USE MyDatabase
UPDATE STATISTICS Clients

select
schemas.name as table_schema,
tbls.name as table_name,
i.name as index_name,
i.id as table_id,
i.indid as index_id,
i.groupid,
i.rowmodctr as modifiedRows,
(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt,
convert(DECIMAL(18,8), convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPct,
stats_date( i.id, i.indid ) as lastStatsUpdate,
‘False’ as Processed
into ##updateStatsQueue
from sysindexes i
inner join sysobjects tbls on i.id = tbls.id
inner join sysusers schemas on tbls.uid = schemas.uid
inner join information_schema.tables tl
on tbls.name = tl.table_name
and schemas.name = tl.table_schema
and tl.table_type=’BASE TABLE’
where 0 < i.indid and i.indid < 255
and table_schema <> ’sys’
and i.rowmodctr <> 0
and (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) > 0

select * from ##updateStatsQueue

drop table ##updateStatsQueue

Query to get a list Statistics in the database (from sys.sysindexes table)

select name, rowcnt, rowmodctr,* from sys.sysindexes where rowcnt = 0 and name like ‘%_WA%’

All Statistics objects in the sys.sysindexes have rowcnt = 0 and its name starts from ‘_WA’

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.

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 <database_name>
EXEC sp_updatestats

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.

select rowmodctr,* from sys.sysindexes

From this number, you can estimate when the next automatic update of statistics will occur.

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.

FULLSCAN is the same as SAMPLE RATE 100 with the difference that with SAMPLE RATE, SQL Server 2005 might adjust the rate up (and use a sample rate that is sufficiently high to create useful statistics) and if the requested rate creates more values than is considered needed, it “tries to match the requested sample amount”. I’m not a 100% how it is in 2005, but in 2000 it would actually revert to FULLSCAN if the sample rate exceeded some threshold (for performance). You can check which sample rate you end up with DBCC SHOWSTATISTICS… WITH STAT_HEADER.

Now Update States with SAMPLE may take longer then with FULLSCAN, the explanation to that probably lies in how samples are gathered. Thing is, when you use a sample rate, SQL Server needs to figure out an access path (heap/cl idx/non-cl idx) and it will try to find one that is NOT physically sorted on the first column in the statistics. It does this to provide a more random sample and thus more accurate statistics. For FULLSCAN, this is not an issue so the lowest cost access path is chosen.

SELECT ‘update statistics’ + ‘ ‘ + RTRIM(object_name(I.id)) + ‘ ‘ + RTRIM(name)

/* DATALENGTH (statblob) size,
STATS_DATE (I.id, I.indid) last_updated */
FROM
sysindexes as I

WHERE
OBJECTPROPERTY(I.id, N’IsUserTable’) = 1 AND
INDEXPROPERTY (I.id , name , ‘IsAutoStatistics’ ) = 1 AND
DATALENGTH (statblob) is null

You can run sp_updatestats against the database, it will update the statistics on those tables which were modified recently.