Простой способ для оптимизирования выполнения SQL-запросов

При выполнении запросов оптимизатор SQL-сервера строит планы запросов и сохраняет их в кэше. Для построения плана запроса требуется информация о индексах, статистиках, количествах строк в таблицах и т.д. Если какие-то данных не хватает, то SQL оптимизатор сохранит информацию об этом в плане запроса. А если мы можем найти эту информацию в планах, то мы можем узнать, какой информации было недостаточно для оптимизирования выполнения запросов. Пример такой информации:

• Недостающие индексы;
• Колонки без статистики.

 В SQL 2005, с помощью следующих Dynamic Management views можно получать информацию о планах выполнения запросов:

• Sys.dm_exec_cached_plans;
• Sys.dm_exec_query_plan;
• Sys.dm_exec_sql_text.

А теперь когда мы узнали, где хранятся данные о планах выполнения запросов, время пришло узнать, как искать нужную информацию. Для этой цели необходимо выполнять следующий SQL-запрос:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Declare @StringToSearchFor Varchar(255)

Set @StringToSearchFor = '%%'

SELECT  st.text

      ,cp.cacheobjtype

      ,cp.objtype

      ,DB_NAME(st.dbid) AS [DatabaseName]

      ,cp.usecounts

      ,qp.query_plan

FROM sys.dm_exec_cached_plans cp

      CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

      CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

WHERE CAST(qp.query_plan AS NVARCHAR(MAX))LIKE @StringToSearchFor

ORDER BY cp.usecounts DESC

Примечание: можно изменить значение @Stringtosearchfor на :

• '%%' чтобы узнать информацию о колонках без статистки;
• '%%' чтобы узнать информацию о недостающих индексах;
• '%%' чтобы узнать какие таблицы были сканированные.

После того, как мы узнали, чего не недостаточно для оптимизирования планов запросов остаётся один вопрос: что нам нужно чтобы, исправлять ситуацию?

Чтобы ответить на этот вопрос, продемонстрирую следующий пример.

В результатах выполнения показанного SQL-запроса получил следующее:

Теперь мы знаем о том, что оптимизатору SQL-сервера нужен какой-то индекс чтобы, оптимизировать план выполнения запросов, но этот индекс отсутствует. Для исправления ситуации необходимо создать нужный индекс, но для этого нам нужно узнать его параметры.

Для получения информацию о нужном индексе, посмотрите план выполнения вопроса в виде XML и там необходимо искать раздел под именном «MissingIndexes», например:

 Теперь вашей задачей останется только создать нужный индекс.