Database growth

Question

Post reply

Database growth

mtz676

SSCrazy Eights

Points: 9212
More actions
May 11, 2023 at 2:05 pm

#4188037

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

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

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

frederico_fonseca SSCoach Points: 16155 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

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)