Transferring SQL Server Statistics From One Database to Another

http://www.sql-server-performance.com/2005/transferring-statistics/

Many production databases today are in the tens or hundreds of gigabytes in size. That size is not a problem for production hardware. However, developers generally like to work on their personal computers, including notebooks with limited disk capacity, so a 10-100GB database size is an issue. Furthermore, it can be impractical to transfer such large data files over the network on a regular basis, if working time is lost. For this reason, it is preferable to work on a smaller sized database with the same schema.

 

The major difference between a small development database and the full-sized production database is the statistical data distribution. This leads to different cost estimates and potentially different execution plans. In this circumstance, the developer is effectively blind to potential performance issues until the actual execution plan can be verified on a full-sized database.

A solution to the different execution plan effect would be to transfer the statistics from the production database to the smaller development database. Ideally, this capability should be internal to SQL Server, and readers are encouraged to submit a request for this feature to sqlwish@microsoft.com. Since this feature is reportedly in Sybase Adaptive Server Enterprise, someone else has apparently also thought of this. In the interim, an alternative method is presented.

SQL Server Statistics

SQL Server uses cost-based optimization. The key to cost-based optimization is a method to estimate the rows and pages involved in each step of an execution plan. This is the reason SQL Server generates and maintains distribution statistics. Statistics are generated on index keys and can also be generated for columns not indexed. The sysindexes table has an entry for each index and each statistics collection not associated with an index. Each table has an entry in the sysobjects table with an object id unique to the database. The id column in the sysindexes table is the object id that identifies the table. The id and indid column uniquely identifies a row in the sysindexes table. The name column in sysindexes is either the index name or the statistics collection name. Any particular statistics collection can be displayed with the following command:

DBCC SHOW_STATISTICS ( table , target )

The target is either the index name or statistics collection name. An example of the DBCC SHOW_STATISTICS output for an index-based statistics collection is shown below. The first dataset contain general information including the date of the last update, total rows, rows sampled, etc. The second dataset contains the overall average distribution for each key in succession. In this example, the lead key column is eventPlannerID, and the second and last key column is ID. The first row show the overall average distribution information for each distinct value of the first key, and the second row shows the distribution for each distinct value of the first key combined with the second key.

Figure 1. DBCC SHOW_STATISTICS output.

Statistics Transfer Process

 

The process for transferring statistics from one database to another database with the same schema is described as follows:

 

1)   Update statistics on the full-sized database (optional, but recommended).

2)  Create a new database on a server with a full-sized version of the source database.

3)  Set AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS off.

4)  Create users, data types, tables, constraints, clustered indexes (including primary keys) and all other objects except nonclustered indexes.

5)  Create tables to hold table and user name to object id mappings between the original database and the new database. Populate the table and user name mapping tables.

6)  Create and populate a table with a copy of the sysindexes tables from the original database (Optional).

7)  Execute sp_configure to allow updates to system tables.

8)  Insert statistics collections not associated with indexes into the sysindexes table of the new database.

9)  Create all nonclustered indexes.

10) Update the sysindexes entries for statistics related values on all index rows.

Update Statistics

This step is not necessary, but if we are going to all the effort to transfer statistics, we may as well transfer accurate information. The simplest method of updating statistics is executing the following statement.

exec sp_updatestats

The above statement runs UPDATE STATISTICS against all user-defined tables in the current database. The new statistics will inherit the sampling ratio from the old statistics.

 

exec sp_updatestats

 

It may be desirable to change the sampling ration to full scan, in which case, run the following statement to generate a script for updating statistics on each table with a full scan. Note that on SQL Server Enterprise Edition, a view may have indexes as well. It is also assumed that one does not want to transfer statistics for tables created by SQL Server during installation.

SELECT ‘UPDATE STATISTICS ‘ + o.name + ‘ WITH FULLSCAN’

FROM sysobjects o

WHERE ( OBJECTPROPERTY(o.id, N’IsUserTable’) = 1

OR OBJECTPROPERTY(o.id, N’IsUserView’) = 1 )

AND OBJECTPROPERTY(o.id, N’IsMSShipped’) = 0

ORDER BY o.name

 

Also run DBCC UPDATEUSAGE to correct the rows, used, reserved and dpages columns of the sysindexes table.

Create New Database and Disable Automatic Statistics

The script below is an example for creating a new database.

CREATE DATABASE [sut] ON PRIMARY (NAME = N’sut_data’,

FILENAME = N’C:MSSQLDatasut_data.mdf’ , SIZE = 32)

  LOG ON (NAME = N’sut_log’,

 

 

FILENAME = N’C:MSSQLDatasut_log.ldf’ , SIZE = 16)

  COLLATE SQL_Latin1_General_CP1_CI_AS

 

After creating the new database, disable automatic statistics management as follows.

ALTER DATABASE [sut] SET AUTO_CREATE_STATISTICS OFF

GO

ALTER DATABASE [sut] SET AUTO_UPDATE_STATISTICS OFF

GO

Create Users, Data Types, Tables, Except for Nonclustered Indexes

Create required users (that own objects in the original database), data types, tables, clustered indexes, constraints and other objects except nonclustered indexes. It may be easier to run generate scripts from Enterprise Manager twice, once with indexes but not keys, defaults and constraints, and a second time without indexes but with keys, defaults and constraints. This may make it easier to separate the nonclustered indexes from any defaults. The reason for not creating the nonclustered indexes at this stage is that indexes and statistics not associated with indexes may have interspersed indid values. If the nonclustered indexes were created now, it may be necessary to remap the indid values for statistics only rows from the original database.

Create and Populate Mapping Table

When objects like tables are created in the new database, the new object is likely to have a different object id from the original object id. Since the sysindexes uses the object id to identify tables, it is necessary to create a mapping table to hold the original and new object ids for each table. The table below also holds the userid. This may not be necessary, but this set of scripts has not been tested on databases where tables are owned by users other than dbo. It is also not necessary to have the name column, but it is included for convenience.

CREATE TABLE [tobjects] ( name sysname , oid int , ouid int , id int , uid int

Switch back to the original database, and use the following script to populate the object id mapping table.

INSERT [sut]..[tobjects] (name, oid, ouid, id, uid)

SELECT o.name, o.id, o.uid, n.id, n.uid

FROM sysobjects o

INNER JOIN [sut]..sysobjects n ON n.name = o.name

WHERE ( OBJECTPROPERTY(o.id, N’IsUserTable’) = 1

OR OBJECTPROPERTY(o.id, N’IsUserView’) = 1 )

AND OBJECTPROPERTY(o.id, N’IsMSShipped’) = 0

ORDER BY o.name

Create a Copy of the Original Sysindexes Table

This step is also optional, but it is helpful to make a permanent copy of the sysindexes table from the original database in the new database. The script below creates a table with the same columns as the sysindexes table.

CREATE TABLE tindexes (id int , status int , first binary (6) , indid smallint , root binary(6) ,  minlen smallint ,keycnt smallint , groupid smallint , dpages int , reserved int , used int , rowcnt bigint ,

 rowmodctr int , reserved3 tinyint , reserved4 tinyint , xmaxlen smallint ,

 

 maxirow smallint , OrigFillFactor tinyint , StatVersion tinyint , reserved2 int ,

 

 FirstIAM binary (6), impid smallint , lockflags smallint , pgmodctr int ,

 

 keys varbinary (1088), name sysname , statblob image , maxlen int )

 

 

   

GO

Switch back to the original database and populate the above table with a copy of the sysindexes table from the original database.

INSERT [sut]..tindexes (id,status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt, rowmodctr,reserved3,reserved4,xmaxlen,maxirow,OrigFillFactor,StatVersion,

 reserved2,FirstIAM,impid,lockflags,pgmodctr,keys,name,statblob,maxlen,rows)

 

SELECT

 i.id,status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt,

 

rowmodctr,reserved3,reserved4,xmaxlen,maxirow,OrigFillFactor,StatVersion,

 

 reserved2,FirstIAM,impid,lockflags,pgmodctr,keys,i.name,statblob,maxlen,rows

 

FROM sysindexes i

INNER JOIN [sut]..[tobjects] t ON t.oid = i.id

 

The inner join to the [tobjects] tables causes only rows with proper mapping information to be transferred. The object id is not mapped to the new databases object id at this time. There is no specific reason for this choice.

Reconfigure to Allow Updates to the System Tables

By default, direct updates to the system tables are not allowed, for obvious reasons. The following command changes this setting. (It is generally not recommended to make direct updates to system table, but this application is a special circumstance.)

Exec sp_configure ‘allow updates’, 1

RECONFIGURE WITH OVERRIDE

GO

 

Note that any stored procedure created while ‘allow updates’ is active will also have the ability to update system tables even if it is later disabled. It is recommended that the ‘allow updates’ be disabled promptly and stored procedures should not be created while this status is active.

Inserting the Statistics

Insert statistics collections not associated with indexes into the sysindexes table of the new database. The status column with a bit mask of 64 corresponds to statistics not associated with indexes. This step should be performed before nonclustered indexes are created. With only clustered indexes created in the new database, the only values of indid in the sysindexes table are 0, representing heap organized tables, and 1 representing the clustered indexes. Both statistics not associated with indexes and nonclustered indexes have indid values between 2 and 254. The indid value 255 represents text or image data. This allows a simple copy of the entire row of sysindexes from the original database to be inserted into the new database sysindexes table with the new object id and the original indid value. Note the last column of sysindexes, rows, is not set because it is a computed column.

INSERT sysindexes(id,status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt, rowmodctr,reserved3,reserved4,xmaxlen,maxirow,OrigFillFactor,StatVersion, reserved2,FirstIAM,impid,lockflags,pgmodctr,keys,name,statblob)

SELECT o.id, status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt,

rowmodctr,reserved3, reserved4,xmaxlen,maxirow,OrigFillFactor,StatVersion,

 

reserved2,FirstIAM,impid,lockflags,pgmodctr,keys,s.name,statblob

 

FROM [tindexes] s

INNER JOIN [tobjects] o

ON o.oid = s.id

WHERE  (s.status & 64) = 64

GO

 

 

Create Nonclustered Indexes and Update Sysindexes

Once the statistics not associated with indexes have been inserted into the sysindexes table, the nonclustered indexes can be created. It does not matter if the indid value for the nonclustered indexes matches to the original indid value. The statement below updates the new sysindexes table with required statistics data from the copy of the original sysindexes table.

UPDATE i SET dpages = s.dpages, reserved = s.reserved, used = s.used, rowcnt = s.rowcnt, rowmodctr = s.rowmodctr, statblob = s.statblob

FROM sysindexes i

INNER JOIN [tobjects] t ON t.id = i.id

INNER JOIN [tindexes] s ON s.id = t.oid AND s.name = i.name

WHERE (s.status & 64) = 0

GO

 

Run sp_configure to disallow updates to system tables at this time. The new database can now be backed up or detached for transfer to other systems. Any necessary data for the new small sized database can be transferred at any time during this process.

Summary

A description of the steps to transfer statistics from one database to another has been presented. This is probably most useful when it is necessary to display the execution plan that would be used on the large production database from a smaller sized database. Developers can work on a database generated from the scripts and still observe the production execution plan, with the exception of parallel execution plans if the development system is single processor.

Other potential uses include allowing a person to troubleshoot performance issues due to problem execution plans that can only be seen from the production database statistics data. The scripts for the required tables and indexes can be transferred to a new database along with statistics. This small amount of data can be sent by email for remote troubleshooting.

 

2004 Joe Chang. All rights reserved. Published with the express written permission of the author.