Настройка индексов

[vesna dba]

Всем привет!

Мониторю длительные запросы SQL на пользователях.
В планах запросов наблюдаю TABLE SCAN например по таблице CUSTINVOICETRANS.
Смотрю через репозитарий свойства таблицы
[attachment=603:CustInvoiceTrans.JPG]
Наблюдаю отсутсвие PrimaryIndex, ClusterIndex
(и во многоих таблицах, по которым встречаю в планах запросов сканирование таблиц такое положение)

В тестовой базе например делаю имеющийся индекс по полю InvoiceId как кластерный,
повторно запускаю отчет, мониторю, смотрю план запроса - сканирования таблицы уже нет.
Соответственно нет уже огромной очереди на диске, где отдельно выложил данную таблицу (даже отдельно она лежит от своих индексов).
Все это хорошо, НО не хотелось бы огрести проблем в другом месте, так как все сделано чисто интуитивно.

Прошу меня наставить на путь истинный, дать ссылки на доки (теорию по индексам знаю , практики мало) и т.п., какую инфу требовать от программистов.
Наверняка кто то уже занимался этой проблемой.
Потери производительности на этой неоптимальности просто огромные, особенно когда в конце месяца активно работают продажники, логистика, бухгалтерия..

[AlGol]

Если по условию которое установлено в запросе есть индекс, то сканирование таблицы не должно делаться. Максимум сканирование индекса.

Попробуй для начала сделать проверку/синхронизацию по таблицам в которых наблюдаешь сканирование.
Это вызывается из Администрирование/ Периодические операции/ SQL Администрирование.

Если проверка не даст ошибок, то сделай реиндексацию по таблице (делается там же).

Если после этого все равно все плохо - то надо смотреть что за индексы на таблице, что за запросы долго работают и тд.
методики есть и у mazzy - http://axapta.mazzy.ru/lib/querytuning/

[Vadik]

отчет стандартный?
все очень сильно зависит от кода
к примеру, между теми же строками и шапкой накладной связь далеко не по одному полю InvoiceId, и одному InvoiceId может соответствовать несколько накладных
так что (как вариант) - доля вины программистов здесь присутствует (может присутствовать)

[mazzy]

Цитата:

Сообщение от vesna dba

Наблюдаю отсутсвие PrimaryIndex, ClusterIndex
(и во многоих таблицах, по которым встречаю в планах запросов сканирование таблиц такое положение)

PrimaryIndex - это индекс который будет использоваться по умолчанию в формах и отчетах, если индекс не указан явно. Если не указан PrimaryIndex, то формы и отчеты не будут указывать индекс при получении записей.

Насчет ClusterIndex.
Таковая была стратегия разработчиков. Они считали, что кластерные индексы - это зло.
Исходя из этого тезиса, ядро может сделать уникальным любой индекс, добавив поле recid в к индексу.

[mazzy]

Цитата:

Сообщение от AlGol

Если по условию которое установлено в запросе есть индекс, то сканирование таблицы не должно делаться.

Может делаться и сканирование.
Если оптимизатор решит, что сканирование дешевле просмотра индекса.
А вот на основании каких данных оптимизатор принимает такое решение - вопрос.

[vesna dba]

Цитата:

Сообщение от AlGol

..то сделай реиндексацию по таблице ..

На тестовой базе (недавняя копия рабочей) после изменений индексов (в части - один из индексов делал кластерным(см.начало)) решил вернуть все в исходное. Вернул, реиндексировал таблицу, и .. - НЕТ TABLE SCAN по этой таблице.

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

[vesna dba]

Цитата:

Сообщение от vesna dba

На тестовой базе (недавняя копия рабочей) после изменений индексов (в части - один из индексов делал кластерным(см.начало)) решил вернуть все в исходное. Вернул, реиндексировал таблицу, и .. - НЕТ TABLE SCAN по этой таблице.

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

Реиндексация базы данных не помогла.
Чето не то творится.. План запроса не изменился (TABLE SCAN остался). А в тестовой базе, с такими же свойствами таблицы (поля, индексы ..) нет сканирования. Размерами правда таблицы отличаются. Рабочая в 1,5 раза больше - >3Gb.
Буду делать свежую копию рабочей базы и повторять изменение индекса на кластерный и потом откат - для повтора\подтверждения эффекта.

[AlGol]

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

Ну, в качестве предположения, можно еще статистику по таблице обновить.

[fedka]

Цитата:

Сообщение от AlGol

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

Ну, в качестве предположения, можно еще статистику по таблице обновить.

Разве индексация не включает в себя и статистику?

[vesna dba]

- Создал копию базы рабочей. Построил план запроса в QA (запроса, который отловил в Аксапте через мониторинг запросов SQL (см.первое сообщ.)). - есть сканирование таблицы.
- Делаю индекс (см.первое сообщ.) кластерным (кстати я был не точен в первом сообщ. - индекс составной по нескольким полям). - нет скана таблицы.
- Возвращаю все назад. - есть скан той же таблицы, план запроса тот же.
---
Чуть позже выложу скриншоты запроса и плана запроса до и после изменения индекса, спиок индексов на таблице и комментарий программиста по поводу отчета, в запросе которого получается скан таблицы.

[vesna dba]

Запрос и его план
SELECT MAX(A.NAMEALIAS),MAX(A.VSN_CUSTTYPE),MAX(A.LINEOFBUSINESSID),A.CUSTACCOUNT,MAX(B
.INVOICINGNAME),B.VSN_BONDCODE,SUM(C.VSN_QTY),SUM(C.LINEAMOUNTMST),SUM(C.TAXAMOU
N
TMST),SUM(C.VSN_QTYPCS),SUM(C.QTY),MAX(D.REFINVENTCLASS),MAX(D.REFINVENTRAITING)
,
MAX(D.REFFUNCTIONALSUBGROUP),MAX(D.REFTRADEMARK),MAX(D.VSN_AGGREGATPACKID),MAX(D
.
VSN_GENERALNAME),MAX(D.NAMEALIAS),MAX(D.ITEMNAME),MAX(D.PACKING_RU),D.ITEMID
FROM SMMBUSRELTABLE A,VSN_CUSTINVOICEJOURCONTR B,CUSTINVOICETRANS C,INVENTTABLE D
WHERE (A.DATAAREAID='VC')
AND (((B.DATAAREAID='DAT')
AND (B.DATAAREAID#2='VC'))
AND ((((((B.INVOICEDATE>={TS '2007-02-01 00:00:00.000'})
AND (B.INVOICEDATE<={TS '2007-02-28 00:00:00.000'}))
AND (B.INVOICETYPE=0))
AND (B.VSN_STORNED=0))
AND
NOT ((B.INVOICEACCOUNT=' КЛ-00235')))
AND (A.CUSTACCOUNT=B.INVOICEACCOUNT)))
AND ((C.DATAAREAID='DAT')
AND ((((B.NUMBERSEQUENCEGROUP=C.NUMBERSEQUENCEGROUP)
AND (B.INVOICEDATE=C.INVOICEDATE))
AND (B.INVOICEID=C.INVOICEID))
AND (B.SALESID=C.SALESID)))
AND ((D.DATAAREAID='VC')
AND (C.ITEMID=D.ITEMID))
GROUP BY A.CUSTACCOUNT,B.VSN_BONDCODE,D.ITEMID
ORDER BY A.CUSTACCOUNT,B.VSN_BONDCODE,D.ITEMID OPTION(FAST 15)
[attachment=605:Plan1_Cu...iceTrans.JPG]

Запрос и его план после изменения индекса INVOICEIDX (сделан кластерным)
[attachment=606:Plan2_Cu...iceTrans.JPG]

Индексы на таблице CUSTINVOICETRANS:
I_064INVOICEIDX
по полям:
- DATAAREAID
- SALESID
- INVOICEDATE
- INVOICEID
- LINENUM
- NUMBERSEQUENCEGROUP
I_064ITEMIDIDX
- DATAAREAID
- ITEMID
- INVOICEDATE
I_064RECID
- DATAAREAID
- RECID
I_064TRANSIDIDX
- DATAAREAID
- INVENTTRANSID
- INVOICEID
- INVOICEDATE
I_064VSN_INVOICEID
- DATAAREAID
- INVOICEID

Индексы кроме I_064VSN_INVOICEID из стандартного функционала.
--
Комментарий программиста относительно отчета, в котором разбираем запрос к базе данных, который вызывает TABLE SCAN:
"Отчет не из стандартного функционала Аксапты.
Создан сотрудниками отдела программирования.
Отчет создан стандартными визуальными средствами разработки с использованием объекта Query.
Запросы формируются на уровне ядра и возможности вмешаться в построение запроса к базе данных у нас нет."
---
Гуру, отзовитесь. Наверняка это касается многих.

[Vadik]

я бы для начала отказался от VIEW VSN_CUSTINVOICEJOURCONTR

[vesna dba]

Цитата:

Сообщение от Vadik

я бы для начала отказался от VIEW VSN_CUSTINVOICEJOURCONTR

Это вьюшник по custinvoicejour и rcontracttable т.е . по шапкам накладных и договорам (Отчет в модуле РК, отчет по продажам).
Отказ невозможен.

[Vadik]

Цитата:

Сообщение от vesna dba

Это вьюшник по custinvoicejour и rcontracttable т.е . по шапкам накладных и договорам (Отчет в модуле РК, отчет по продажам).

это я понял

Цитата:

Отказ невозможен.

попробуйте вместо этого view использовать в запросе две таблицы

[vesna dba]

Цитата:

Сообщение от Vadik

попробуйте вместо этого view использовать в запросе две таблицы

А смысл?
Сканирование таблицы вообще не в той стороне.

[vesna dba]

Народ! Есть результат! Интересный и положительный! Завтра на работе буду - опишу все подробно!

[mazzy]

Цитата:

Сообщение от vesna dba

Народ! Есть результат! Интересный и положительный! Завтра на работе буду - опишу все подробно!

Спасибо. Ждем

[vesna dba]

Цитата:

Сообщение от vesna dba

Народ! Есть результат! Интересный и положительный! Завтра на работе буду - опишу все подробно!

Я извиняюсь что каким либо образом обнадежил вас..
Поспешил. Ложная тревога.
--
В результате работы отчета формируются несколько запросов к базе данных. На один из них (не тот, который нужен, но очень был похож) я наткнулся и поспешил с выводами. Стал делать разбор и понял что ушел не туда.
--
Итак топчемся на месте.
Таблица CUSTINVOICETRANS, идет скан таблицы в запросе по полю DATAAREAID.
Поле DATAAREAID входит в состав индексов построенных на этой таблице (индексы из стандартного функционала).
Но при выполнении запроса все таки сваливается в TABLE SCAN.
Если индекс I_064ITEMIDIDX(по полям DATAAREAID, ITEMID, INVOICEDATE) сделать кластерным - план запроса изменяется и становится более эффективным (стоимость плана 458 до, и 37 - после переделки индекса в качестве кластерного).
--
Задача: избавиться от TABLE SCAN не изменяя индексов стандартного функционала.
Вопрос остается открытым.

[vesna dba]

Цитата:

Сообщение от mazzy

Насчет ClusterIndex.
Таковая была стратегия разработчиков. Они считали, что кластерные индексы - это зло.

А где бы почитать про эту стратегию..?

[vesna dba]

Временно индекс I_064ITEMIDIDX(по полям DATAAREAID, ITEMID, INVOICEDATE) сделал кластерным.
Размер таблицы (исходный - 3Gb) увеличился примерно на 600Mb.
Очереди на диске с таблицей CUSTINVOICETRANS во время выполнения отчета "как корова слизала" (средняя очередь к диску уменьшилась с 50 до 2-3).
Длительных блокировок по таблице не наблюдается.
Продолжаю мониторинг. Ищутся другие решения.
---
"Нет ничего постояннее, чем временное."


--
З.Ы.:
Мониторил запросы к этой таблице в модуле Расчеты с клиентами по отчетам из стандартного функционала.
Картинка планов запросов всегда была красивая, без каких либо изъянов.