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 3 / 4

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.