Sql-server – Calculating daily growth using previous row value in new column – SQL Server

sql servert-sql

I have the following query below that shows size of a database and when this data was collected. I am trying to create a new column that has "average daily growth" which would be determined by the size on the previous day.

If my query captures data from multiple databases/instances how can I do this for each database? As seen in here

I am trying to calculate daily growth per database using the "DB Size mb" column and would like a new column called "LastDayGrowth"

This is my query:

SELECT DISTINCT
        c.instance_name ,
        a.database_name ,
        CONVERT (DATE, SWITCHOFFSET(CAST (a.collection_time AS DATETIMEOFFSET),
                                    '+00:00')) AS 'Collection Time' ,
        'LDF mb' = ( CONVERT(DEC(15, 2), a.logsize) * 8192 / 1048576.0 ) ,
        'MDF mb' = ( CONVERT(DEC(15, 2), a.dbsize) * 8192 / 1048576.0 ) ,
        ( ( CONVERT(DEC(15, 2), a.dbsize) + CONVERT(DEC(15, 2), a.logsize) )
          * 8192 / 1048576.0 ) AS 'DB Size mb' ,
        'Reserved mb' = ( a.reservedpages * 8192 / 1048576.0 ) ,
        'Data mb' = CONVERT(DEC(15, 2), a.pages) * 8192 / 1048576.0 ,
        'Index mb' = ( a.usedpages - a.pages ) * 8192 / 1048576.0 ,
        'Unused mb' = ( ( CONVERT(DEC(15, 2), a.reservedpages)
                          - CONVERT(DEC(15, 2), a.usedpages) ) * 8192
                        / 1048576.0 ) ,
        'Unallocated mb' = ( CASE WHEN a.dbsize >= a.reservedpages
                                  THEN ( CONVERT (DEC(15, 2), a.dbsize)
                                         - CONVERT (DEC(15, 2), a.reservedpages) )
                                       * 8192 / 1048576.0
                                  ELSE 0
                             END )
FROM    MDWDB.snapshots.disk_usage a
        INNER JOIN MDWDB.core.snapshots_internal b ON a.snapshot_id = b.snapshot_id
        INNER JOIN MDWDB.core.source_info_internal c ON b.source_id = c.source_id
ORDER BY c.instance_name ,
        a.database_name ASC;

I have tried this using the "LAG" function but did not succeed. I tried it like this:

LastDayGrowth = DATEDIFF(DAY,
                         LAG(( CONVERT(DEC(15, 2), a.dbsize)
                               + CONVERT(DEC(15, 2), a.logsize) )
                             * 8192 / 1048576.0, 1, NULL) OVER ( ORDER BY a.database_name, ( ( CONVERT(DEC(15,
                                                      2), a.dbsize)
                                                      + CONVERT(DEC(15,
                                                      2), a.logsize) )
                                                      * 8192
                                                      / 1048576.0 ) ),
                         ( ( CONVERT(DEC(15, 2), a.dbsize)
                             + CONVERT(DEC(15, 2), a.logsize) ) * 8192
                           / 1048576.0 ))

Any tips, or advice would be greatly appreciated!

Best Answer

I just read this article adressing this topic and I suppose you might be interested, because it uses the lag function to solve exactly your Task:

https://www.mssqltips.com/sqlservertip/3690/identify-sql-server-database-growth-rates/

The Author created the following Script using the LAG Window function:

SELECT DISTINCT
    A.[database_name]
,   AVG( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Avg Size Diff From Previous (MB)]
,   MAX( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Max Size Diff From Previous (MB)]
,   MIN( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Min Size Diff From Previous (MB)]
,   A.[Sample Size]
FROM 
(
    SELECT
        s.[database_name]
    --, s.[backup_start_date]
    ,   COUNT(*) OVER ( PARTITION BY s.[database_name] ) AS [Sample Size]
    ,   CAST ( ( s.[backup_size] / 1024 / 1024 ) AS INT ) AS [Backup Size (MB)]
    ,   CAST ( ( LAG(s.[backup_size] ) 
            OVER ( PARTITION BY s.[database_name] ORDER BY s.[backup_start_date] ) / 1024 / 1024 ) AS INT ) AS [Previous Backup Size (MB)]
    FROM 
        [msdb]..[backupset] s
    WHERE
        s.[type] = 'D' --full backup
    --ORDER BY
    --  s.[database_name]
    --, s.[backup_start_date]
) AS A
ORDER BY
    [Avg Size Diff From Previous (MB)] DESC;
GO

Related Solutions

Sql-server – better way to create dynamic time averaged series over a dynamic frequency

Your join between the tables is embedded in a function making it really hard for the optimizer to do anything smart with it. I guess it has to compare every row in one table against every other row in the other table.

Rewriting your join with a range check should be a lot faster. I also added a primary key to your table variable to remove a sort operation from the query plan and I turned your table variable into a temp table instead. The difference in my tests was that the query plan started to use Parallelism.

declare @start datetime;
declare @end datetime;
set @start = '20120301';
set @end = '20120303';
declare @interval int;
set @interval = 300;

create table #tpart
(
  dt datetime primary key
);

with CTE_TimeTable
as
(
  select @start as [date]
  union all
  select dateadd(second ,@interval, [date])
  from CTE_TimeTable
  where dateadd(second, @interval, [date]) <= @end
)
insert into #tpart
select [date]
from CTE_TimeTable
option (maxrecursion 0);

select t.dt, avg(c.x1), min(c.x1), max(c.x2), avg(c.x2), min(c.x2), max(c.x2)
from clean.data c
  inner join #tpart t 
    on c.Date >= t.dt and
       c.Date < dateadd(second, @interval, t.dt)
group by t.dt;

drop table #tpart;

Note: This query does not return exactly the same intervals as your query does. It will divide the date range into equally sized parts where your query had one half interval at the start and one half interval at the end of the range. It is of course possible to modify the query to do be equivalent to your query if that is desired.

Update

I tested on a table with a total of 1036801 rows and with 34560 in the interval 2012-03-01 to 2012-03-03. In my tests the original query takes 4.1 seconds. The query above takes 0.1 seconds.

Script to generate the test data:

create table clean.data
(
    Date datetime primary key,
    x1 int,
    x2 int
);

go

with C as
(
  select cast('20120201' as datetime) as D
  union all
  select dateadd(second, 5, D)
  from C
  where D < '20120401'
)
insert into clean.data(Date, x1, x2)
select D, checksum(newid()) % 1000, checksum(newid()) % 1000
from C
option (maxrecursion 0);

Sql-server – Execution plan flips Filter and Execute Scalar when using the PK, causes cast to fail

I'm sure there are dba.se and StackOverflow duplicates, but it was far faster for me to post this link:

https://feedback.azure.com/forums/908035-sql-server/suggestions/32912431-sql-server-should-not-raise-illogical-errors

In short, the Query Optimizer is really free to rewrite the plan as it sees best. Sometimes, it means a column is transformed (CAST -> int) close to retrieval so that it doesn't have to be carried many steps forward before performing the CAST.

The only sure-fire way of preventing the error is to look at the CAST expression itself, e.g.:

cast(CASE WHEN ISNUMERIC(ENTRY_CODE)=1 THEN ENTRY_CODE END as int)

Best Answer

Related Solutions

Sql-server – better way to create dynamic time averaged series over a dynamic frequency

Sql-server – Execution plan flips Filter and Execute Scalar when using the PK, causes cast to fail

Related Question