Как проверить, какая хранимая процедура занимает максимальное время на сервере sql

Если у вас есть SQL 2005 +, вы можете запускать стандартные отчеты в отчетах управления. Щелкните правой кнопкой мыши базу данных в студии управления и выберите Статистика выполнения объекта - это работает только с момента последней перезагрузки. Вы также можете запросить это с помощью DMV sys.dm_exec_query_stats

. Если вы используете более старую версию SQL, используйте SQL Server Profiler, он покажет вам время выполнения, чтения, ЦП, записи, и вы можете отфильтровать пороговые значения для любого из них.

Что касается платных инструментов, я бы настоятельно рекомендовал Ignite для SQL Server из Confio Software ]. Вы можете скачать бесплатную пробную версию и испытать ее. Мы пользуемся им уже около 2 лет и очень довольны результатами. В нашем случае мы выбрали несколько хранимых процедур «низко висящие», которые оптимизировали для быстрых результатов, как раз во время запуска демонстрации. Этого было достаточно, чтобы убедить нас в том, что это выгодное вложение.

Если вы использовали SQL Server 2008, вы можете поэкспериментировать с регулятором ресурсов, который позволяет регулировать и контролировать рабочую нагрузку процессора и памяти.

Для ведения журнала вы можете использовать Сбор данных . Вы можете создать настраиваемый набор сбора, который собирает моментальные снимки из любых DMV и загружает их в хранилище данных о производительности.

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

Из выигравшего ответа на недавний вопрос SO , это даст вам 50 наиболее часто используемых процедур и операторы в процедурах . Вы можете изменить ТОП 50 на ТОП 1 или любое другое число, которое вы хотите видеть.

SELECT TOP 50 *
FROM ( SELECT COALESCE(OBJECT_NAME(s2.objectid), 'Ad-Hoc') AS ProcName
           ,execution_count
           ,s2.objectid
           ,( SELECT TOP 1 SUBSTRING(s2.text,
                                     statement_start_offset / 2 + 1,
                                     ( ( CASE WHEN statement_end_offset = -1
                                              THEN ( LEN(CONVERT(NVARCHAR(MAX), s2.text))
                                                     * 2 )
                                              ELSE statement_end_offset
                                         END ) - statement_start_offset )
                                     / 2 + 1)
            ) AS sql_statement
           ,last_execution_time
        FROM sys.dm_exec_query_stats AS s1
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
     ) x
WHERE sql_statement NOT LIKE 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
    AND OBJECTPROPERTYEX(x.objectid, 'IsProcedure') = 1
    AND EXISTS ( SELECT 1
                    FROM sys.procedures s
                    WHERE s.is_ms_shipped = 0
                        AND s.name = x.ProcName )
ORDER BY execution_count DESC

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

Это то, что я обычно использую. Не могу вспомнить, откуда я их взял, но они работают. Этот для самых длительных запросов:

SELECT TOP 100
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
    qs.total_elapsed_time / 1000000.0 AS total_seconds,
    qs.execution_count,
    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) AS individual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_seconds DESC;

... и этот дает список запросов, использующих наибольшее количество операций ввода-вывода:

SELECT TOP 100
    (total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
    (total_logical_reads + total_logical_writes) AS total_IO,
    qs.execution_count AS execution_count,
    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) AS individual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_IO DESC;

Надеюсь, это поможет!

Вы можете использовать SQL Server Profiler:

1. Создайте новую трассировку.
2. Выберите нужный сервер
3. Выберите шаблон «пустой»
4. На вкладке «Выбор событий» выберите "SP: Завершено" (в разделе "Сохраненные" Процедуры)
5. Включите нужные столбцы.
6. В фильтрах столбцов выберите Продолжительность. и введите значение "Больше, чем" или равно '(обратите внимание на единицы измерения описано выше; это либо миллисекунды или микросекунды). Это ваш «порог»
7. Щелкните «Выполнить»

Примечания:

• Это исключает время для загрузки SP; если ваши SP очень большие, это будет добавить к указанным выше разам
• Иногда у меня были проблемы с значение Продолжительности; если у вас есть проблемы с этим, вы можете захотеть dateiff время окончания / начала

Также обратите внимание, что в SQL 2008 есть новое DMV специально для процедур: sys.dm_exec_procedure_stats. Это представление предоставит вам совокупную статистику для всей процедуры, включая время и процессор, потраченные на выполнение работы, не связанной с запросами, такой как WAITFOR, вызовы функций и условная логика.

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

Еще один инструмент, который вы, возможно, захотите изучить, если имеете дело с SQL 2008, - это сборщик данных и хранилище данных управления. Это инструмент, поставляемый с SQL Server (без дополнительных затрат), который позволит вам регулярно собирать статистику запросов и выгружать ее в хранилище данных. Он поставляется со встроенными сборщиками и отчетами, которые достаточно полны для наиболее распространенных потребностей в мониторинге.

http://msdn.microsoft.com / en-us / library / dd939169 (SQL.100) .aspx

Из всего упомянутого выше, я думаю, вам лучше всего подходит sys.dm_exec_query_stats, поскольку он требует минимальных затрат, бесплатен и доступен как в SQL 2005. и SQL 2008.