Information_Schema query is sporadically slow
InformationSchema queries often take 10-30 seconds, especially when joining several InformationSchema tables. During a typical query, the CPU meter momentarily peaks, then drops to zero while something inside SQL goes out to lunch. Other times the response is very fast.
Information_Schema was no speed demon in SQL 2000, but at least I could always get a response in 1-2 seconds.
Upvotes: 0<=-=Mar 13 2006 11:48AM=-=>
Thanks for reporting this. Can you gather statistics profile & query plan and send our way?
Atached is a sql profiler trace file, capturing two queries, one of which took 17 seconds.
The CPU was idle before the test, with 90 MB physical memory free.<=-=Mar 30 2006 12:12PM=-=>
Thanks for reporting this.
We looked at the two statistic profiles you supplied.
The times in the profile trace are interesting �
The fast (first) statement is recorded at 1 second for the statement and 2 seconds for the batch.
The slow (second) statement is recorded at 6 seconds but its corresponding batch at 9 seconds.
The statistics profiles look identical � the plan and number of rows returned are the same in each case. It does not look like a stats profile that would take 17seconds.
The CPU recorded for the statements is under 100ms in both cases.
The number of reads is about the same in each case (~10% higher in the slow case).
We think it has nothing to do with the query, but something else is causing the delay. Maybe hardware or other application in the box.