3

I'm no DBA and barely know a few things about SQL, I have found a lot of scripts online that get most expensive queries, but I can't seem to find one that would focus on just one particular database. For instance: 

SELECT TOP 100 
    SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
    ((CASE qs.statement_end_offset
         WHEN -1 THEN DATALENGTH(qt.TEXT)
         ELSE qs.statement_end_offset
      END - qs.statement_start_offset)/2)+1) as query,
    qs.execution_count,
    qs.total_logical_reads, qs.last_logical_reads,
    qs.total_logical_writes, qs.last_logical_writes,
    qs.total_worker_time, qs.last_worker_time,
    qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
    qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
    qs.last_execution_time,
    qp.query_plan
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY 
    sys.dm_exec_query_plan(qs.plan_handle) qp
-- ORDER BY 
--    qs.total_logical_reads DESC -- logical reads
-- ORDER BY 
--     qs.total_logical_writes DESC -- logical writes
ORDER BY 
    qs.total_worker_time DESC -- CPU time

This script give a lot of details about all operations across all databases, how can I specify one particular database?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Varda Elentári
  • 2,242
  • 6
  • 35
  • 55

1 Answers1

3

Filter by:

where DB_NAME(qt.dbid) = 'MyDBNAme'
uzi
  • 4,118
  • 1
  • 15
  • 22
  • 1
    You shouldn't wrap columns in function calls. It is better to use `where qt.dbid = db_id('MyDBNAme')` – Martin Smith Jan 07 '18 at 15:46
  • This worked for only one of my instance, for the others, db_id was always NULL. Any idea why that might be? – Varda Elentári Jan 08 '18 at 18:18
  • 1
    dbid is not null only for stored procedures. For information please look https://connect.microsoft.com/SQLServer/feedback/details/374600/sys-dm-exec-query-stats-dbid-column-null-for-dynamic-sql or https://stackoverflow.com/questions/20172267/dmf-sys-dm-exec-sql-text-not-showing-dbid – uzi Jan 09 '18 at 02:48