February 17, 2011 at 6:51 am
I'm trying to categorize which databases on my database server are the most utilized for the purpose of splitting them out onto different disk drives. Is there a query against a DMV that could answer this for me?
Thanks in advance,
Rick Wenger.
February 17, 2011 at 7:06 am
See if sys.dm_exec_query_stats DMV helps you.
__________________________
Allzu viel ist ungesund...
February 17, 2011 at 7:17 am
I'd look at the amount of IO per database so try this
--Calculating the Percentage of I/O for Each Database
---------------------------------------------------
WITH Agg_IO_Stats
AS
(
SELECT
DB_NAME(database_id) AS database_name,
CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576.
AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS DM_IO_Stats
GROUP BY database_id
)
SELECT
ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS row_num,
database_name,
io_in_mb,
CAST(io_in_mb / SUM(io_in_mb) OVER() * 100
AS DECIMAL(5, 2)) AS pct
FROM Agg_IO_Stats
ORDER BY row_num;
---------------------------------------------------------------------
February 17, 2011 at 7:24 am
you might want to break that down further to database files so you can see data and log file activity, but anyway DMV sys.dm_io_virtual_file_stats is the one you want, it will show read and writes as well. the values are since the last SQL restart.
---------------------------------------------------------------------
February 17, 2011 at 7:41 am
thanks a million!
February 17, 2011 at 7:45 am
Another approach below.
SELECT SUM(deqs.total_logical_reads) TotalPageReads,
SUM(deqs.total_logical_writes) TotalPageWrites,
CASE
WHEN DB_NAME(dest.dbid) IS NULL THEN 'AdhocSQL'
ELSE DB_NAME(dest.dbid) END Databasename
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
GROUP BY DB_NAME(dest.dbid)
__________________________
Allzu viel ist ungesund...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply