If I wanted to get a better sense of what is happening right now, what is the current measure query using "the combination of sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests." to measure current CPU% by Database?
As has been stated before, there is no way to get truly accurate per-Database CPU % because this is not how SQL Server works. The
database_id column of
sys.dm_exec_requests return the ID of the "active" Database. The Database that is "active" is the one that reflects the most recent
USE statement for ad hoc queries / Dynamic SQL, OR the database that the Stored Procedure exists in if a Stored Proc is being executed (and I assume Triggers fall into this category as well since they are essentially event-based Stored Procedures). And, if one Stored Procedure executes another in another DB, the "active" DB is the one that reflects where the currently executing Stored Procedure exists (i.e. inner-most level when dealing with nested Stored Procs).
So, consider the following:
-- log into [master]
CREATE TABLE #TempTable ([Col1] INT, [Col2] INT);
INSERT INTO #TempTable ([Col1], [Col2])
SELECT [object_id], [column_id]
Assume "Database2.dbo.StoredProc" is defined as follows:
CREATE PROCEDURE dbo.StoredProc
SET NOCOUNT ON;
INSERT INTO [Database8].[dbo].[TableWithInsertTriggerToLogIntoDatabase9](Col1, Col2, Col3)
SELECT (tmp.[Col1] * tmp.[Col2]) AS [WhatEvs],
FROM #TempTable tmp
LEFT JOIN [Database5].[dbo].[LargeTable] lt
ON lt.[Col1] = tmp.[Col1]
OUTER APPLY [Database6].[dbo].[TsqlTVF](lt.[Col2]) tvf
WHERE tmp.[Col1] > 0;
That is a highly convoluted example, but it is designed to show that there are several areas that can introduce performance degradation. Which DB will be reflected in the DMVs? Most likely "Database2" as that is where the Stored Procedure exists, even though none of the tables or functions that it queries from / uses exist there. But, if you were to execute that same
INSERT...SELECT as an ad hoc query in "Database1" (rather than executing the Stored Procedure), then it would be "Database1" that would be reflected in the DMVs.
To be fair, you might could get a somewhat reasonable big picture view of per-DB CPU ONLY IF:
- your code is completely quarantined into each of the separate DBs and does not do any cross-DB queries.
- The logins are either directly into the DB where the code exists, OR executes Stored Procedure, OR at least issues a
USE statement prior to executing any ad hoc queries / Dynamic SQL.
- The code either does not use tempdb / temp tables / table variables / snapshot isolation OR they all use those things roughly to the same degree (the idea is to cancel out the
we have microservices architecture, no cross database joins, each stored procedure query has initialdb set correctly, no adhoc queries, we just wanted baseline measurements of each database cpu%, without deprecated sys.sysprocesses
In that case, you can probably get away with both approaches:
sys.dm_exec_query_stats (as shown in the linked answer in the question -- just need to add the
database_id column in there)
the combination of
sys.dm_exec_requests. Join all of them on
session_id. BUT, you might not need
sys.dm_exec_connections so start with just the other two:
SELECT DB_NAME(sess.[database_id]) AS [DatabaseName],
SUM(sess.[cpu_time]) AS [CompletedTotalCpuTime],
SUM(sess.total_elapsed_time) AS [CompletedTotalElapsedTime],
SUM(sess.logical_reads) AS [CompletedTotalLogicalReads],
'---' AS [---],
SUM(req.[cpu_time]) AS [ExecutingTotalCpuTime],
SUM(req.[total_elapsed_time]) AS [ExecutingTotalElapsedTime],
SUM(req.[logical_reads]) AS [ExecutingTotalLogicalReads],
SUM(req.[wait_time]) AS [ExecutingTotalWaitTime]
FROM sys.dm_exec_sessions sess
LEFT JOIN sys.dm_exec_requests req
ON req.[session_id] = sess.[session_id]
WHERE sess.is_user_process = 1
AND sess.[program_name] NOT LIKE N'Microsoft SQL Server Management Studio%'
GROUP BY sess.[database_id]
ORDER BY [CompletedTotalCpuTime] DESC;
PLEASE NOTE: this shows stats for Sessions that exist. If your app uses Connection Pooling then the Sessions will stick around a bit and you will get more useful info (in the "Completed" columns). If you do not use Connection Pooling, this will be of limited usefulness as the Sessions will disappear before you can get useful metrics. The issue is that the current running process is in the
requests DMV and the
sessions DMV contains the info for requests that have completed. If that is the case, then adding in
sys.dm_exec_requests would help as that will show the true "current" activity, but again, with queries having durations in the (hopefully) sub-second range, this won't show the kind of trend that you need to make your decision, which is why you need this and the
dm_exec_query_stats DMV query.
ALSO, my testing seems to indicate that while the "Current" columns for
ExecutingTotalLogicalReads do roll-up into the Session "total" columns of the same metric as one would expect, the
TotalCpuTime for the Request does not. Meaning, if a Request shows a CPU time of 300 and then ends (and goes to
NULL) but the Session is still there to see, do not expect that the updated
CompletedTotalCpuTime column for the Session to be 300 more than it was before that Request completed. Sometimes it only goes up a little.