How to monitor a server and a particular database

Corobori

Hi, I have just installed SQL Monitor 7 on my server and I am trying to find my way around. Searching the online documentation didn't return the answers I am looking for. Basically 2 questions: the 1st one is what are the databases on my server using the most CPU and memory and the 2nd one is what are the queries, views using the most resources and if possible how to fix the underlying issues.

Thanks.

JL

Grant

1) There's not really a ranking by database of CPU usage. In fact, the way SQL Server works, it's going to be difficult to identify that for certain because any given query could be running from one database while querying another. Which database do you put the CPU use into? You can look at transactions and other database specific metrics. Some are on the overview page and a whole bunch more are available in the Analysis page.
2) That's front and center on the Overview page. Scroll down from the sparkline one the server and you'll see a list of the current top 10 queries. You can resort that list by any of the provided columns. You can also go back in time to look at previous periods. That should show you what you're looking for. Further, clicking on the query will show you the waits for that query and you can look at the execution plan. Between the two, you get a lot of guidance on what to do to address the slow queries or the queries consuming lots of resources.

Corobori

Hi Grant,

I ran on my SQL Server the following statement

WITH CPU_Per_Db
    AS
    (SELECT 
     dmpa.DatabaseID
     , DB_Name(dmpa.DatabaseID) AS [Database]
     , SUM(dmqs.total_worker_time) AS CPUTimeAsMS
     FROM sys.dm_exec_query_stats dmqs 
     CROSS APPLY 
     (SELECT 
     CONVERT(INT, value) AS [DatabaseID] 
     FROM sys.dm_exec_plan_attributes(dmqs.plan_handle)
     WHERE attribute = N'dbid') dmpa
     GROUP BY dmpa.DatabaseID)
     
     SELECT 
     [Database] 
     ,[CPUTimeAsMS] 
     ,CAST([CPUTimeAsMS] * 1.0 / SUM([CPUTimeAsMS]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUTimeAs%]
     FROM CPU_Per_Db
     ORDER BY [CPUTimeAs%] DESC;

And it gave me the results shown below. I am not expert but it looked as some sort of ranking. So my actual issue is nailing down what's up with the database appearing at #1 here. Or is the information shown here irrelevant ?

Grant

So that's cumulative query time. That database may in fact be the problem. You still don't know that 100% because the query is assigned to the database that calls it, but that query could be calling other databases. It's just this odd point that makes a perfect, "Well, yes, of course that particular database is the one using your CPU resources" difficult to say with certainty. It depends on the queries.

That said, the queries from that database are absolutely the top cost of CPU on your system. We can say that with 100% certainty.

Does that help?