Article ID: 251077
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q251077
BUG #: 56124 (SQLBUG_70)
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.
Additional query words: ent man entman SEM SSEM SS EM hang hangs hung freeze freezes frozen slow slows down
Keywords: kbbug kbfix KB251077
