Disk Capacity trends 3

[xxing]

Hi

I have to add Disk capacity trending information to data. Need to add future dates with potential disk capacity based on past dates. I have created some dummy data as follows:

if object_id('tempdb..#dummytable') is not null drop table #dummytable
create table #dummytable
(
ServerName varchar(10)
,SampleDate datetime
,DiskUsed decimal(5)
,DiskCapacity decimal(5)
)

insert into #dummytable
select 'abcdefgh' ,'20140630' ,100 ,1000
union all
select 'abcdefgh' ,'20140531' ,90 ,1000
union all
select 'abcdefgh' ,'20140430' ,80 ,1000
union all
select 'abcdefgh' ,'20140331' ,70 ,1000
union all
select 'abcdefgh' ,'20140228' ,60 ,1000
union all
select 'zyxwvuts' ,'20140628' ,60 ,500
union all
select 'zyxwvuts' ,'20140528' ,50 ,500
union all
select 'zyxwvuts' ,'20140428' ,40 ,500
union all
select 'zyxwvuts' ,'20140328' ,30 ,500
union all
select 'zyxwvuts' ,'20140228' ,20 ,500

select * from #dummytable

Need to add 6 months of the last day of the month from the last date for each server
Need to calculate the additional Disk Used based on the last 6 months data
The end Disk Used data can't be more then the DiskCapacity


I want to see these additional records:

ServerName-----------SampleDate------------DiskUsed--------DiskCapacity
abcdefgh---------------2014-07-31--------------110------------------1000
abcdefgh---------------2014-08-31--------------120------------------1000
abcdefgh---------------2014-09-30--------------130------------------1000
abcdefgh---------------2014-10-31--------------140------------------1000
abcdefgh---------------2014-11-30--------------150------------------1000
abcdefgh---------------2014-12-31--------------160------------------1000

zyxwvuts---------------2014-07-31--------------70--------------------1000
zyxwvuts---------------2014-08-31--------------80--------------------1000
zyxwvuts---------------2014-09-30--------------90--------------------1000
zyxwvuts---------------2014-10-31--------------100-------------------1000
zyxwvuts---------------2014-11-30--------------110-------------------1000
zyxwvuts---------------2014-12-31--------------120-------------------1000

The DiskUsed is calculated as the average monthly gain from first to last month spread over the next 6 months

Can this be done using set based sql, or do I have to use a cursor

regards
Mark

 

[gmmastros]

Yes, this can be done set based, but it's complicated.

I came up with a solution that uses several common table expressions (CTE's).

The first thing I did was to get the growth from one month to the next. Please understand that this code relies on knowledge that each month is represented in the data set. Specifically, if a month is missing, the calculations will be wrong because the growth for the row will not represent the growth for a month. It will actually represent the growth for multiple months which would throw off all your calculations.

This code could be modified to calculate the daily growth and perform more accurate calculations. This would make the code even more complicated. SQL Server is ok with complicated, but it is more difficult to understand (and therefore more difficult to maintain).

To calculate the monthly growth, I use the Row_Number function to assign a counter to each row (grouped by server name). I then use that counter to join the table back to itself so that I have the disk size from 2 separate rows on the same row. This allows me to calculate the difference in size.

Then I get the average growth.

Finally, I create future rows with a union all select query, add that number of months, and calculate the expected future size.

Here's the code:

; With Data As
(
  select  ServerName, 
		  DiskUsed, 
		  SampleDate,
		  DiskCapacity,
          Row_Number() Over (Partition By ServerName Order By SampleDate Desc) As RowId
  from    #dummytable
  Where   SampleDate > DateAdd(month, -6, GetDate())
), Growth As
(
  Select ThisMonth.ServerName,
         Avg(ThisMonth.DiskUsed - PreviousMonth.DiskUsed) As DiskGrowth
  From   Data As ThisMonth
         Inner Join Data As PreviousMonth
           On ThisMonth.ServerName = PreviousMonth.ServerName
           And ThisMonth.RowId = PreviousMonth.RowId - 1
  Group By ThisMonth.ServerName
)
Select Growth.ServerName, 
       DateAdd(Day, -1, DateAdd(Month, Counter + 1 + DateDiff(Month, 0, Data.SampleDate), 0)) As FutureDate,
       Round(Data.DiskUsed + DiskGrowth * Counter, 0) As FutureSize, 
       Data.DiskCapacity
From   Growth
       Inner Join Data
         On Growth.ServerName = Data.ServerName
         And Data.RowId = 1
       Cross Join (
         Select 1 As Counter
         Union All Select 2
         Union All Select 3
         Union All Select 4
         Union All Select 5
         Union All Select 6
         ) As Counters

You'll notice that this code accurately creates your expected output. However, I strongly encourage you to understand what this query is doing so that you can maintain this code in the future. If there is any part of this that you don't understand, let me know and I will try to explain it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[xxing]

Thanks George