0

We have a .NET Windows application that runs the following query on login to get some information about the database:

SELECT t.TABLE_NAME, ISNULL(pk_ccu.COLUMN_NAME,'') PK, ISNULL(fk_ccu.COLUMN_NAME,'') FK 
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk_tc 
ON pk_tc.TABLE_NAME = t.TABLE_NAME 
AND pk_tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk_ccu 
ON pk_ccu.CONSTRAINT_NAME = pk_tc.CONSTRAINT_NAME 
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk_tc 
ON fk_tc.TABLE_NAME = t.TABLE_NAME 
AND fk_tc.CONSTRAINT_TYPE = 'FOREIGN KEY' 
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fk_ccu 
ON fk_ccu.CONSTRAINT_NAME = fk_tc.CONSTRAINT_NAME 

Usually this runs in a couple seconds, but on one server running SQL Server 2000, it is taking over four minutes to run. I ran it with the execution plan enabled, and the results are huge, but this part caught my eye (it won't let me post an image):

http://img35.imageshack.us/i/plank.png/

I then updated the statistics on all of the tables that were mentioned in the execution plan:

update statistics sysobjects
update statistics syscolumns
update statistics systypes
update statistics master..spt_values
update statistics sysreferences

But that didn't help. The index tuning wizard doesn't help either, because it doesn't let me select system tables. There is nothing else running on this server, so nothing else could be slowing it down. What else can I do to diagnose or fix the problem on that server?

4 Answers 4

1

Left joins are notorious for generating massive recordsets if circumstances permit. I've occasionally run into this sort of problem and I don't have a quick and easy answer. I've found that playing around with the query, e.g. by building up the query one join at a time, is the best way to work out which join is casuing the problem.

Have you tried doing a select count(*) from the tables in the query to see if one of them has an unexpectedly high number off rows?

JR

More thoughts: it isn't clear so far whether the problem lies with the server or if the query is pathological and would kill any server. I suppose you could copy the database to a different server and try the query again.

How about modifying the query to be something like:

SELECT t.TABLE_NAME, ISNULL(pk_ccu.COLUMN_NAME,'') PK, ISNULL(fk_ccu.COLUMN_NAME,'') FK 
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN 
( INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk_tc INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk_ccu
  ON pk_ccu.CONSTRAINT_NAME = pk_tc.CONSTRAINT_NAME 
)
 ON pk_tc.TABLE_NAME = t.TABLE_NAME AND pk_tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
LEFT JOIN
( INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk_tc INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fk_ccu
  ON fk_ccu.CONSTRAINT_NAME = fk_tc.CONSTRAINT_NAME
)
 ON fk_tc.TABLE_NAME = t.TABLE_NAME AND fk_tc.CONSTRAINT_TYPE = 'FOREIGN KEY' 

I think the inner joins ought to be safe since the tc and ccu tables ought to have matching records. Does doing the query this way improve the execution time?

4
  • It doesn't slow down until I add the last join. The query returns 5004 records. select count() from INFORMATION_SCHEMA.TABLES -- 3716 select count() from INFORMATION_SCHEMA.TABLE_CONSTRAINTS -- 2224 select count(*) from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE -- 2221
    – user10444
    Jun 23, 2009 at 16:06
  • See the edit to my post. Jun 23, 2009 at 17:02
  • Yes, that gets it back to running in a couple of seconds, thanks. I'd still like to try to find out why, out of the hundreds of servers running this application, only this one decided to be slow.
    – user10444
    Jun 24, 2009 at 13:21
  • You'd have to looks closely at the data in the tables to see why, but bear in mind that the total record count in the joined tables could be 376*2224*2221*2224*2221, which is, erm, a large number. Normally the joined columns are indexed and the join is very efficient, however with left joins I have seen SQL give up on indexing and resort to table scans. This makes things very slow. Exactly how SQL decides this I don't know. Jun 24, 2009 at 15:07
0

Have you checked to see if you are being blocked?

Since you've gone to the depth of getting the execution plan, I am guessing you have, but it's always worth asking. Depending on what isolation level you're running in and what other activity is running on the system, you could be waiting for locks.

Could the tables be highly fragmented?

You can check system tables for fragmentation on SQL 2000 (not SQL 2005 or later). See Kalen Delaney's blog entry on system table fragmentation for more information there. (If the tables are not very large, however, high fragmentation levels might be misleading. I usually use 1K pages as a rule of thumb.)

Does the SQL Instance have a Larger Perf Issue?

I am really not sure if the issue is the plan here-- from the link it looks like you have a plan with some beefy joins, but if actually very few rows are being returned then I don't think those joins would make performance terrible unless you have other issues. I have seen plans which have overestimated rowcounts by a lot and performed just fine, it's the underestimated row counts which usually kill me. (If others know of instances where this might be incorrect, I would love to hear about them!)

At this point I personally would do a quick check of the sql environment on the box to make sure things look as I expect. I have often found these issues of "why is this query slow" lead to finding that there is a larger perf issue.

  1. sp_configure options such as maxdop, memory min/max, lock settings
  2. check the SQL Error Log for any warnings as to disk latency, make sure that when it started up it was able to use large pages if I am expecting it to use large pages, etc
  3. are regularly scheduled sql jobs completing within a normal timerange?
  4. check any perfmon counters you collect to see if they are within normal range.
0
0

Just a thought, the optimizer engine in SQL 2000 used to have difficulties in trying to find the most optimized queryplan for statements with more than 4 inner joins, and i think it is even harder with left joins.

As you can see in the quereplan you provided, it performed a many to many query.

The best practice for SQL 2000 is to break up the code if you need data from more then 4 tables.

/Håkan Winther

1
  • Left joins seem to be particularly problematic. Jun 25, 2009 at 7:55
0

This may be related to the problems you're having

http://bugs.mysql.com/bug.php?id=19588

You must log in to answer this question.