Назад | Перейти на главную страницу

Медленный запрос INFORMATION_SCHEMA

У нас есть приложение .NET для Windows, которое выполняет следующий запрос при входе в систему, чтобы получить некоторую информацию о базе данных:

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

Обычно это выполняется за пару секунд, но на одном сервере с SQL Server 2000 это занимает более четырех минут. Я запустил его с включенным планом выполнения, и результаты огромны, но эта часть привлекла мое внимание (она не позволяет мне опубликовать изображение):

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

Затем я обновил статистику по всем таблицам, которые были упомянуты в плане выполнения:

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

Но это не помогло. Мастер настройки индекса тоже не помогает, потому что не позволяет мне выбирать системные таблицы. На этом сервере больше ничего не работает, поэтому ничто другое не могло его замедлить. Что еще я могу сделать для диагностики или устранения проблемы на этом сервере?

Левое соединение печально известно тем, что генерирует массивные наборы записей, если позволяют обстоятельства. Я иногда сталкиваюсь с подобными проблемами, и у меня нет быстрого и простого ответа. Я обнаружил, что поигрался с запросом, например путем создания запроса по одному соединению за раз - лучший способ выяснить, какое соединение вызывает проблему.

Вы пытались выполнить подсчет выборки (*) из таблиц в запросе, чтобы увидеть, есть ли в одной из них неожиданно большое количество строк?

JR

Еще мысли: пока неясно, связана ли проблема с сервером или запрос является патологическим и убьет любой сервер. Я полагаю, вы могли бы скопировать базу данных на другой сервер и повторить запрос.

Как насчет изменения запроса, чтобы он выглядел примерно так:

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'

Я думаю, что внутренние соединения должны быть безопасными, так как таблицы tc и ccu должны иметь совпадающие записи. Уменьшает ли выполнение запроса таким образом время выполнения?

Подумать только, механизм оптимизатора в SQL 2000 испытывал трудности при попытке найти наиболее оптимизированный план запроса для операторов с более чем 4 внутренними соединениями, и я думаю, что с левыми соединениями это еще сложнее.

Как вы можете видеть в предоставленном вами плане quereplan, он выполнял запросы "многие ко многим".

Лучшая практика для SQL 2000 - разбить код, если вам нужны данные из более чем 4 таблиц.

/ Хокан Винтер

Вы проверяли, не блокируют ли вас?

Поскольку вы дошли до глубины получения плана выполнения, я предполагаю, что да, но всегда стоит спрашивать. В зависимости от того, на каком уровне изоляции вы работаете и какие другие действия выполняются в системе, вы можете ожидать блокировки.

Могут ли таблицы быть сильно фрагментированными?

Вы можете проверить системные таблицы на фрагментацию в SQL 2000 (не в SQL 2005 или новее). См. Кален Делейни запись в блоге о фрагментации системной таблицы для получения дополнительной информации. (Однако, если таблицы не очень большие, высокий уровень фрагментации может вводить в заблуждение. Обычно я использую страницы размером 1 КБ в качестве практического правила.)

Есть ли у экземпляра SQL более серьезная проблема с производительностью?

Я действительно не уверен, что проблема здесь в плане - из ссылки похоже, что у вас есть план с некоторыми мускулистыми соединениями, но если на самом деле возвращается очень мало строк, я не думаю, что эти объединения снизят производительность если у вас нет других проблем. Я видел планы, которые сильно завышали количество строк и выполняли их просто отлично; обычно меня убивает заниженное количество строк. (Если другие знают о случаях, когда это может быть неверно, я хотел бы услышать о них!)

На этом этапе я лично бы быстро проверил среду sql на коробке, чтобы убедиться, что все выглядит так, как я ожидал. Я часто обнаруживал, что эти вопросы «почему этот запрос медленный?» Приводят к обнаружению более серьезной проблемы с производительностью.

  1. параметры sp_configure, такие как maxdop, минимум / максимум памяти, настройки блокировки
  2. проверьте журнал ошибок SQL на наличие предупреждений о задержке диска, убедитесь, что при запуске он мог использовать большие страницы, если я ожидаю, что он будет использовать большие страницы и т. д.
  3. Регулярно запланированные задания sql завершаются в нормальный временной интервал?
  4. проверьте все собранные вами счетчики perfmon, чтобы убедиться, что они находятся в пределах нормы.

Это может быть связано с вашими проблемами.

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