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.
Best Answer
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)
MEMORY:
You can look into buffer pool usage (what databases are occupying more space in buffer pool ?)
From :Performance issues from wasted buffer pool memory
DISK IO:
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
Also refer to Leveraging sys.dm_io_virtual_file_stats
CPU:
From : Brent's answer
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.