Post reply

CPU Usage by Database

  • May 1, 2014 at 9:45 pm

    #285194

    Comments posted to this topic are about the item CPU Usage by Database

  • July 8, 2014 at 6:57 am

    #1727752

    What SQL edition the script applies to? Thanks.

  • July 8, 2014 at 7:23 am

    #1727758

    Very nice script. Is there a way to break down the script for date ranges (ie last week, this month, this year)?

    Thanks

  • July 9, 2014 at 12:50 am

    #1727945

    Very nice script. Is there a way to break down the script for date ranges (ie last week, this month, this year)?

    Thanks

    It does not make sense, because the CPU usage calculation is to get the Analysis of the Usage at certain period where there is a bottle neck was/is happened, thus I dont think you would require such a query.

    Regards,

    Prabhu

  • July 9, 2014 at 7:27 am

    #1728036

    So this the script is returning a snapshot at the time the script is executed. Is this correct?

  • July 9, 2014 at 10:59 am

    #1728153

    Yes it is. You can put it in a job that runs periodically and populates a table which you can use for analysis.

  • July 9, 2014 at 12:24 pm

    #1728188

    Great! Thanks

  • July 9, 2014 at 12:36 pm

    #1728191

    Just ran the second query on a server and it surprised me that a non-used db consumes 93.29% CPU time!

    Does it calculate CPU time based on the whole server life history?

    I further checked total_worker_time in sys.dm_exec_query_stats by executing

    Select * FROM sys.dm_exec_query_stats

    It returns me 2200 rows and all the rows with creation_time and last_execution_time in 2014, and the db was abandoned in 2014, how does it come up with the 93.29% CPU time?

    Can anyone explain? Thanks.

  • July 10, 2014 at 3:03 am

    #1728301

    @halifaxdal

    May I know from which DB have you executed this statement, because if its got executed from the system DBs then propably you could get such number of results and If I am not wrong, I don't think you can execute this query from the DB which was shutdown in status.

    may be you could have executed that from the master DB.

    Thanks,

    Prabhu

  • July 10, 2014 at 4:47 am

    #1728313

    prabhu.st (7/10/2014)

    @halifaxdal

    May I know from which DB have you executed this statement, because if its got executed from the system DBs then propably you could get such number of results and If I am not wrong, I don't think you can execute this query from the DB which was shutdown in status.

    may be you could have executed that from the master DB.

    Thanks,

    Prabhu

    master is where I execute this script

  • July 10, 2014 at 7:29 am

    #1728373

    prabhu.st (7/10/2014)

    @halifaxdal

    May I know from which DB have you executed this statement, because if its got executed from the system DBs then propably you could get such number of results and If I am not wrong, I don't think you can execute this query from the DB which was shutdown in status.

    may be you could have executed that from the master DB.

    Thanks,

    Prabhu

    It actually doesn't matter which DB I ran the script, result is the same

  • July 11, 2014 at 4:07 am

    #1728660

    No.. the results would be different.. am sure.. (refer the screen shots attached)

    may be the CPU usage is not because of only the SQL Query, also some other network related issue would be there.

    refer to the screen shots, but the number of rows returned in the result would have only about the active DB details.

    Thanks,

    Prabhu

  • September 21, 2015 at 12:59 pm

    #1828194

    Thanks for the script.

  • September 21, 2015 at 8:24 pm

    #1828264

    @Donnie

    Depends upon what you mean by "snapshot". Dm_exec_query_stats shows the metrics accumulated for a cached plan. If a plan was just cached and it is being run for the first time, nothing will be reported. If a plan was cached and one execution of it has completed with low cpu usage, if a secon execution of the same plan is currently running but with high cpu usage, then an inspection of dm_exec_query_stats will show low CPUs usage, even though the currently running plan has high court usage.

  • September 22, 2015 at 7:58 am

    #1828413

    Really helpful script. Thanks!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply