sql-server - valores - tuning base de datos sql server

Utilización de CPU por base de datos?

Question

¿Es posible obtener un desglose de la utilización de la CPU por base de datos ?

Idealmente estoy buscando una interfaz de tipo Task Manager para SQL Server, pero en lugar de ver la utilización de la CPU de cada PID (como taskmgr ) o cada SPID (como spwho2k5 ), quiero ver la utilización total de la CPU de cada base de datos. Suponga una sola instancia de SQL.

Me doy cuenta de que se podrían escribir herramientas para recopilar estos datos e informar sobre ellos, pero me pregunto si hay alguna herramienta que me permita ver en vivo qué bases de datos están contribuyendo más a la carga de la CPU sqlservr.exe .

Answer 1

SQL Server (a partir de 2000) instalará contadores de rendimiento (visibles desde Performance Monitor o Perfmon).

Una de las categorías de contador (desde una instalación de SQL Server 2005 es :) - SQLServer: Bases de datos

Con una instancia para cada base de datos. Sin embargo, los contadores disponibles no proporcionan un contador de% de utilización de CPU o algo similar, aunque hay algunos contadores de tasas que puede utilizar para obtener una buena estimación de la CPU. Ejemplo sería, si tiene 2 bases de datos, y la tasa medida es de 20 transacciones / seg en la base de datos A y 80 trans / seg en la base de datos B --- entonces sabría que A contribuye aproximadamente al 20% de la CPU total, y B contribuye a otro 80%.

Aquí hay algunos defectos, ya que eso supone que todo el trabajo que se está haciendo está vinculado a la CPU, lo que por supuesto no es con las bases de datos. Pero eso sería un comienzo, creo.

Answer 2

¿Has mirado el generador de perfiles SQL?

Tome la plantilla estándar "T-SQL" o "Procedimiento almacenado", modifique los campos para agrupar por la ID de la base de datos (creo que debe usar el número, no obtiene el nombre de la base de datos, pero es fácil de averiguar usando exec sp_databases para obtener la lista)

Ejecute esto por un tiempo y obtendrá los recuentos totales de CPU / Disk IO / Wait, etc. Esto le puede dar la proporción de CPU utilizada por cada base de datos.

Si supervisa el contador de PerfMon al mismo tiempo (registra los datos en una base de datos SQL) y hace lo mismo con el Analizador de SQL (log to database), puede correlacionar los dos juntos.

Aun así, debería darle suficiente pista sobre qué DB vale la pena examinar con más detalle. Luego, haga lo mismo otra vez con solo esa ID de base de datos y busque los procedimientos SQL / almacenados más caros.

Answer 3

Creo que la respuesta a tu pregunta es no.

El problema es que una actividad en una máquina puede causar carga en múltiples bases de datos. Si tengo un proceso que está leyendo desde un DB de configuración, iniciando sesión en un DB de registro y moviendo transacciones dentro y fuera de varios DB basados ​​en el tipo, ¿cómo hago una partición del uso de la CPU?

Podría dividir la utilización de la CPU por la carga de la transacción, pero de nuevo es una medida aproximada que puede engañarlo. ¿Cómo dividirías el envío de registros de transacciones de una base de datos a otra, por ejemplo? ¿Se carga la CPU en la lectura o la escritura?

Es mejor que observe la tasa de transacción de una máquina y la carga de CPU que causa. También podría perfilar los procedimientos almacenados y ver si alguno de ellos toma una cantidad excesiva de tiempo; sin embargo, esto no le dará la respuesta que desea.

Answer 4

Eche un vistazo a SQL Sentry . Hace todo lo que necesita y más.

Saludos, Lieven

Answer 5

Aquí hay una consulta que mostrará la base de datos real causando alta carga. Se basa en el caché de consultas que se puede eliminar frecuentemente en escenarios de baja memoria (haciendo que la consulta sea menos útil).

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

Answer 6

Más o menos Verifique esta consulta:

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

Esto obtendrá las consultas en la memoria caché del plan en orden de la cantidad de CPU que han utilizado. Puede ejecutar esto periódicamente, como en un trabajo del Agente SQL, e insertar los resultados en una tabla para asegurarse de que los datos persisten más allá del reinicio.

Cuando lea los resultados, probablemente se dará cuenta de por qué no podemos correlacionar esos datos directamente con una base de datos individual. En primer lugar, una sola consulta también puede ocultar su verdadero padre de base de datos haciendo trucos como este:

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

La consulta se ejecutará en MSDB, pero sondeará los resultados de AdventureWorks. ¿Dónde deberíamos asignar el consumo de CPU?

Se pone peor cuando:

• Únete entre múltiples bases de datos
• Ejecute una transacción en múltiples bases de datos, y el esfuerzo de bloqueo abarca múltiples bases de datos
• Ejecute trabajos de SQL Agent en MSDB que "funcionen" en MSDB, pero haga una copia de seguridad de bases de datos individuales

Lo sigue y sigue. Es por eso que tiene sentido para la sintonía de rendimiento en el nivel de consulta en lugar del nivel de la base de datos.

En SQL Server 2008R2, Microsoft introdujo características de administración del rendimiento y administración de aplicaciones que nos permitirán empacar una única base de datos en un paquete DAC distribuible e implementable, y son características prometedoras para facilitar el manejo del rendimiento de las bases de datos individuales y sus aplicaciones. Sin embargo, todavía no hace lo que estás buscando.

Para obtener más información, consulte el repositorio de T-SQL en la wiki de SQL Server de Toad World (anteriormente en SQLServerPedia) .

Actualizado el 1/29 para incluir números totales en lugar de solo promedios.

Answer 7

por favor revise esta consulta:

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

Answer 8

Con todo lo dicho anteriormente en mente.
Comenzando con SQL Server 2012 (¿puede ser 2008?), Hay una columna database_id en sys.dm_exec_sessions .
Nos da un cálculo sencillo de la CPU para cada base de datos para las sesiones actualmente conectadas . Si la sesión se desconectó, sus resultados desaparecieron.

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;