FIX: Enterprise Manager Slow in Opening the Database Properties Dialog Box (251077)

SYMPTOMS

If a database has a large number of users with various permissions set, the SQL Server Enterprise Manager experiences slow performance if you attempt to view the properties for the database. This slow performance makes it appear as if Enterprise Manager has stopped responding.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

While the Properties dialog box is opening the CPU Usage value shown in Windows NT Task Manager is at 100 percent and the SQL Server Enterprise Manager Task in Task Manager has a Status of Not Responding. However, it has not stopped responding and is actually still running; if you wait for a long time (about 15 minutes on a Pentium II 400 MHz computer with 128 MB of RAM and 800 users in that particular database) the dialog box eventually opens and the CPU Usage value returns to a normal level.

The fix provided by SP2 means that opening the Properties page for the database no longer causes slow performance. However, if you click the Permissions tab of the Properties page once it is open, you still see the slowdown due to the large number of users and permissions set.

Looking at SQL Server Profiler, the cause for the slow performance appears to be the following query, which is executed for each database user:

Select null, null, user_name(p.uid), null, 0, p.action, p.protecttype 
From sysprotects p 
Where p.action not in (193, 195, 196, 197, 224, 26) and p.uid = 1

This query, when run from Query Analyzer, also causes slow performance.