Post reply

CPU usage of database for time spans in SQL Server 2019

  • April 16, 2022 at 6:13 am

    #4017060

    I want to track how much CPU usage (in ms, not %) was consumed for a database in a given time window.  I know how to do this with the Query Store, but I think it stores a lot more than I need, and I don't want to lose data due to seldom-used query plans getting discarded from the store.  Ideally, I'd like to store 6 numbers per hour - the number of milliseconds of CPU used by that database for each 10-minute period of that hour.

  • April 16, 2022 at 6:03 pm

    #4017307

    Please don't mistake my answer as being "snarky"... I'm trying to help you help yourself in the future. 🙂

    When you have a question like this, you have to ask yourself "Would I be the first person in the world that asks this question or might someone have already done it" and ask the question on "Yabingooducklehoo" and immediately get a multitude of answers to learn from.

    Here's an example with Google.  Check out the articles that it returns.

    https://www.google.com/search?q=which+database+is+using+the+most+CPU+in+SQL+Server

    Now, a bigger question is, does that information actually tell you anything important other that what your busiest database actually is?  And, what are you going to do with that information once you get it?

    Unless you're planning to stand up another server and move your busiest database there, I don't see much use for the information.  There are a whole lot of methods (some are even built in as reports you can run from the Object Explorer window) that will help you find your most resource intensive queries and even which part of a stored procedure is the part to "concentrate" on regardless of database.

    You can find the pre-built internal reports by right-clicking on the instance in the Object Explorer (press the {f8} key to open it if it's not already open) and then follow your nose in the popups that appear.  The reports that start with "Performance - Top Queries by..." are the most useful for what I think you may be looking for (although I could certainly be wrong there).  Remember also that you busiest database may not actually have your worst queries so far as CPU goes. 😉

    And, yes... as with any canned set of reports for any system, some of those reports are mostly useless to most people.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

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