Como obter o uso da CPU pelo banco de dados para uma instância específica?

Embora eu, como @Thomas, concorde completamente com @Aaron nos comentários sobre a questão sobre as preocupações do "uso da CPU por banco de dados" ser preciso ou útil, posso pelo menos responder à pergunta sobre por que essas duas consultas são tão diferente. E a razão pela qual eles são diferentes indicará qual é mais preciso, embora esse nível mais alto de precisão ainda seja relativo ao que é especificamente impreciso, portanto, ainda não é realmente preciso ;-).

A primeira consulta usa sys.dm_exec_query_stats para obter informações da CPU (ou seja, total_worker_time). Se você for para a página vinculada que é a documentação do MSDN para esse DMV, verá uma introdução curta de 3 frases e 2 dessas frases nos dão a maior parte do que precisamos para entender o contexto dessas informações ("quão confiável é" e "como se compara a sys.sysprocesses"). Essas duas frases são:

Retorna estatísticas de desempenho agregadas para planos de consulta em cache no SQL Server. ... Quando um plano é removido do cache, as linhas correspondentes são eliminadas desta visualização

A primeira frase, "Retorna estatísticas de desempenho agregadas ", nos diz que as informações neste DMV (assim como vários outros) são cumulativas e não específicas apenas para consultas que estão em execução no momento. Isso também é indicado por um campo nesse DMV que não faz parte da consulta na Pergunta, execution_count, que novamente mostra que se trata de dados cumulativos. E é bastante útil que esses dados sejam cumulativos, pois você pode obter médias, etc., dividindo algumas das métricas pelo execution_count.

A segunda frase, "planos sendo removidos do cache também são removidos deste DMV", indica que não é uma imagem completa, especialmente se o servidor já tiver um cache de plano bastante cheio e estiver sob carga e, portanto, estiver expirando planos com certa frequência. Além disso, a maioria dos DMVs é redefinida quando o servidor é redefinido para que não sejam um histórico verdadeiro, mesmo que essas linhas não tenham sido removidas quando os planos expirarem.

Agora vamos contrastar o acima com sys.sysprocesses. Essa visualização do sistema mostra apenas o que está sendo executado no momento, assim como a combinação de sys.dm_exec_connections , sys.dm_exec_sessions e sys.dm_exec_requests (que é indicado na página vinculada para sys.dm_exec_sessions). Esta é uma visão totalmente diferente do servidor em comparação com o sys.dm_exec_query_statsDMV que mantém os dados mesmo após a conclusão do processo. Ou seja, em relação ao "os resultados da segunda consulta estão errados?" questão, eles não estão errados, eles apenas dizem respeito a um aspecto diferente (ou seja, período de tempo) das estatísticas de desempenho.

Portanto, a consulta usando sys.sysprocessesestá apenas olhando para "agora". E a consulta usando sys.dm_exec_query_statsestá olhando principalmente (talvez) o que aconteceu desde a última reinicialização do serviço SQL Server (ou obviamente reinicialização do sistema). Para análise geral de desempenho, parece que sys.dm_exec_query_statsé muito melhor, mas, novamente, descarta informações úteis o tempo todo. E, em ambos os casos, você também precisa considerar os pontos feitos por @Aaron nos comentários da pergunta (já removidos) em relação à precisão do valor "database_id" em primeiro lugar (ou seja, reflete apenas o banco de dados ativo que iniciou o código , não necessariamente onde o "problema" está ocorrendo).

Mas, se você apenas precisa/quer ter uma noção do que está acontecendo agora em todos os bancos de dados, possivelmente porque as coisas estão desacelerando agora, é melhor usar a combinação de sys.dm_exec_connections, sys.dm_exec_sessionse sys.dm_exec_requests(e não o obsoleto sys.sysprocesses). Apenas tenha em mente que você está procurando por consultas , não bancos de dados , porque as consultas podem se unir em vários bancos de dados, incluir UDFs de um ou mais bancos de dados etc.

EDIT:
Se a preocupação geral for reduzir os altos consumidores de CPU, procure as consultas que estão ocupando mais CPU, porque os bancos de dados não ocupam a CPU (procurar por banco de dados pode funcionar em uma empresa de hospedagem onde cada banco de dados é isolado e propriedade de um cliente diferente).

A consulta a seguir ajudará a identificar consultas com uso médio de CPU alto. Ele condensa os dados no DMV query_stats, pois esses registros podem mostrar a mesma consulta (sim, o mesmo subconjunto do lote de consultas) várias vezes, cada uma com um plano de execução diferente.

;WITH cte AS
(
  SELECT stat.[sql_handle],
         stat.statement_start_offset,
         stat.statement_end_offset,
         COUNT(*) AS [NumExecutionPlans],
         SUM(stat.execution_count) AS [TotalExecutions],
         ((SUM(stat.total_logical_reads) * 1.0) / SUM(stat.execution_count)) AS [AvgLogicalReads],
         ((SUM(stat.total_worker_time) * 1.0) / SUM(stat.execution_count)) AS [AvgCPU]
  FROM sys.dm_exec_query_stats stat
  GROUP BY stat.[sql_handle], stat.statement_start_offset, stat.statement_end_offset
)
SELECT CONVERT(DECIMAL(15, 5), cte.AvgCPU) AS [AvgCPU],
       CONVERT(DECIMAL(15, 5), cte.AvgLogicalReads) AS [AvgLogicalReads],
       cte.NumExecutionPlans,
       cte.TotalExecutions,
       DB_NAME(txt.[dbid]) AS [DatabaseName],
       OBJECT_NAME(txt.objectid, txt.[dbid]) AS [ObjectName],
       SUBSTRING(txt.[text], (cte.statement_start_offset / 2) + 1,
       (
         (CASE cte.statement_end_offset 
           WHEN -1 THEN DATALENGTH(txt.[text])
           ELSE cte.statement_end_offset
          END - cte.statement_start_offset) / 2
         ) + 1
       )
FROM cte
CROSS APPLY sys.dm_exec_sql_text(cte.[sql_handle]) txt
ORDER BY cte.AvgCPU DESC;

Ajustei a consulta para divisão por 0 erros e otimizei os nomes das colunas para copiar/colar no Excel.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
WITH DB_CPU_Stats
AS
(
    SELECT DatabaseID, isnull(DB_Name(DatabaseID),case DatabaseID when 32767 then 'Internal ResourceDB' else CONVERT(varchar(255),DatabaseID)end) AS [DatabaseName], 
      SUM(total_worker_time) AS [CPU_Time_Ms],
      SUM(total_logical_reads)  AS [Logical_Reads],
      SUM(total_logical_writes)  AS [Logical_Writes],
      SUM(total_logical_reads+total_logical_writes)  AS [Logical_IO],
      SUM(total_physical_reads)  AS [Physical_Reads],
      SUM(total_elapsed_time)  AS [Duration_MicroSec],
      SUM(total_clr_time)  AS [CLR_Time_MicroSec],
      SUM(total_rows)  AS [Rows_Returned],
      SUM(execution_count)  AS [Execution_Count],
      count(*) 'Plan_Count'
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY (
                    SELECT CONVERT(int, value) AS [DatabaseID] 
                  FROM sys.dm_exec_plan_attributes(qs.plan_handle)
                  WHERE attribute = N'dbid') AS F_DB
    GROUP BY DatabaseID
)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [Rank_CPU],
       DatabaseName,
       [CPU_Time_Hr] = convert(decimal(15,2),([CPU_Time_Ms]/1000.0)/3600) ,
        CAST([CPU_Time_Ms] * 1.0 / SUM(case [CPU_Time_Ms] when 0 then 1 else [CPU_Time_Ms] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU_Percent],
       [Duration_Hr] = convert(decimal(15,2),([Duration_MicroSec]/1000000.0)/3600) , 
       CAST([Duration_MicroSec] * 1.0 / SUM(case [Duration_MicroSec] when 0 then 1 else [Duration_MicroSec] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Duration_Percent],    
       [Logical_Reads],
        CAST([Logical_Reads] * 1.0 / SUM(case [Logical_Reads] when 0 then 1 else [Logical_Reads] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical_Reads_Percent],      
       [Rows_Returned],
        CAST([Rows_Returned] * 1.0 / SUM(case [Rows_Returned] when 0 then 1 else [Rows_Returned] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Rows_Returned_Percent],
       [Reads_Per_Row_Returned] = [Logical_Reads]/(case [Rows_Returned] when 0 then 1 else [Rows_Returned] end),
       [Execution_Count],
        CAST([Execution_Count] * 1.0 / SUM(case [Execution_Count]  when 0 then 1 else [Execution_Count] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Execution_Count_Percent],
       [Physical_Reads],
       CAST([Physical_Reads] * 1.0 / SUM(case [Physical_Reads] when 0 then 1 else [Physical_Reads] end ) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Physcal_Reads_Percent], 
       [Logical_Writes],
        CAST([Logical_Writes] * 1.0 / SUM(case [Logical_Writes] when 0 then 1 else [Logical_Writes] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical_Writes_Percent],
       [Logical_IO],
        CAST([Logical_IO] * 1.0 / SUM(case [Logical_IO] when 0 then 1 else [Logical_IO] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical_IO_Percent],
       [CLR_Time_MicroSec],
       CAST([CLR_Time_MicroSec] * 1.0 / SUM(case [CLR_Time_MicroSec] when 0 then 1 else [CLR_Time_MicroSec] end ) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CLR_Time_Percent],
       [CPU_Time_Ms],[CPU_Time_Ms]/1000 [CPU_Time_Sec],
       [Duration_MicroSec],[Duration_MicroSec]/1000000 [Duration_Sec]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY [Rank_CPU] OPTION (RECOMPILE);

Gostei tanto da consulta da CPU sys.dm_exec_query_statsque a estendi. Ele ainda é classificado por CPU, mas adicionei outros totais e porcentagens para obter um perfil de servidor melhor. Isso copia bem no Excel e com formatação condicional de cores nas colunas Porcentagem, os piores números se destacam bem. Usei a 'Graded Color Scale' com 3 cores; uma cor rosa para valores altos, amarelo para médio, verde para baixo.

Eu adicionei um rótulo para database id 32676o qual é o banco de dados de recursos SQL interno. Eu converto o tempo de CPU e Duração em Horas para ter uma melhor noção do uso do tempo.

WITH DB_CPU_Stats
AS
(
    SELECT DatabaseID, isnull(DB_Name(DatabaseID),case DatabaseID when 32767 then 'Internal ResourceDB' else CONVERT(varchar(255),DatabaseID)end) AS [DatabaseName], 
      SUM(total_worker_time) AS [CPU Time Ms],
      SUM(total_logical_reads)  AS [Logical Reads],
      SUM(total_logical_writes)  AS [Logical Writes],
      SUM(total_logical_reads+total_logical_writes)  AS [Logical IO],
      SUM(total_physical_reads)  AS [Physical Reads],
      SUM(total_elapsed_time)  AS [Duration MicroSec],
      SUM(total_clr_time)  AS [CLR Time MicroSec],
      SUM(total_rows)  AS [Rows Returned],
      SUM(execution_count)  AS [Execution Count],
      count(*) 'Plan Count'

    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY (
                    SELECT CONVERT(int, value) AS [DatabaseID] 
                  FROM sys.dm_exec_plan_attributes(qs.plan_handle)
                  WHERE attribute = N'dbid') AS F_DB
    GROUP BY DatabaseID
)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU Time Ms] DESC) AS [Rank CPU],
       DatabaseName,
       [CPU Time Hr] = convert(decimal(15,2),([CPU Time Ms]/1000.0)/3600) ,
        CAST([CPU Time Ms] * 1.0 / SUM([CPU Time Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent],
       [Duration Hr] = convert(decimal(15,2),([Duration MicroSec]/1000000.0)/3600) , 
       CAST([Duration MicroSec] * 1.0 / SUM([Duration MicroSec]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Duration Percent],    
       [Logical Reads],
        CAST([Logical Reads] * 1.0 / SUM([Logical Reads]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical Reads Percent],      
       [Rows Returned],
        CAST([Rows Returned] * 1.0 / SUM([Rows Returned]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Rows Returned Percent],
       [Reads Per Row Returned] = [Logical Reads]/[Rows Returned],
       [Execution Count],
        CAST([Execution Count] * 1.0 / SUM([Execution Count]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Execution Count Percent],
       [Physical Reads],
       CAST([Physical Reads] * 1.0 / SUM([Physical Reads]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Physcal Reads Percent], 
       [Logical Writes],
        CAST([Logical Writes] * 1.0 / SUM([Logical Writes]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical Writes Percent],
       [Logical IO],
        CAST([Logical IO] * 1.0 / SUM([Logical IO]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical IO Percent],
       [CLR Time MicroSec],
       CAST([CLR Time MicroSec] * 1.0 / SUM(case [CLR Time MicroSec] when 0 then 1 else [CLR Time MicroSec] end ) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CLR Time Percent],
       [CPU Time Ms],[CPU Time Ms]/1000 [CPU Time Sec],
       [Duration MicroSec],[Duration MicroSec]/1000000 [Duration Sec]
FROM DB_CPU_Stats
--WHERE DatabaseID > 4 -- system databases
--AND DatabaseID <> 32767 -- ResourceDB
ORDER BY [Rank CPU] OPTION (RECOMPILE);

Descobrimos que usar o Query Store é um excelente ponto de partida na investigação de consumidores de CPU do servidor. Caso o QS esteja habilitado em todos os bancos de dados de usuários, você obterá uma estimativa bastante precisa da distribuição média de CPU entre eles durante algum período de tempo.

Por exemplo, a consulta abaixo retorna informações sobre consumidores de CPU da semana passada:

DECLARE @DatabaseName NVARCHAR(256);
DECLARE @SQL    NVARCHAR(MAX);

CREATE TABLE #Stats (
    DatabaseName    NVARCHAR(256),
    CPUUsage        FLOAT
);

DECLARE myCursor CURSOR READ_ONLY
FOR
SELECT name
FROM sys.databases
WHERE is_query_store_on = 1;

OPEN myCursor;

WHILE 1=1 BEGIN

    FETCH NEXT FROM myCursor
    INTO @DatabaseName;

    IF @@FETCH_STATUS != 0 BREAK;

    SET  @SQL = N'
        USE ' + QUOTENAME(@DatabaseName) +';
        
        INSERT #Stats (DatabaseName, CPUUsage)
        SELECT @DatabaseName,
            SUM(qsrs.count_executions*qsrs.avg_cpu_time)
        FROM [sys].[query_store_runtime_stats] qsrs
            JOIN [sys].[query_store_runtime_stats_interval] qsrsi ON qsrsi.runtime_stats_interval_id = qsrs.runtime_stats_interval_id
        WHERE qsrsi.start_time > DATEADD(DAY, -7, GETDATE());
    '
    EXEC sp_executesql @SQL, N'@DatabaseName NVARCHAR(256)', @DatabaseName;

END

CLOSE myCursor;
DEALLOCATE myCursor;

SELECT ROW_NUMBER() OVER(ORDER BY CPUUsage DESC) AS [CPU Rank],
       DatabaseName, 
       ISNULL(CPUUsage , 0)/1000 AS [CPU Time (ms)], 
       CAST(ISNULL(CPUUsage / SUM(CPUUsage) OVER() * 100.0, 0) AS DECIMAL(5, 2)) AS [CPU Percent]
FROM #Stats
ORDER BY [CPU Rank];

DROP TABLE #Stats;