Microsoft KB Archive/914288

= How to generate a script of the necessary database metadata to create a statistics-only database in SQL Server 2005 =

Article ID: 914288

Article Last Modified on 11/20/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Enterprise X64 Edition
 * Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Standard X64 Edition
 * Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems

-



INTRODUCTION
The Microsoft SQL Server 2005 query optimizer uses the following types of information to determine an optimal query plan:
 * The database metadata
 * The hardware environment
 * The database session state

Typically, you must simulate all these same types of information if you want to reproduce the behavior of the query optimizer on a test system.

Microsoft Customer Support Services may ask you to generate a script of the database metadata. Microsoft Customer Support Services uses this script of the database metadata to investigate an optimizer issue. This article describes the steps to generate the statistics script. The article also describes how the query optimizer uses the information.



MORE INFORMATION
Before you follow the steps to generate the script, make sure that SQL Server Management Studio is the SQL Server 2005 Service Pack 2 version or a later version. If you use earlier versions of the SQL Server Management Studio, the Script Wizard does not contain all the necessary options for the steps in this article to work correctly.

Script the whole database
When you generate a statistics-only clone database, it may be easier and more reliable to script the whole database instead of scripting individual objects. When you script the whole database, you receive the following benefits:
 * You avoid issues with missing dependent objects that are required to reproduce the issue.
 * You require significantly fewer steps to select the necessary objects.

Note If you generate a script for a database, and the metadata for the database contains thousands of objects, the scripting process consumes significant CPU resources. We recommend that you generate the script during off-peak hours. Or, you can use the second option to generate the script for individual objects.

To script each database that is referenced by your query, follow these steps:  Open SQL Server Management Studio. In the Object Explorer, expand Databases, and then locate the database that you want to script. Right-click the database, point to Tasks, and then click Generate Scripts. In the Script Wizard, verify that the correct database is selected. Click to select the Script all objects in the selected database check box, and then click Next. In the Choose Script Options dialog box, change the following settings from the default value to the value that is listed in the following table.

Note The Script Logins option and the Script Object Level Permissions option may not be required unless the schema contains objects that are owned by logins other than dbo. Click Next. Click the Script to File option, and then enter a file name.</li> Click Finish.</li></ol>

Script individual objects
You may only script individual objects that are referenced by a particular query instead of scripting the complete database. However, unless all database objects were created by using the WITH SCHEMABINDING clause, the dependency information in the sys.depends system table may not always be accurate. This inaccuracy may cause one of the following issues:
 * The scripting process does not script a dependent object.
 * The scripting process may script objects in the incorrect order. To run the script successfully, you must manually edit the generated script.

Therefore, we do not recommend that you script individual objects, unless the database has lots of objects and scripting would otherwise take too long. If you must use script individual objects, follow these steps: <ol> In SQL Server Management Studio, expand Databases, and then locate the database that you want to script.</li> Right-click the database, point to Script Database As, point to CREATE to, and then click File.</li> Enter a file name, and then click Save.

The core database container will be scripted. This container includes files, file groups, the database, and properties.</li> Right-click the database, point to Tasks, and then click Generate Scripts.</li> Make sure that the correct database is selected, and then click Next.</li> In the Choose Script Options dialog box, change the following settings from the default value to the value that is listed in the following table.

Note The Script Logins option and the Script Object Level Permissions option may not be required unless the schema contains objects that are owned by logins other than dbo.</li> In the Choose Object Types dialog box, select all database object types that the problematic query references.

For example, if the query only references tables, select Tables. If the query references a view, select Views and Tables. If the problematic query uses a user-defined function, select Functions.</li> When you have selected all the object types that are referenced by the query, click Next.</li> A dialog box appears for each database object type that you selected in step 7. In each dialog box, select the specific tables, views, functions, or other database objects, and then click Next.</li> Click the Script to File option, and then specify the same file name that you entered in step 3.</li> Click Finish to start the scripting.</li></ol>

When the scripting has finished, send the script file to the Microsoft support engineer. The Microsoft Support Engineer may also request the following information:
 * The hardware configuration, including the number of processors and how much physical memory exists
 * The SET options that were active when you ran the query

Note You may have already provided this information by sending a SQLDiag report or a SQL Profiler trace. You may have also used another method to provide this information.

How the information is used
The following tables help explain how the query optimizer uses this information to select a query plan.

Environment
Additional query words: clone db

Keywords: kbhowto kbinfo kbexpertiseadvanced kbexpertiseinter kbsql2005engine KB914288

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.