Загрузка процессора по базе данных?

Можно ли получить разбивку загрузки процессора по базе данных?

Я идеально ищу интерфейс типа Task Manager для SQL-сервера, но вместо того, чтобы смотреть на использование ЦП каждого PID (например, taskmgr) или каждый SPID (например, spwho2k5), я хочу просмотреть общее использование ЦП каждой базы данных. Предположим, что один экземпляр SQL.

Я понимаю, что инструменты могут быть записаны, чтобы собирать эти данные и сообщать об этом, но мне интересно, есть ли какой-либо инструмент, который позволяет мне видеть живое представление о том, какие базы данных в наибольшей степени способствуют загрузке процессора sqlservr.exe.

Ответ 1

Сорт. Проверьте этот запрос:

SELECT total_worker_time/execution_count AS AvgCPU  
, total_worker_time AS TotalCPU
, total_elapsed_time/execution_count AS AvgDuration  
, total_elapsed_time AS TotalDuration  
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads 
, (total_logical_reads+total_physical_reads) AS TotalReads
, execution_count   
, 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 txt  
, 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 qp 
ORDER BY 1 DESC

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

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

USE msdb
DECLARE @StringToExecute VARCHAR(1000)
SET @StringToExecute = 'SELECT * FROM AdventureWorks.dbo.ErrorLog'
EXEC @StringToExecute

Запрос будет выполнен в MSDB, но он будет опросить результаты AdventureWorks. Где мы должны назначить потребление ЦП?

Ухудшается, когда вы:

Соединение между несколькими базами данных
Запустите транзакцию в нескольких базах данных, а задержка блокирует несколько баз данных.
Запуск заданий агента SQL в MSDB, которые "работают" в MSDB, но создают резервные копии отдельных баз данных.

Это продолжается и продолжается. Поэтому имеет смысл настраивать производительность на уровне запросов, а не на уровне базы данных.

В SQL Server 2008R2 Microsoft представила функции управления производительностью и функциями управления приложениями, которые позволят нам упаковать единую базу данных в распространяемом и развертываемом пакете DAC, и они являются многообещающими функциями, чтобы упростить управление производительностью отдельных баз данных и их Приложения. Тем не менее, он все еще не выполняет то, что вы ищете.

Для получения дополнительной информации посетите репозиторий T-SQL в вики Wiki (ранее на SQLServerPedia) в Toad World.

Обновлено на 1/29, чтобы включить общее число вместо средних.

Ответ 2

SQL Server (начиная с 2000 года) установит счетчики производительности (можно просматривать из Performance Monitor или Perfmon).

Одна из категорий счетчиков (из установки SQL Server 2005::) - SQLServer: Базы данных

С одним экземпляром для каждой базы данных. Однако доступные счетчики не обеспечивают счетчик использования% CPU или что-то подобное, хотя есть некоторые счетчики скорости, которые можно использовать для получения хорошей оценки CPU. Например, если у вас есть 2 базы данных, а измеренная скорость составляет 20 транзакций/с в базе данных A и 80 транзакций/сек в базе данных B - тогда вы бы знали, что A составляет примерно 20% от общего ЦП и B способствует другим 80%.

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

Ответ 3

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

select dbs.name, cacheobjtype, total_cpu_time, total_execution_count from
    (select top 10
        sum(qs.total_worker_time) as total_cpu_time,  
        sum(qs.execution_count) as total_execution_count, 
        count(*) as  number_of_statements,  
        qs.plan_handle
    from  
        sys.dm_exec_query_stats qs 
    group by qs.plan_handle
    order by sum(qs.total_worker_time) desc
    ) a
inner join 
(SELECT plan_handle, pvt.dbid, cacheobjtype
FROM (
    SELECT plan_handle, epa.attribute, epa.value, cacheobjtype
    FROM sys.dm_exec_cached_plans 
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
     /* WHERE cacheobjtype = 'Compiled Plan' AND objtype = 'adhoc' */) AS ecpa 
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("dbid", "sql_handle")) AS pvt
) b on a.plan_handle = b.plan_handle
inner join sys.databases dbs on dbid = dbs.database_id

Ответ 4

Я думаю, что ответ на ваш вопрос - нет.

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

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

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

Ответ 5

Учитывая все сказанное выше.
Начиная с SQL Server 2012 (может быть, 2008?), Есть столбец database_id в sys.dm_exec_sessions.
Это дает нам простой расчет процессора для каждой базы данных для сеансов в настоящее время. Если сеанс отключен, результаты его исчезли.

select session_id, cpu_time, program_name, login_name, database_id 
  from sys.dm_exec_sessions 
 where session_id > 50;

select sum(cpu_time)/1000 as cpu_seconds, database_id 
 from sys.dm_exec_sessions 
group by database_id
order by cpu_seconds desc;

Ответ 6

Посмотрите SQL Sentry. Он делает все, что вам нужно, и многое другое.

С уважением, Ливны

Ответ 7

Посмотрели ли вы на профилировщик SQL?

Возьмите стандартный шаблон "T-SQL" или "Хранимая процедура", настройте поля для группировки по идентификатору базы данных (я думаю, вы должны использовать этот номер, вы не получите имя базы данных, но легко узнать используя exec sp_databases для получения списка)

Запустите это некоторое время, и вы получите общее количество CPU/Disk IO/Wait и т.д. Это может дать вам долю процессора, используемого каждой базой данных.

Если вы одновременно контролируете счетчик PerfMon (регистрируете данные в базе данных SQL) и выполняете то же самое для SQL Profiler (журнал для базы данных), вы можете сопоставить их вместе.

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

Ответ 8

проверьте этот запрос:

SELECT 
    DB_NAME(st.dbid) AS DatabaseName
    ,OBJECT_SCHEMA_NAME(st.objectid,dbid) AS SchemaName
    ,cp.objtype AS ObjectType
    ,OBJECT_NAME(st.objectid,dbid) AS Objects
    ,MAX(cp.usecounts)AS Total_Execution_count
    ,SUM(qs.total_worker_time) AS Total_CPU_Time
    ,SUM(qs.total_worker_time) / (max(cp.usecounts) * 1.0) AS Avg_CPU_Time 
FROM sys.dm_exec_cached_plans cp 
INNER JOIN sys.dm_exec_query_stats qs 
    ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid) IS NOT NULL
GROUP BY DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid),cp.objtype,OBJECT_NAME(objectid,st.dbid) 
ORDER BY sum(qs.total_worker_time) desc