Requêtes utiles SQL Server

Ici, vous trouverez un melting pots de requêtes SQL permettant le diagnostics et l’analyse de différents cas de figure que nous rencontrons tous quotidiennement. Je stocke ici celles dont je me sers régulièrement (ou pas !).

Rechercher les requetes prenant le plus de CPU (Ref) :

SELECT TOP 50 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid),
    TextData = qt.text,
    DiskReads = qs.total_physical_reads,
    MemoryReads = qs.total_logical_reads,
    Executions = qs.execution_count,
    TotalCPUTime = qs.total_worker_time,
    AverageCPUTime = qs.total_worker_time/qs.execution_count,
    DiskWaitAndCPUTime = qs.total_elapsed_time,
    MemoryWrites = qs.max_logical_writes,
    DateCached = qs.creation_time,
    DatabaseName = DB_Name(qt.dbid),
    LastExecutionTime = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time DESC

Rechercher les requêtes ayant consommées le plus de CPU en moyenne :

SELECT TOP 50
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid)
  ,TextData = qt.text
  ,DiskReads = qs.total_physical_reads — The worst reads, disk reads
  ,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads
  ,Executions = qs.execution_count
  ,TotalCPUTime = qs.total_worker_time
  ,AverageCPUTime = qs.total_worker_time/qs.execution_count
  ,DiskWaitAndCPUTime = qs.total_elapsed_time
  ,MemoryWrites = qs.max_logical_writes
  ,DateCached = qs.creation_time
  ,DatabaseName = DB_Name(qt.dbid)
  ,LastExecutionTime = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
   CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time/qs.execution_count DESC

Rechercher les requêtes avec un temps d’exécution long :

SELECT TOP 10
ObjectName = OBJECT_NAME(qt.objectid)
   ,DiskReads = qs.total_physical_reads — The worst reads, disk reads
   ,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads
   ,Executions = qs.execution_count
   ,AvgDuration = qs.total_elapsed_time / qs.execution_count
   ,CPUTime = qs.total_worker_time
   ,DiskWaitAndCPUTime = qs.total_elapsed_time
   ,MemoryWrites = qs.max_logical_writes
   ,DateCached = qs.creation_time
   ,DatabaseName = DB_Name(qt.dbid)
FROM sys.dm_exec_query_stats AS qs
   CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id()
ORDER BY qs.total_elapsed_time DESC