SQL Server Performance

Mangement Studio opens tables very slow even empty tables in sql server 2005

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by samiam914, Mar 23, 2009.

  1. samiam914 New Member

    Env = SQLserver 2005 SP3. Windows server 2003.

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

    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 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)

    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.

    Any help is appreciated.

  2. jhuettner New Member

    Does query performance seem ok? Do the tables open faster from the same database when you use management studio on another computer? I had a problem with management studio displaying rows very slowly on my laptop but not on other computers. Turned out to be some bug between management studio and my nVidia display drivers. Going into Display Properties - Settings - Advanced - Troubleshoot, and turning down Hardware acceleration a bit fixed the problem. Not sure if your issue is related.
  3. samiam914 New Member

    I used another computer to open the same table from the same database, same problem. Yes, 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. I am aware of the problem you indicated above, it is not the same problem. Thank you for replying.
  4. satya Moderator

    This is a network latency problem, I believe as your program loads from local machine and tries to authenticate with the server, then to execute the query.
    Why do you want to do that way, rather you could keep SSMS open and then double clikc on that file from windows to open up?
  5. samiam914 New Member

    Thank you for the reply. I wish it was, but it is not. I logged on to the server, and did the same, same problem. As I indicated in my initial post I can open tables from another database that exists within the same instance with no problems. What seems to be the case is that when you have way too many sysnonyms in the database it is causing the problem, and I posted the query the Management Studio is using internally (I captured it via sqlprofiler). You can see in the query it is using function ObjectPropertyEx many times, I wonder if it is slowing down, it has to use the funtion for 5000 syonym rows. However the same happens on my test server not a problem with same number of synonyms. Could it be that it is compiling ObjectPropertyEx function each time? The .NET frame work on production server is latest, the one on test server is an earlier version.
    The other possible solution: Is it possible to add indexes on system views some how? I was thinking of adding an index on object_id column and then see how that works.

Share This Page