Querying INFORMATION_SCHEMA is sporadically slow

Question

Hi, it seems that pulling metadata out of SQL 2005 runs quite a bit slower than in 2000. The main use case I am running into is selecting a set of columns from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'MyTable'. Sometimes when I run this, it takes 100 milliseconds, but then if I run it again right afterwards sometimes it takes 20-30 seconds! Is there some newfangled engine for managing metadata in SQL 2005 that causes these random bursts of lag?

Answer 1

I'm experiencing the same sporadic lags in SQL 2005, typically 10-30 seconds for the following query, during which the CPU meter momentarily peaks, then drops to zero while something inside SQL goes out to lunch.   Information_Schema was no speed demon in SQL 2000, but at least I could always get a response in 1-2 seconds.

SELECT *

FROM INFORMATION_SCHEMA.table_constraints As tc

JOIN information_schema.key_column_usage kcu1 ON kcu1.constraint_catalog = tc.constraint_catalog

AND kcu1.constraint_schema = tc.constraint_schema

AND kcu1.constraint_name = tc.constraint_name

WHERE tc.Constraint_Type = 'PRIMARY KEY'

Answer 2

Hi Greg,

Thanks for reporting this.

Can you post query plan and statistic profile output in order for us to better help you?

Best Regards

Andrew

[MSFT]