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

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.