Is there a way via T-SQL and DMVs to show which database a query was run against?

The goal is to look at CPU and I/O by query by database so I can redistribute the databases as I reconfigure the environment.

I do not want to use trace, profiler or extended events if I can help it. I also do not want to shred XML if I can avoid it. I am looking for history not just active.

  • You can use DMV sys.dm_exec_query_stats and cross apply with sys.dm_exec_sql_text
    – Shanky
    Commented Aug 25, 2015 at 13:26
  • 2
    Regarding your opening question, to which database would you assign a query that accesses objects in multiple databases?
    – Paul White
    Commented Aug 25, 2015 at 13:57
  • Paul in this case the databases are segregated by customer so there is no cross application. However, the same query text could easily apply to multiple databases. Sorry for not getting back to this sooner.
    – pshore73
    Commented Sep 10, 2015 at 12:39

1 Answer 1


You have to concentrate on Memory, Disk IO and CPU utilization to balance out your environment.

Best is to start collecting and dumping the results in a utility database and to give you a good baseline using sql agent on a scheduled basis.

Alternatively, you can use Performance data collector (applies to sql server 2008 and up)


You can look into buffer pool usage (what databases are occupying more space in buffer pool ?)

From :Performance issues from wasted buffer pool memory

    (CASE WHEN ([database_id] = 32767)
        THEN N'Resource Database'
        ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
    COUNT (*) * 8 / 1024 AS [MBUsed],
    SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id];


In terms of Disk IO, you need to move away the mdf/ldf files having the most disk IO to a better/faster LUN/Array :

From : How to examine IO subsystem latencies from within SQL Server

    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    [Latency] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
    [AvgBPerRead] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
    [AvgBPerWrite] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
    [AvgBPerTransfer] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) /
                ([num_of_reads] + [num_of_writes])) END,
    LEFT ([mf].[physical_name], 2) AS [Drive],
    DB_NAME ([vfs].[database_id]) AS [DB],
    sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
    ON [vfs].[database_id] = [mf].[database_id]
    AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;

Also refer to Leveraging sys.dm_io_virtual_file_stats


From : Brent's answer

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 

A gold mine of DMV related queries : SQL Server Diagnostic Information Queries for September 2014

For completeness, if you are using sql server 2012, you can use the system health reporting dashboard for Visualizing sp_server_diagnostics results.

  • Kin that is some awesome stuff. Thank you.
    – pshore73
    Commented Sep 10, 2015 at 12:40
  • I am accepting the answer even though it did not give what I asked for it gave what I needed.
    – pshore73
    Commented Sep 10, 2015 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.