Сразу к делу.
Я надеюсь, к концу чтения этой публикации у Вас появятся некоторые представления, а если у Вас они есть, то Вы можете не читать дальше, т.к. это весьма субъективный вопрос.
Держите в своей голове несколько принципов написания запросов:
- В маленьких табличках нечего оптимизировать, т.к. SQL сервер в любом случае будет просматривать всю табличку, а 1С не забывает делать один кластеризованный индекс.
- Если вы намерены получить ограниченное количество строк (одну или две), то не забывайте сообщить об этом заранее, добавляя «первые K» и сортировку вдоль отсортированного поля.
- Поиск в отсортированном поле выполняется быстрее, чем в любом другом. Вам нужно соединять таблицы или выбирать из них строки по отсортированным полям (в 1С есть свойство индексирования у реквизитов).
- Старайтесь обходиться левыми соединениями и поднимать вверх таблички с наименьшим количеством строк (оптимизатор SQL сервера будет стараться делать это за Вас, опираясь на статистику, и, конечно, может ухудшить ожидаемое время, если соединений слишком много, ну, скажем, 7 и более).
- Если условий много, то складывайте во временную таблицу небольшой объем нужных строк, отбирая их по индексу. А потом используйте этот небольшой набор, чтобы добавить прочие условия или левые соединения. (Оптимизатор SQL вполне может «напутать» с порядком формирования таких начальных наборов, и ждать придется долго).
- Если есть вложенные запросы и много вычисляемых полей, то старайтесь отложить вычисления на позднее время, когда количество отобранных строк максимально сократится.
- Добавляйте требуемые индексы к реквизитам, значения которых редко повторяются. Поиск хорошо «разгоняется» по таким полям.
Пути оптимизации запросов:
- Добавление и удаление индексов. Это позволит ускорить поиск и отбор нужных строк.
- Изменение текста запросов. Это позволит изменить стратегию отбора нужных строк из таблиц.
Модель поведения программиста при оптимизации запросов.
Обычно проблема появляется резко и без подсказок. А нам нужно быстро собрать сведения.
1) Работа не клеится, когда кто-то стоит над душой. Поэтому открываем Management Studio, подключаемся к нашей базе и нажимаем волшебную комбинацию <Ctrl>+<Alt>+A. В результате откроется окно, где мы для виду будем тыкать на кнопочки с умным видом, пока нас не оставят в покое, решив, что мы уже работаем. Это лучше потренировать заранее.
2) Проверим для начала, не ждут ли пользователи друг друга:
/*Кто кого блокирует */
SELECT
pr1.status,
pr1.waittime as [Сколько ждем?],
pr1.waitresource as [Что ждем?],
pr1.waittype as [Тип ожидания],
DB_NAME(pr1.dbid) AS [DB],
pr1.spid AS [spID ждущего],
RTRIM(pr1.loginame) AS [Login ждущего],
RTRIM(pr1.hostname) AS [Компьютер ждущего],
pr1.program_name AS [программа ждущего],
pr2.spid AS [spID виновника],
RTRIM(pr2.loginame) AS [Login виновника],
RTRIM(pr2.hostname) AS [Компьютер виновника],
pr2.program_name AS [программа виновника],
txt.[text] AS [Запрос виновника],
pr1.*
FROM MASTER.dbo.sysprocesses as pr1(NOLOCK)
left JOIN MASTER.dbo.sysprocesses as pr2(NOLOCK) ON (pr2.spid = pr1.blocked)
OUTER APPLY MASTER.sys.dm_exec_sql_text(pr2.[sql_handle]) AS txt
WHERE pr1.blocked <> 0
--or pr1.hostname='ws-msk-a1573'
--or pr1.spid in (87)
Скажу честно, я давно не сталкиваюсь с блокировками, поэтому не буду касаться здесь деталей.
3) Проверим, не ждут ли пользователи долгие запросы:
/* Список тяжелых запросов */
SELECT TOP 555
execution_count,
total_worker_time/(execution_count*1000) AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text,
pt.query_plan
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS pt
--where st.text like '%dnprefix%Docno>%'
ORDER BY 2 DESC
Этот запрос (в отличие от предыдущего) всегда что-то возвращает. В первой колонке количество запросов, а во второй длительность выполнения (смело округляйте до тысяч, и это будут секунды). Найдите строки с большим количеством в первой колонке и подумайте, могут ли на это жаловаться пользователи, взглянув на время выполнения. Имеет смысл анализировать запросы длительностью большей и близкой к 1 секунде. Третья колонка содержит текст запроса (и его придется искать в 1С, связав имена таблиц в запросе с привычными именами 1С). Четвертая колонка содержит план выполнения запроса, и если Вы его откроете, то узнаете, из каких источников и в каком порядке выбираются данные (есть даже совет, какого индекса не хватает).
3) Разработчиком был программист (хочется об этом мечтать), а пользоваться готовым функционалом дали пользователям, которые преследуют какие-то свои цели. Поэтому часть имеющихся индексов может просто тормозить базу. Это, в первую очередь, индексы на полях, значения которых часто повторяются, или не используемые в запросах индексы:
/* Index Read/Write stats (all tables in current DB) */
SELECT
OBJECT_NAME(s.[object_id]) AS [ObjectName],
i.name AS [IndexName],
i.index_id,
user_seeks + user_scans + user_lookups AS [Reads],
user_updates AS [Writes],
i.type_desc AS [IndexType],
i.fill_factor AS [FillFactor]
FROM
sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
WHERE
OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
--Укажите анализируемую таблицу (Комментарий в SQL –это «--«)
--and OBJECT_NAME(s.[object_id]) like '_Reference44'
ORDER BY
OBJECT_NAME(s.[object_id]),
writes DESC,
reads DESC;
Если в поле Read стоит ноль или число значительно меньшее числа в поле Write, то индекс скорее мешает и его лучше убрать. Если Вы сами не можете принять такое решение, то спросите:
/*Возможно не нужные индексы*/
SELECT
OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.[object_id] = i.[object_id] AND i.index_id = s.index_id
WHERE
OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1
ORDER BY
[Difference] DESC ,
[Total Writes] DESC ,
[Total Reads] ASC ;
А если не верите мне, то сходите https://technet.microsoft.com/ru-ru/library/jj128029.aspx
Очевидные вопросы:
1) Кластеризованный индекс – это отсортированная по каким-то полям сама таблица (а таблица может быть только одна).
2) Дополнительный индекс содержит индексируемые поля и поле кластеризованного индекса. Поиск в таком индексе сводится к отбору значений, по которым будет выполняться поиск в кластеризованном индексе, если требуется информация из других полей (реквизитов).
3) У регистров кластеризованный индекс содержит все измерения в порядке, указанном в конфигурации. А обычный индекс включает реквизит (с сортировкой) и все измерения, т.е. измерения всегда будут задвоены (что увеличивает объем).
4) Поиск в индексе возможен, если указаны все входящие в него поля (и желательно без условий, т.к. SQL допускает только простейшие сравнения в индексах).
5) В плане запроса указано относительное время выполнения операции и сравнительный объем отбираемых данных из источника.
6) Т.к. все отсортировано, то частые изменения предполагают периодическую дефрагментацию. Иначе статистика, накапливаемая сервером, начнет жить своей жизнью, меняя стратегию выборки данных по запросам.
Примечание:
Несмотря на кажущуюся простоту, скоро Вы поймете, что можно оптимизировать бесконечно и бесконечно зарабатывать большие деньги только этим. Удачи!
