What are the challenges you face when working across database platforms? Take the survey
Options

How to monitor a server and a particular database

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
Tagged:

Answers

  • Options
    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.
  • Options
    CoroboriCorobori Posts: 2 Bronze 1
    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 ?

    szkg0o1qqzwb.jpg
  • Options
    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?
Sign In or Register to comment.