articles >> performance tuning >> Transferring SQL Server Statistics From One Database ...

Transferring SQL Server Statistics From One Database to Another

By : Joe Chang Feb 16, 2005

Page 2 / 4

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:\MSSQL\Data\sut_data.mdf' , SIZE = 32)

  LOG ON (NAME = N'sut_log',

FILENAME = N'C:\MSSQL\Data\sut_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