странный план выполнения запроса SQL Server

Контекст: SQL Server 2008. Есть 2 таблицы для внутреннего объединения. Таблица фактов, содержащая 40 миллионов строк, содержит ключ пациента, введенные лекарства и другие факты. Существует уникальный индекс (некластеризованный) для ключа лекарства и ключа пациента, объединенных в указанном порядке. Таблица размеров - это список лекарств (70 строк). Присоединение предназначено для получения кода лекарства (бизнес-кода) на основе ключа лекарства (суррогатного ключа). Запрос:

SELECT a.PKey, a.SomeFact, b.MCode
FROM tblFact a
JOIN tblDIM b ON a.MKey = b.MKey

Все возвращаемые столбцы являются целыми числами. Вышеупомянутый запрос выполняется за 7 минут, и его план выполнения показывает, что используется индекс (MKey, PKey). Индекс был перестроен прямо перед запуском. Когда я отключил индекс в таблице фактов (или скопировал данные в новую таблицу с той же структурой, но без индекса), тот же запрос занимает всего 1:40 минуты.

Статистика ввода-вывода также ошеломляет.

С индексом: Таблица tblFACT. Количество сканирований 70, логических чтений 190296338, физических чтений 685138, упреждающих чтений 98713

Без индекса: таблица tblFACT_copy. Счетчик сканирований 17, логических чтений 468891, физических чтений 0, упреждающих чтений 419768

Вопрос: почему он пытается использовать индекс и идти по неэффективному пути?


sql-server indexing sql-execution-plan
person vuht2000    schedule 23.08.2011    source источник
comment
Можем ли мы увидеть определения индексов и план выполнения? Должен ли он выполнять RID_LOOKUP с вашим индексом?   -  person Derek Kromm    schedule 23.08.2011

Ответы (3)


arrow_upward
0
arrow_downward

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

Могут быть директивы, побуждающие базу данных не использовать индексы или использовать другие индексы, но я плохо знаю SQL-сервер.

person GolezTrol    schedule 23.08.2011
comment
К вашему сведению, FROM YourTable WITH (INDEX(0)) заставляет сканировать таблицу - person Andomar; 23.08.2011
comment
А в SQL Server 2008 R2 SP1 и Denali вы можете использовать FORCESCAN для принудительного сканирования любого индекса - хотя сомнительно, что будет слишком много неакадемических случаев, когда это будет полезно. Сказать это слово вслух в переполненном зале для презентаций также может быть забавным, в зависимости от вашего акцента. :-) См. sqlblog.com/blogs/aaron_bertrand/archive/2011/04/22/ и msdn.microsoft.com/en-us/library/ms187373%28SQL.110%29.aspx - person Aaron Bertrand; 24.08.2011

arrow_upward
1
arrow_downward

Вам нужно добавить SomeFact как INCLUDE в индекс tblFact, чтобы он стал охват.

В настоящее время доступ к таблице будет осуществляться дважды: один раз для индекса, а затем еще раз для поиска, чтобы получить SomeFact либо как RID, либо как поиск по ключу (зависит от наличия кластеризованного индекса)

Это не относится к tblDIM, потому что я предполагаю, что MKey является кластеризованным индексом, что делает его неявным

person gbn    schedule 23.08.2011
comment
+1 - я уверен, что ему все еще нужно вытащить все страницы для кластерного индекса - person JNK; 23.08.2011
comment
При текущем плане выполнения к индексу обращаются 70 раз (равно количеству строк в нечеткой таблице). Есть поиск по ключу (кластерный индекс). Я не решаюсь полностью покрыть индекс, поскольку столбцы фактов различаются в зависимости от запросов. - person vuht2000; 23.08.2011
comment
@ vuht2000: Три варианта: 1. Без индекса, каждый запрос будет занимать 1:40 2. Сделать (Mkey, Pkey) кластеризованным индексом 3. Сделать текущий индекс покрывающим. Твой выбор... - person gbn; 23.08.2011
comment
@gbn: спасибо. Я переместил Mkey во второй столбец в индексе, и он работает должным образом. Однако я все еще не могу не задаться вопросом, почему SQL Server делает такой плохой выбор с помощью довольно простого запроса. Согласовано, если в запросе должен быть фильтр, скажем, по конкретному пациенту, тогда использование индекса имеет смысл. Но SQL Server точно знает ожидаемое количество строк с фильтром или без него. - person vuht2000; 23.08.2011
comment
@ vuht2000: с MKey 2nd я подозреваю, что он не использует индекс, потому что он бесполезен: это то же самое, что без индекса (сравнить планы), но просто занимает место на диске - person gbn; 23.08.2011
comment
Я понимаю. Но индекс по-прежнему полезен для запросов, которые фильтруют как PKey, так и MKey, я полагаю? Просто способ отключить индекс для этого типа запроса. У вас есть понимание, почему индекс использовался ненадлежащим образом? Это стабильное поведение SQL Server или просто редкость? - person vuht2000; 24.08.2011
comment
Индекс использовался ненадлежащим образом, вероятно, из-за плохой статистики: выполняли ли вы полное сканирование, чтобы SQL Server знал точное распределение строк? В противном случае (вы позволяете ему выполнять выборку по умолчанию), оценки SQL Server не будут правильными, поэтому он не знает, как игнорировать индекс. Ответ GolezTrol правильный, но они не знают почему - person gbn; 24.08.2011

arrow_upward
0
arrow_downward

Ваша статистика актуальна? Проверить с:

SELECT object_name = Object_Name(ind.object_id)
,      IndexName = ind.name
,      StatisticsDate = STATS_DATE(ind.object_id, ind.index_id)
FROM   SYS.INDEXES ind
order by
       STATS_DATE(ind.object_id, ind.index_id) desc

Обновить с помощью:

exec sp_updatestats;
person Andomar    schedule 23.08.2011
comment
OP сказал, что индекс был перестроен прямо перед запуском: статистика индекса всегда обновляется путем перестроения индекса (но не индексов столбцов). По сути, индекс tblFact не покрывает - person gbn; 23.08.2011