Запросы, для изучения производительности системы mssql server

Административные скрипты MS SQL > Запросы, для изучения производительности системы mssql server
25.02.2014 10:41:36


Наиболее часто встречающиеся слова в статье:

[object_id] [индексы] [DB_NAME] [индекса] [indexes] [запроса] [index_id] [DatabaseName] [индексов] [сведения]


Статья:

Причины задержек в работе серверов
Низкая производительность обычно выражается для пользователя в серии задержек. Каждый раз, когда запрос SQL может запуститься, но ожидает другого ресурса, он записывает сведения о причине задержки. Доступ к этим сведениям можно получить, используя представление DMV sys.dm_os_wait_stats. Изучить собранные причины всех задержек можно при помощи сценария SQL
SELECT TOP 10
 [Wait type] = wait_type,
 [Wait time (s)] = wait_time_ms / 1000,
 [% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 
               / SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' 
ORDER BY wait_time_ms DESC;

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

Чтение и запись
Интенсивное использование ввода/вывода может быть признаком неудачных механизмов доступа к данным. SQL Server 2005 отслеживает общее число операций чтения и записи, используемых каждым запросом для удовлетворения своих нужд. Эти числа можно просуммировать, чтобы определить, какие базы данных выполняют наибольшее общее число операций чтения и записи.
Представление DMV sys.dm_exec_query_stats содержит объединенную статистику производительности для кэшированных планов запросов. В нее входят сведения о числе логических операций чтения и записи и о числе случаев исполнения запроса. Присоединив это представление DMV к динамической административной функции (DMF) sys.dm_exec_sql_text, можно просуммировать число прочтений и записей базы данных. Обратите внимание на то, что я использовал CROSS APPLY, новый оператор SQL Server 2005, для проведения этого присоединения. Сценарий, который я использую для определения баз данных, наиболее активно использующих чтение и запись
SELECT TOP 10 
        [Total Reads] = SUM(total_logical_reads)
        ,[Execution count] = SUM(qs.execution_count)
        ,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;

SELECT TOP 10 
        [Total Writes] = SUM(total_logical_writes)
        ,[Execution count] = SUM(qs.execution_count)
        ,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Writes] DESC;

Результаты указывают, какие базы данных читают и записывают больше всего логических страниц. Верхний набор данных упорядочен по общему числу прочтений, а нижний – по общему числу записей.
Как несложно заметить, DatabaseName в ряде случаев установлен на NULL. Этот параметр определяет специально подобранные и подготовленные к этому случаю операторы SQL. Такая деталь полезна при определении степени использования собственно языка SQL – что само по себе может стать причиной многих проблем. (Например, это указывает на отсутствие повторного использования планов запросов и кода, а также на потенциальные недостатки безопасности.)
Высокое значение tempdb может означать избыточное использование временных таблиц, избыточную рекомпиляцию или неэкономное устройство. Результаты можно использовать для отделения баз, используемых в основном для отчетов (частый выбор данных), от транзакционных баз данных (частые обновления). Эти два типа баз данных требуют различного индексирования. Ниже мы рассмотрим эту проблему в подробностях.
Базы данных и их отсутствующие индексы
Когда SQL Server обрабатывает запрос, оптимизатор ведет запись индексов, используемых в попытке удовлетворить запрос. Если индексы не найдены, SQL Server создает запись об отсутствующем индексе. Все эти записи можно просмотреть, используя представление DMV sys.dm_db_missing_index_details. Обнаружение этих отсутствующих индексов важно, поскольку индексы часто предоставляют идеальный путь к получению данных запросов. Это, в свою очередь, может уменьшить ввод-вывод и улучшить общую производительность. Мой сценарий изучает sys.dm_db_missing_index_details и выводит итоговое число отсутствующих индексов для каждой базы данных, позволяя легко определить, какие базы данных стоит исследовать дальше.
SELECT 
    DatabaseName = DB_NAME(database_id)
    ,[Number Indexes Missing] = count(*) 
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;

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

 

Отсутствующие индексы, вызывающие издержки
Влияние индексов на производительность запросов непостоянно. Можно получить подробные сведения об отсутствующих индексах на всех базах данных сервера, вызывающих наибольшие издержки, выясняя, восстановление которых из них может оказать наиболее серьезное позитивное влияние на производительность.
В представлении DMV sys.dm_db_missing_index_group_stats отмечается число попыток SQL использовать определенный отсутствующий индекс. В представлении DMV sys.dm_db_missing_index_details приводятся подробности о структуре отсутствующего индекса, такие как число столбцов, требуемое запросом. Эти два представления DMV связаны вместе через представление DMV sys.dm_db_missing_index_groups. Издержки ресурсов, связанные с отсутствующим индексом (столбец общих издержек) вычисляются посредством перемножения средних общих издержек использования, среднего влияния на пользователей и суммы произведенных пользователями операций поиска и просмотра.
Для определения вызывающих наибольшие издержки отсутствующих индексов можно использовать сценарий, см ниже.Результаты этого опроса, упорядоченные по общему уровню издержек, показывают издержки, вызванные наиболее важными отсутствующими индексами, вместе со сведениями о базе данных/схеме/таблице и столбцах, необходимых в отсутствующих индексах. Говоря точнее, этот сценарий определяет, какие столбцы используются в операторах равенства и неравенства языка SQL. Кроме того, он сообщает о том, какие из прочих столбцов следует использовать как включенные столбцы в отсутствующем индексе. Включенные столбцы позволяет удовлетворять большее число охваченных запросов, в то же время получая данные от лежащей в основе страницы, что уменьшает число операций ввода/вывода и улучшает производительность.
SELECT  TOP 10 
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns 
        , [InequalityUsage] = inequality_columns
        , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g 
INNER JOIN    sys.dm_db_missing_index_group_stats s 
       ON s.group_handle = g.index_group_handle 
INNER JOIN    sys.dm_db_missing_index_details d 
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

 

Обратите внимание на то, что результаты не указывают порядок, в котором следует создавать столбцы требуемого индекса. Для определения этого следует проверить сборную базу кода SQL. Как общее правило, большинство выбранных столбцов должны появляться первыми в индексе.
Я также должен указать, что только столбцы пользователей (такие как user_seeks и user_scans) учитываются при вычислении издержек отсутствующего индекса. Это объясняется тем, что системные столбцы обычно представляют статистику использования, а также команды проверки согласованности баз данных(DBCC) и языка определения данных (DDL), которые менее существенны для выполнения бизнес-задач (в отличие от задач администратора базы данных).
При вычислении потенциальных издержек на дополнительный индекс очень важно помнить о необходимости специального учета того, когда происходят изменения данных в лежащей в основе таблице. По этой причине, стоит озаботиться дополнительным изучением лежащей в основе базы кода SQL.
Если обнаружится, что к включению рекомендовано много столбцов, следует изучить лежащий в основе код SQL, поскольку это может указывать на использование сборного оператора "SELECT *" – если он действительно используется, то, вероятно, стоит пересмотреть запросы SELECT.

 

Неиспользуемые индексы
Неиспользуемые индексы могут оказывать негативное влияние на производительность. Причина этого заключается в том, что при изменении данных в базовой таблице индекс также может нуждаться в изменении. Это, само собой, требует дополнительного времени и может даже увеличить вероятность блокировки.
При использовании индекса для удовлетворения запроса и при его обновлении вследствие обновления данных базовой таблицы SQL Server обновляет соответствующие сведения об использовании индекса. Эти сведения об использовании можно просмотреть для определения неиспользуемых индексов.
В представлении DMV sys.dm_db_index_usage_stats сообщается, как часто и в какой степени используются индексы. Это представление присоединено к представлению DMV sys.indexes, содержащему сведения, используемые при создании индекса. Для определения неиспользуемых индексов можно проверить столбцы пользователей на предмет наличия значения 0. Влияние системных столбцов опять игнорируется по указанным выше причинам. Для определения вызывающих наибольшие издержки неиспользуемых индексов можно использовать сценарий
-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the following step.
SELECT TOP 1
        DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,user_updates    
        ,system_updates    
        -- Useful fields below:
        --, *
INTO #TempUnusedIndexes
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND    user_seeks = 0
    AND user_scans = 0 
    AND user_lookups = 0
    AND s.[object_id] = -999  -- Dummy value to get table structure.
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB    'USE [?]; 
-- Table already exists.
INSERT INTO #TempUnusedIndexes 
SELECT TOP 10    
        DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,user_updates    
        ,system_updates    
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
    AND    user_seeks = 0
    AND user_scans = 0 
    AND user_lookups = 0
    AND i.name IS NOT NULL    -- Ignore HEAP indexes.
ORDER BY user_updates DESC
;
'

-- Select records.
SELECT TOP 10 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC
-- Tidy up.
DROP TABLE #TempUnusedIndexes

 

В результатах данного запроса показаны индексы, которые не использовались для извлечения данных, но обновлены в результате изменений в базовой таблице. Эти обновления показаны в столбцах user_updates и system_updates. Результаты упорядочены по числу пользовательских обновлений, примененных к индексу.
Необходимо собрать достаточно сведений, чтобы убедиться в отсутствии использования индекса – очень неприятно было бы удалить индекс, возможно, являющийся крайне важным для запроса, производимого раз в квартал или год. Кроме того, обратите внимание на то, что некоторые индексы используются для ограничения числа одинаковых записей или для упорядочивания данных, эти факторы также стоит учесть перед удалением неиспользуемых индексов.
Базовая форма запроса применяется только к текущей базе данных, поскольку она присоединяется к представлению DMV sys.indexes, ограниченному текущей базой данных. Результаты для всех баз данных на сервере можно извлечь, используя системную хранимую процедуру sp_MSForEachDB. Шаблон, который я использую для этого, разобран на боковой панели «Организация цикла по всем базам данных». Я использую этот шаблон и в других разделах сценария, где требуется перебрать все базы данных на сервере. Кроме того, я отфильтровал индексы, принадлежащие к типу heap, поскольку они представляют собственную структуру таблицы без формального индекса.

Индексы с высокими издержками при использовании
Также может быть полезным определение используемых индексов, вызывающих наибольшие издержки при изменениях в базовых таблицах. Эти издержки негативно влияют на производительность, но сам индекс может быть важен для получения данных.
Представление DMV sys.dm_db_index_usage_stats позволяет увидеть, как часто и в какой степени используются индексы. Это представление присоединено к представлению DMV sys.indexes, которое содержит сведения, используемые при создании индекса. Изучение столбцов user_updates и system_updates позволяет найти индексы, поддержка которых наиболее затратна. 
-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the following step.
SELECT TOP 1
        [Maintenance cost]  = (user_updates + system_updates)
        ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
INTO #TempMaintenanceCost
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id
WHERE s.database_id = DB_ID() 
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND (user_updates + system_updates) > 0 -- Only report on active rows.
    AND s.[object_id] = -999  -- Dummy value to get table structure.
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB    'USE [?]; 
-- Table already exists.
INSERT INTO #TempMaintenanceCost 
SELECT TOP 10
        [Maintenance cost]  = (user_updates + system_updates)
        ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id
WHERE s.database_id = DB_ID() 
    AND i.name IS NOT NULL    -- Ignore HEAP indexes.
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
    AND (user_updates + system_updates) > 0 -- Only report on active rows.
ORDER BY [Maintenance cost]  DESC
;
'

-- Select records.
SELECT TOP 10 * FROM #TempMaintenanceCost 
ORDER BY [Maintenance cost]  DESC
-- Tidy up.
DROP TABLE #TempMaintenanceCost

Результаты раскрывают наиболее затратные в поддержании индексы вместе со сведениями о задействованных базах данных/таблицах. Столбец Maintenance cost (Издержки на поддержание) показывает сумму столбцов user_updates и system_updates. Полезность индекса (показанная в столбце Retrieval usage (Использование получения)), является суммой различных столбцов user_*. Важно рассмотреть полезность индекса перед принятием решения о его удалении.
Когда речь идет о массовых изменениях данных, эти результаты помогают определить индексы, которые следует удалить перед проведением обновлений. После завершения обновлений эти индексы можно применить заново.

 

Часто используемые индексы

Представления DMV можно использовать для определения того, какие индексы используются наиболее часто – они будут основными путями к лежащим в основе данным. Некоторые индексы могут предоставить существенное общее повышение производительности в случае, если их удастся улучшить или оптимизировать.
Представление DMV sys.dm_db_index_usage_stats содержит сведения о том, как часто индексы используются для получения данных посредством поиска и просмотра. Это представление присоединено к представлению DMV sys.indexes, содержащему сведения, используемые при создании индекса. Столбец Usage («Использование») вычисляется как сумма всех полей user_*. Это можно проделать, используя сценарий, показанный на рис. 7. Результаты этого запроса, упорядоченные по значению Usage, показывают число использований индекса.
-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the -- following step.
SELECT TOP 1
        [Usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
INTO #TempUsage
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE   s.database_id = DB_ID() 
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND (user_seeks + user_scans + user_lookups) > 0 
-- Only report on active rows.
    AND s.[object_id] = -999  -- Dummy value to get table structure.
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB    'USE [?]; 
-- Table already exists.
INSERT INTO #TempUsage 
SELECT TOP 10
        [Usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE   s.database_id = DB_ID() 
    AND i.name IS NOT NULL    -- Ignore HEAP indexes.
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
    AND (user_seeks + user_scans + user_lookups) > 0 -- Only report on active rows.
ORDER BY [Usage]  DESC
;
'

-- Select records.
SELECT TOP 10 * FROM #TempUsage ORDER BY [Usage] DESC
-- Tidy up.
DROP TABLE #TempUsage

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

 

Логически фрагментированные индексы
Логическая фрагментация индекса отражает процент записей индекса, находящихся не на своем месте. Этот тип фрагментации не идентичен типу, связанному с заполненностью страниц. Логическая фрагментация влияет на любые проверки порядка, использующие индекс. Ее следует устранять при первой же возможности. Этого можно добиться путем перестройки или реорганизации индекса.
Наиболее логически фрагментированные индексы можно определить, используя следующие представления DMV. Представление DMV sys.dm_db_index_physical_stats позволяет просматривать сведения о размере и степени фрагментации индексов. Это представление присоединено к представлению DMV sys.indexes, содержащему сведения, используемые при создании индекса.
-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the -- following step.
SELECT TOP 1 
        DatbaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)
INTO #TempFragmentation
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE s.[object_id] = -999  -- Dummy value just to get table structure.
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB    'USE [?]; 
-- Table already exists.
INSERT INTO #TempFragmentation 
SELECT TOP 10
        DatbaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE s.database_id = DB_ID() 
      AND i.name IS NOT NULL    -- Ignore HEAP indexes.
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
ORDER BY [Fragmentation %] DESC
;
'

-- Select records.
SELECT TOP 10 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC
-- Tidy up.
DROP TABLE #TempFragmentation

Запросы с высокими издержками на ввод-вывод
Ввод-вывод является мерой операций чтения-записи, совершаемых запросом. Его можно использовать как показатель экономичности запроса – запросы с большими значениями ввода-вывода часто являются хорошими объектам для улучшения производительности.
Представление DMV sys.dm_exec_query_stats предоставляет объединенную статистику производительности кэшированных планов запросов, включая сведения о физическом и логическом вводе/выводе и о числе выполнений запроса. Оно содержит смещения, использованные для извлечения реального SQL из вложенного родительского SQL. Это представление DMV присоединяется к представлению DMF sys.dm_exec_sql_text, содержащему информацию о пакете SQL, к которому относится ввод-вывод. Различные смещения применяются к этому пакету для получения лежащих в основе индивидуальных запросов SQL.
SELECT TOP 10 
 [Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) 
        ,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;

Запросы с высоким использованием ресурсов ЦП
Другим довольно полезным подходом является анализ того, какие запросы поглощают больше всего ресурсов ЦП. Такой подход может очень хорошо выявлять запросы с низкой производительностью. Здесь я использую те же представления DMV
SELECT TOP 10 
 [Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - 
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;
Запрос возвращает среднее использование ЦП, общее использование ЦП, индивидуальный запрос и родительский запрос (если индивидуальный запрос является частью пакета), а также имя соответствующей базы данных. Как я уже заметил раньше, также, вероятно, стоит проверить запрос в помощнике по настройке баз данных, чтобы определить, можно ли внести в него дополнительные улучшения.

 

Запросы с высокими издержками в отношении CLR
SQL Server все шире использует CLR. Следовательно, может быть полезным определение запросов, наиболее активно использующих CLR, включая хранимые процедуры, функции и триггеры.
Представление DMV sys.dm_exec_query_stats содержит сведения о total_clr_time и число выполнений запроса. Оно также содержит смещения, используемые для извлечения реального запроса из вложенного родительского запроса. Это DMV присоединяется к представлению DMF sys.dm_exec_sql_text, содержащему информацию о пакете SQL. Различные смещения применяются к этому пакету для получения лежащих в основе операторов SQL. 

 

Наиболее часто выполняемые запросы
Предыдущий пример для запросов CLR с высокими издержками можно изменить так, чтобы он определял наиболее часто выполняемые запросы. Обратите внимание на то, что здесь применяются те же представления DMV. Улучшение производительности очень часто выполняемых запросов может предоставить большее общее улучшение производительности, чем оптимизация большого, но редко выполняемого запроса. (В качестве страховочной меры следует выполнить перекрестную проверку путем изучения запросов с наиболее высоким вводом-выводом или использованием ресурсов процессора.) Другим преимуществом улучшения часто выполняемого запроса является возможность уменьшить число блокировок и длину транзакций. Само собой, конечным результатом является общее улучшение времени отклика системы.
SELECT TOP 10 
 [Execution count] = execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Execution count] DESC;

 

Запросы, страдающие от блокировки
От блокировки обычно страдают долго работающие запросы. После определения этих запросов анализ позволяет определить, можно ли и следует ли переписать их для борьбы с блокировкой. В число причин блокировки входят непоследовательность в порядке использования объектов, конфликтующие области транзакций и обновление неиспользуемых индексов.
Представление DMV sys.dm_exec_query_stats, о котором я рассказал выше, содержит столбцы, которые можно использовать для определения запросов, наиболее страдающих от блокировки. Среднее время блокировки определяется как разница между total_elaspsed_time и total_worker_time, деленная на execution_count.
Функция DMF sys.dm_exec_sql_text содержит сведения о пакете SQL, к которому относится блокировка. Различные смещения применяются к нему для получения лежащих в основе операторов SQL.
SELECT TOP 10 
 [Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count
,[Total Time Blocked] = total_elapsed_time - total_worker_time 
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) 
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average Time Blocked] DESC;
Изучение запроса также позволяет найти недостатки проекта (такие, как отсутствующие индексы), проблемы с транзакциями (использование ресурсов не в нужном порядке) и так далее. Помощник по настройке баз данных также может выделить возможные улучшения.
Минимальное повторное использование планов
Одним из преимуществ использования хранимых процедур является кэширование плана запроса и возможность использовать его повторно без новой компиляции. Это экономит время, ресурсы и улучшает производительность. Планы запросов, которые реже всего используются повторно, можно определить с целью дальнейшего изучения того, почему это так. Некоторые из них, вероятно, можно будет переписать для оптимизации повторного использования.
SELECT TOP 10
 [Plan usage] = cp.usecounts
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), 
qt.text)) * 2 ELSE qs.statement_end_offset END - 
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
,cp.cacheobjtype
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
INNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
WHERE cp.plan_handle=qs.plan_handle
ORDER BY [Plan usage] ASC;
Затем можно изучить найденные индивидуальные запросы, чтобы определить причину, по которой их планы не используются повторно более часто. Одной из возможных причин является перекомпиляция запроса при каждом запуске – это может произойти, если запрос содержит различные операторы SET или временные таблицы.