Database growth

  • What is the best way to capture sql database growth on a weekly basis and report it ?Thanks

    1. table on a dba database to store the database details of the server (or even all your servers).
    2. weekly sql job to retrieve the details of databases and add them to the table above.
  • mtz676 wrote:

    What is the best way to capture sql database growth on a weekly basis and report it ?Thanks

    The "best" way is to anticipate the next question that usually comes up...

    "What are the largest objects in that database and how much are those growing"?

    And, of course, the question after that would be...

    "How much space is being wasted in those objects"?

    So, very much like Frederico suggests, you need to start keeping track of stuff in a "DBA" database.  What you might want to use as a source of information is a run of sys.dm_db_index_physical_stats() using the "Sampled" method, which gives you all the detail columns you're going to need but only samples 1 out of every hundred pages for all must minute tables, which makes it quite a bit faster than using the "Detailed" method.  You could also reduce the size of what's being stored by saving only those rows with an Index_Level = 0.  That's for RowStore stuff.  I've not worked with ColumnStore so you'd have to read up on that.

    That'll give you, among other things, the page count (pagecount/128 = MegaBytes) and average page density percents for each object and their indexes (or heap).  If you also save the sample data and database, you can answer just about all the questions about space usage.

    There are other methods that will get you the space allocated more quickly but none of those will also give you any hints on page densities.

    At a database level, you also need to check on "Allocated but unused" space.  That comes from the mistake that MS built in for bulk operations (called "Fast Inserts") where the system doesn't even check for available pages during such operations.  Instead, it just up and allocates a full extent.

    The problem there is that a lot of front-end code uses "insert bulk" (not to be confused with BULK INSERT) to insert even one row at a time.  Guess how much it takes to store just one row when all of that comes together?  1 full extent (64KB).  Then next row won't use that extent because of what I said previously.

    For more on that fun little "feature", please search for Trace Flag "692" in the article at the following URL...

    https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql

    I heard a rumor that that functionality is now control-able at the database level but I haven't checked on that rumor because we're currently still using SQL Server 2016.  This "feature" was a part of the great "It just runs faster" advertising campaign for 2016, although it wasn't very well advertised.  As a result, I have the Trace Flag enabled to defeat the feature in the SQL Server Startup.

     

    --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)

  • Thanks Jeff and All for the detailed response

  • We use extended events to capture that data.

    ref: https://www.brentozar.com/archive/2015/12/tracking-tempdb-growth-using-extended-events/

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 5 posts - 1 through 4 (of 4 total)

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