Mangement Studio opens tables very slow even empty tables when user has 5000 synonyms - by samiam914

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


0
0
Sign in
to vote
ID 431674 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 4/9/2009 12:14:50 PM
Access Restriction Public

Description

Hello,

MS studio takes 2 minutes to open small tables, even empty tables. 

Env = SQLserver 2005 SP3. Windows server 2003 SP2.

Microsoft SQL Server Management Studio 9.00.4035.00
Microsoft Analysis Services Client Tools 	2005.090.4035.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 
Microsoft Internet Explorer 7.0.5730.13
Microsoft .NET Framework  2.0.50727.1433
Operating System 5.2.3790

select * from sys.synonyms shows 5100 rows
select * from sys.schemas shows 22 rows

After clicking on a table to open in Management Studio, immediately I did a sqlprofiler. I found the following query is where it is taking enormous time. sql profiler shows CPU=4625; Reads=28116; Duration=29996

SELECT sch.name, sn.name, sn.base_object_name, CASE WHEN ObjectPropertyEx(sn.object_id, 'BaseType') = 'U' THEN 2 WHEN ObjectPropertyEx(sn.object_id, 'BaseType') = 'V' THEN 3 WHEN ObjectPropertyEx(sn.object_id, 'BaseType') IN ('FT', 'TF') THEN 4 WHEN ObjectPropertyEx(sn.object_id, 'BaseType') = 'IF' THEN 5 WHEN ObjectPropertyEx(sn.object_id, 'BaseType') = 'AF' THEN 6 ELSE 0 END FROM sys.synonyms AS sn INNER JOIN sys.schemas AS sch ON sch.schema_id = sn.schema_id WHERE (select case when object_id(base_object_name) IS NULL Then 'P' else ObjectPropertyEx(object_id, 'BaseType') end from sys.synonyms where object_id = sn.object_id) NOT IN ('FN', 'FS', 'P')

I did update statistics for the database using "maintenance plan", did not help.

I logged in as one of the test users on my PC, who does not have access to any synonyms, no problem opens tables instantaneously. So, I am 100% sure number of synonyms is causing the problem.

(I have a copy of the same database on a test server, it opens quickly. 
.Net framework is behind an sp relase on this test server. Differences between the test server and production server are noted below. 
Microsoft MSXML 2.6 3.0 5.0 6.0 
Microsoft .NET Framework  2.0.50727.832
)

Also, MS opens tables in a different database instantly, I only have 4 synonyms in this database and number of schemas are about the same number. 

If I run a query it is instantaneous, only when I open a table with Management Studio it is very slow. If I open a table from another database where the number of synonyms are only a couple of dozen it is very fast. I should note that over 5000 sysnonyms in the problem database are referencing linked server tables (oracle tables). However, even if I open a sql server table in this database it is very slow.

Please note I am aware of network latency or Managament Studio refresh rate problem some experienced, it is not the same problem.

Sam 
Sign in to post a comment.
Posted by Microsoft on 8/15/2011 at 10:17 PM
Hi,

This is regarding the connect item 431674. We carefully evaluated the performance issue in working with large number of synonyms and would like to thank you for sharing your feedback with us. Unfortunately given our current schedules and other tasks at hand, it looks unlikely that we will be able to attend to this in the near future. We are closing the issue and will revisit the same in a later release of SQL Server. Thank you again for the feedback.

regards

Seshagiri
PM, SSMS