Tuesday, January 03, 2006 10:13 PMHi, 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?
Thursday, March 09, 2006 7:48 PM
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.
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'
Monday, March 13, 2006 7:40 PM
Thanks for reporting this.
Can you post query plan and statistic profile output in order for us to better help you?