SELECT 'update statistics [' + ss.table_schema + '].' + '[' + object_name(si.id) + ']' + ' (' + si.name + ')' + + ' with FULLSCAN' FROM sysindexes AS si JOIN sysobjects AS so ON si.id = so.id INNER JOIN INFORMATION_SCHEMA.tables AS ss ON so.name = ss.table_name WHERE si.id > 50000 AND si.name IS NOT NULL AND so.xtype = 'U' Copy and paste the generated script from the query results pane into the Transact-SQL editor, and then run it in context of the database you plan to upgrade. In my experience, defragmenting indexes is necessary because SQL Server 2005 and SQL Server 2008 can have greatly reduced performance on fragmented indexes, even if the code does a plain primary key search. (I observed a situation in which a command like SELECT ran for 2 to 3 Fld21, Fld2, Fld3 FROM SomeTable WHERE PrimaryKey = 123 seconds before defragmentation and 10 milliseconds after.) To defragment indexes, you can use a script provided in SQL Server Books Online. For more information, see section D, sys.dm_db_index_physical_stats in a script to rebuild or “Using reorganize indexes” in the Examples section of
SELECT 'update statistics [' + ss.table_schema + '].' + '[' + object_name(si.id) + ']' + ' (' + si.name + ')' + + ' with FULLSCAN' FROM sysindexes AS si JOIN sysobjects AS so ON si.id = so.id INNER JOIN INFORMATION_SCHEMA.tables AS ss ON so.name = ss.table_name WHERE si.id > 50000 AND si.name IS NOT NULL AND so.xtype = 'U' Copy and paste the generated script from the query results pane into the Transact-SQL editor, and then run it in context of the database you plan to upgrade. In my experience, defragmenting indexes is necessary because SQL Server 2005 and SQL Server 2008 can have greatly reduced performance on fragmented indexes, even if the code does a plain primary key search. (I observed a situation in which a command like SELECT Fld21, Fld2, Fld3 FROM SomeTable WHERE PrimaryKey = 123 ran for 2 to 3 seconds before defragmentation and 10 milliseconds after.) To defragment indexes, you can use a script provided in SQL Server Books Online. For more information, see section D, “Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes” in the Examples section of
Database_Upgrade_to_SQL_Server_2008_Tools_and_Approaches.docx