FIX: sp_MSdependencies May Run for a Long Time to Script Tables and Views (308888)

SYMPTOMS

With a large database that has 6,000 tables and 15,000 views, the sysdepends system table may have close to 500,000 rows. An attempt to script 10 tables by using only DROP and CREATE table statements took several minutes on an 8-processor computer. The most time was spent on sp_MSdependencies stored procedure, which does not seem necessary because the dependency objects were not requested. According to this speed, to script all the tables and views may take over 10 days, which is a serious performance concern.

CAUSE

The scripting process uses the same SQL Distributed Management Objects (DMO) scripting process that Data Transformation Services (DTS) uses. By design, the scripting process must issue a warning message for each dependency object not transferred or scripted, which requires a search for all the dependency objects regardless of whether they were requested. A large database may have multiple dependency levels. More levels escalate the cost dramatically for searching and generating that warning message.

RESOLUTION

For additional information about the latest service pack for Microsoft SQL Server 2000, click the article number below to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

This problem is fixed in SQL Server 2000 Service Pack 2 (SP2) by the addition of a new registry key that you can use to turn off the warning message.

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

To use the new registry key, use these steps:

Start the Registry Editor (Regedt32.exe).
Locate, and then click to select the following key in the registry:
HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL Server/80/Tools/DMO

On the Edit menu, click Add Value, and then add the following registry value:

   Value name: IgnoreTransferScriptWarnings
   Data type:  REG_DWORD
   Radix:      Decimal
   Value data: 1

Quit the Registry Editor.

The meaning of the value in Value data is shown in the table that follows.

Value Data Value	Meaning
1	Ignore the warning message.
0	Show the warning message.