-2

In Question How to get CPU usage by database for particular instance? Solomon states: The query using sys.sysprocesses is only looking at right now. And the query using sys.dm_exec_query_stats is looking at mostly what has happened since the last restart of the SQL Server service or obviously system reboot.

Microsoft states sysprocesses is deprecated.

What is the correct way to find Current CPU% without Deprecated sysprocesses, I am not interested in looking at individual queries. We just want general level performance metrics for each database in current state. We have Microservices architecture, so No Cross database joins, only self-database queries, no ad-hoc. Each query has InitialDB set correctly, does not utilize masterdb, tempdb.

DECLARE @total INT
SELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK)
    join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid

SELECT sb.name 'database', @total 'system cpu', SUM(cpu) 'database cpu', CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) '%'
FROM sys.sysprocesses sp (NOLOCK)
JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
--WHERE sp.status = 'runnable'
GROUP BY sb.name
ORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) desc
  • 3
    The new DMVs don't necessarily get the right database accurately either. If you have a query that joins tables from three databases, which database does its CPU usage belong to? What if it also involves tempdb or looks up information in master? What exactly do you need this information for? If a database uses too much CPU, are you going to give it a time out? – Aaron Bertrand Jan 23 '18 at 14:02
  • 2
    Brent Ozar explains what @AaronBertrand is referring above. stackoverflow.com/questions/28952/cpu-utilization-by-database – SqlWorldWide Jan 23 '18 at 14:11
  • 2
    Yes, like Brent said, you should be tuning by query, not by database. – Aaron Bertrand Jan 23 '18 at 14:20
  • Hi Aaron, thanks Solomon mentioned: ""if you just want to get a sense of what is happening right now because things are slowing down right now, you are better off using the combination of sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests."", is he incorrect? Goal: want to measure performance cpu% by database. We are thinking to split databases out onto different servers (right now they are all on 1 server), I wanted some kind of baseline measurement, won't be exactly accurate, wanted to measure current CPU% on a every 10 min basis – user129291 Jan 23 '18 at 14:38
2

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_sessions and 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]

USE [msdb];
CREATE TABLE #TempTable ([Col1] INT, [Col2] INT);
INSERT INTO #TempTable ([Col1], [Col2])
   SELECT [object_id], [column_id]
   FROM   [sys].[all_columns];


USE [Database1];
EXEC [Database2].[dbo].[StoredProc];

Assume "Database2.dbo.StoredProc" is defined as follows:

CREATE PROCEDURE dbo.StoredProc
AS
SET NOCOUNT ON;

INSERT INTO [Database8].[dbo].[TableWithInsertTriggerToLogIntoDatabase9](Col1, Col2, Col3)
  SELECT  (tmp.[Col1] * tmp.[Col2]) AS [WhatEvs],
          [Database3].[dbo].NevrFastTsqlUDF(tmp.[Col1])
                                    AS [UnnecessaryEncapsulationOfSimpleFormula],
          [Database4].[dbo].[SQLCLR_UDF_with_external_connection_to_Database7](tvf.[Col3])
                                    AS [Wow]
  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:

  1. your code is completely quarantined into each of the separate DBs and does not do any cross-DB queries.
  2. 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.
  3. 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 tempdb usage)

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)

    and:

  • the combination of sys.dm_exec_connections, sys.dm_exec_sessions, and 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:

    USE [master];
    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 ExecutingTotalElapsedTime and 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.

  • @AppleBook89 I recommend both since "current" does not show nearly all code paths that are affecting the system. Hopefully most of your queries are over in a matter of milliseconds and you won't see them unless you refresh A LOT. Am updating my answer now with a basic query – Solomon Rutzky Jan 23 '18 at 19:31
  • thanks, can you add sys_dm_exec_requests into existing query -- maybe comment it out joins, or show second query, I will place this on bounty, and send the points to you, Thanks for all the help – user129291 Jan 23 '18 at 19:48
  • @AppleBook89 Thanks, and yer welcome. Please see my updates and lemme know if that is what you are looking for. – Solomon Rutzky Jan 23 '18 at 20:15
  • ok, I see, I will add this in , let me know if this should be in answer, SELECT DB_NAME(ser.[database_id]) AS [DatabaseName], SUM(ser.[cpu_time]) AS [TotalCpuTime], SUM(ser.total_elapsed_time) AS [TotalElapsedTime], SUM(ser.logical_reads) AS [TotalLogicalReads] from sys.dm_exec_requests ser GROUP BY ser.[database_id] ORDER BY [TotalCpuTime] DESC; – user129291 Jan 23 '18 at 20:18
  • You do not want to use a query that is only against the requests DMV. those come and go too quickly. Even grabbing from the Sessions will miss Connection Pools that time-out between samples if you take only every 10 minutes. I think the default connection pool timeout is 5 minutes. You could be missing some expensive queries in those gaps. Hence the need to also use the query_stats DMV. – Solomon Rutzky Jan 23 '18 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy