I have a database server with a couple of databases on it. How can I see where any resource pressure may come from?
I would like to get a table of:
- Database Name
- Batch Requests per second
- CPU Time
- Logical Read
- Logical Writes
Only some resources can be tracked to databases. IO and buffer pool usage can clearly be tracked to a database:
The query execution statistics (like logical reads) can be attributed to queries, but cannot be associated (correctly) with a database because queries can (and often do) span databases.
sys.dm_exec_query_stats is the main entry point for these stats.
I’ve cobbled together this query from a few other queries. It’s not exactly what you’re looking for but can give you an idea of what sort of resource consumption you may be looking at.
DECLARE @total_buffer INT; SELECT @total_buffer = cntr_value FROM sys.dm_os_performance_counters WHERE RTRIM([object_name]) LIKE '%Buffer Manager' AND (counter_name = 'Total Pages' --this is 2008 OR counter_name = 'Database pages'); -- this is 2012 ;WITH src AS ( SELECT database_id, DB_Buffer_Pages = COUNT_BIG(*) FROM sys.dm_os_buffer_descriptors --WHERE database_id BETWEEN 5 AND 32766 GROUP BY database_id ) SELECT database_name = DB_NAME(mem.database_id), db_buffer_pages, cached_size_mb, db_buffer_pct, cpu_time, cpu_percent_usage, total_io_mb, io_percent FROM ( --DatabaseName = CASE [database_id] WHEN 32767 -- THEN 'Resource DB' -- ELSE DB_NAME([database_id]) END, SELECT database_id, db_buffer_pages = DB_Buffer_Pages, cached_size_mb = DB_Buffer_Pages / 128.0, db_buffer_pct = CONVERT(DECIMAL(6,3), DB_Buffer_Pages * 100.0 / @total_buffer) FROM src ) mem JOIN ( SELECT database_id, cpu_time, CAST(cpu_time * 1.0 / SUM(cpu_time) OVER() * 100.0 AS DECIMAL(5, 2)) AS cpu_percent_usage FROM ( SELECT database_id, SUM(total_worker_time) AS cpu_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY (SELECT CONVERT(int, value) AS database_id FROM sys.dm_exec_plan_attributes(qs.plan_handle) WHERE attribute = N'dbid') AS F_DB GROUP BY database_id) DB_CPU_Stats ) cpu ON mem.database_id = cpu.database_id JOIN ( SELECT database_id, io_in_mb AS [total_io_mb], CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [io_percent] FROM ( SELECT database_id, 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) Aggregate_IO_Statistics) [io] ON mem.database_id = [io].database_id
Here’s something to get you information on reads and writes (cobbled from a few other queries):
CREATE TABLE #results ( DatabaseName SYSNAME , VLFcount INT ); -- Create the SQL Server 2012-compatible table. We'll drop the column if it's not 2012 CREATE TABLE #stage ( RecoveryUnitId INT , FileID INT , FileSize BIGINT , StartOffset BIGINT , FSeqNo BIGINT , [Status] BIGINT , Parity BIGINT , CreateLSN NUMERIC(38) ); BEGIN TRY INSERT INTO #stage EXECUTE sp_executesql N'DBCC LOGINFO()'; -- if it worked then we don't want this data twice TRUNCATE TABLE #stage; END TRY BEGIN CATCH --All versions between 2000 and 2008R2 do not have RecoveryUnitId ALTER TABLE #stage DROP COLUMN RecoveryUnitId; END CATCH EXECUTE sp_msforeachdb N'Use [?]; INSERT INTO #stage EXECUTE sp_executesql N''DBCC LOGINFO([?])''; INSERT INTO #results SELECT DB_Name(), COUNT(*) FROM #stage; TRUNCATE TABLE #stage;' SELECT collection_date = SYSDATETIME(),--COALESCE(@CollectionDate, GETDATE()), file_type = CASE vfs.file_id WHEN 2 THEN 'Log' ELSE 'Data' END, database_name = DB_NAME(vfs.database_id), mf.physical_name, io_stall_read_ms, num_of_reads, avg_read_stall_ms = CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)), pct_read_stall = CAST(100. * vfs.io_stall_read_ms/(vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS DECIMAL(10,1)), pct_num_reads = CAST(100. * vfs.num_of_reads/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)), pct_read_bytes = CAST(100. * vfs.num_of_bytes_read/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)), io_stall_write_ms, num_of_writes, avg_write_stall_ms = CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)), pct_write_stall = CAST(100. * vfs.io_stall_write_ms/(vfs.io_stall_write_ms + vfs.io_stall_read_ms) AS DECIMAL(10,1)), pct_num_writes = CAST(100. * vfs.num_of_writes/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)), pct_write_bytes = CAST(100. * vfs.num_of_bytes_written/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)), io_stalls = io_stall_read_ms + io_stall_write_ms, total_io = num_of_reads + num_of_writes, avg_io_stall_ms = CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)), io_stall, size_on_disk_bytes, vlf = VLFcount FROM sys.dm_io_virtual_file_stats(null,null) AS vfs INNER JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.[file_id] = mf.[file_id] INNER JOIN #results vlf ON DB_NAME(vfs.database_id) = vlf.DatabaseName; drop table #stage; drop table #results;
Please note that the same caveats apply here as in Remus’ answer. Additionally, much of this information is useless as one-offs and better suited for evaluation over time.
Short answer: No. SQL Server does not track these statistics on a database level. Which logically also makes sense, since each batch request can (and often will, if you count the system dbs) span multiple databases.
You can see which performance counters are available on a database level with this query:
select * from sys.dm_os_performance_counters where object_name in ('SQLServer:Databases', 'SQLServer:Catalog Metadata')
What could help you though is to do a query based analysis like this:
select DB_NAME(st.dbid) As DbName, COUNT(1) As QueriesInCache, SUM(qs.total_worker_time) TotalWorkerTime, SUM(qs.total_logical_reads) TotalLogicalReads, SUM(qs.total_logical_writes) TotalLogicalWrites, SUM(qs.execution_count) As ExecCount from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle) st where st.dbid is not null group by st.dbid
However, this gives you only a cumulated view for all queries in currently in the cache, so you have to do extra analysis to figure out changes over time, and draw your conclusions from that.
Edit: Using SQL Profiler you would have to track every single request, and then aggregate that from time to time. That would put a substantial additional load onto the server resources so I wouldn’t consider this a viable solution for this question.