March 20, 2009 at 6:18 pm
Hello,
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') endfrom 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.
Sam
March 24, 2009 at 12:22 pm
Any one know if there is a problem with function ObjectPropertyEX, could it be recompiling it each time?
Is there a way to add an index on column object_id and schema_id, want to see if that would improve performance.
April 6, 2009 at 4:20 pm
Hi there,
I am seeing similar slowness post SP3 on WIN 2003 Server.
Opening tables is a problem but also so is running sp_help on any table.
Did you find a way to fix this issue?
April 9, 2009 at 10:18 am
ABHILASH,
Can you please post here how many tables, views, synonyms you have in this database you are having problem with.
Can you run SQL Profiler then open tables, narrow down to statements that are taking up lot of CPU, and post the query here, it may be the same query that I posted.
I was thinking of rebuilding views, synonyms that use the linked server on my test machine. It works fine on my test machine, but those views and synonyms were built using SP2, on my production machine they were built using SP3. Perhaps there is a problem with SP3.
Sam
April 9, 2009 at 11:51 am
It may be a long shot but do you have odbc tracing enabled on your machine. I've seen this impact EM (SQL 2000).
April 9, 2009 at 5:07 pm
If you mean ODBC trace setting that is located on ODBC Data source Administrator tracing tab, it is not on.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply