Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2005
    Posts
    75

    Unanswered: calculate average growth rate

    I've got a statistics table that I've been writing to for about 2 years now. Every saturday night, a size (in MB) snapshot of each DB file is taken and dumped into this table. I'm then emailed a copy for that week.

    Now, I'm trying to figure out what the fastest growers are. Here's the table ddl
    Code:
    CREATE TABLE [dbo].[DBSizeStats] (
    	[statid] [int] IDENTITY (1, 1) NOT NULL ,
    	[LogDate] [datetime] NULL ,
    	[Server] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[DBName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MDFName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MDFSize] [decimal](18, 0) NULL ,
    	[LDFName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[LDFSize] [decimal](18, 0) NULL ,
    	[TotalSize] [decimal](18, 0) NULL 
    ) ON [PRIMARY]
    GO
    What I'm trying to figure out is how to query the average monthly and yearly growth percentages per DB on the MDFSize column.

    I'm usually pretty good at this sort of thing, but I just can't seem to wrap my head around how to solve this issue. I'm not having a very good math day.

    what am I missing here?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How do you define "Average growth"?
    This will tell you the average MDFSize for each year period; a good place to start methinks
    Code:
    SELECT Avg(MDFSize)
         , DBName
         , Year(LogDate)
    FROM   DBSizeStats
    GROUP
        BY DBName
         , Year(LogDate)
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Average growth is simply the starting size subtracted from the ending size, and divided by the number of units (months, I assume).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    There is a simpler solution, and it does not require you to collect the info on a reqular basis. Of course, the requirement is for you to backup your databases on a regular basis. If you do, - the info is already collected for you, and your answer is right there, in msdb. Look into backup_set and backupfile tables.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, but isn't data cleared from those tables according to the settings in the maintenance plan? Which is good, because if they grow too large it impacts how quickly a database can be restored. I know I've seen SQL Server chunk for 20 minutes or more on a backup history that has never been cleared before just to bring up a list of restore points. So now as part of my nightly processing I clear out any backup history older than two or three weeks, since I don't retain the backups on disk longer than that anyway.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Aug 2005
    Posts
    75
    thanks guys.

    No, the maintenance plan histories aren't an option. I've got 325 databases across 3 instances, each being backed up full weekly, with a transactional 2-4 times daily (depending on the DB). I clear the history on a regular basis, otherwise the history tables would be gargantuan. That did give me an idea however - I use a Backup Exec SQL agent to handle the backups and I could probably gather the data out of there, but I have a feeling that I'll be in the same boat - raw numbers that I'll have to figure out how to aggegate.

    And yeah, sorry about being a bit of vague on what I meant by average growth. I'm basically looking for the percentage of growth by month and by year. Subtracting month to previous month and averaging it is what I want to do.

    Basically I want results like this:
    Code:
    DBName | CurrentSize | 2005MonthlyAverageGrowth | 2005GrowthRate | 2006MonthlyAverageGrowth | 2006GrowthRate | 2007MonthlyAverageGrowth | 2007GrowthRate | 2008MonthlyAverageGrowth | 2008GrowthRate |
    Like I said, I'm normally pretty good at this stuff, I've just been stumped on this. I guess that's what happens with a sick kid at home and 3 hours of sleep. Let me noodle around with it a bit more and I'll see what I can come up with. I've got lots of coffee in me this morning and I'm feeling a bit better. So far, this is looking like I'll have to have a couple subqueries doing some of the calculations.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The output columns you are requesting are all annual agregates, even though they are using monthly units.
    So again, you can just look at the difference between the first month in the year and the last month in the year, and divide by the number of months between the first month and the last month.
    You do NOT need to figure out the change from month to month.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I like the history. When back in DBA days, I used to load those 2 tables into an Admin database before clearing the history on a regular basis, and append the new rows into my reporting table. Not sure how OP collects the data into his reporting table, but backup info is accurate to a page level (8K). Then, at the capacity planning meetings, show the trend graph per database to justify increase of disk size per server.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK. If you copy the data to a permanent table on a regular basis, that does sound like a painless way to do trending.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Aug 2005
    Posts
    75
    Quote Originally Posted by blindman
    OK. If you copy the data to a permanent table on a regular basis, that does sound like a painless way to do trending.

    it actually started because a few years ago, my boss wanted me to start sending him snapshots of disk utilization for the DBs in this cluster. There was talk of charging some of our customers more, depending on what the their DB disk utilization was. When I wrote the job to send the report, I figured keeping the raw data around couldn't be a bad idea, just for things like this. I just never got around to building a comprehensive overall trending report until now.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you want sweet reporting and analysis, link to the raw data or a view through Microsof Excel, and then you can do charts and pivot tables and statistical trending to your (bosses') heart's content.
    Makes more sense to analyze the data in an analytical tool such as Excel rather than doing it through SQL.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •