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.