Daily/weekly/monthly totals - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Daily/weekly/monthly totals

Author

Topic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-10 : 17:47:37

I have a database growth tracker tool that collects the sizes of each database on the server. When run on a schedule, such as daily, we can see if there's a trend and make predictions of when we'll need to buy more storage.

I'd like to be able to see what the daily, weekly, and monthly growths are for each database. Can someone help me write these? I'm sure there's a cool technique, but my old school way would be to use a Tally table. Can this be achieve via ROW_NUMBER() or maybe a CTE?

Here's my DDL with sample data and expected output. I've only shown one database and a few days of data as I think I can handle incorporating the solution(s) for all of the databases as well as altering it for weekly and monthly. Anyway, here you go:


--DatabaseSize is in KB
DECLARE @dbGrowth table (DatabaseName varchar(256), DatabaseSize int, CreateDate datetime)

INSERT INTO @dbGrowth
SELECT 'Database1', 191403008, '2011-03-10' UNION ALL
SELECT 'Database1', 184644608, '2011-03-09' UNION ALL
SELECT 'Database1', 179543513, '2011-03-08' UNION ALL
SELECT 'Database1', 170333221, '2011-03-07' UNION ALL
SELECT 'Database1', 165123456, '2011-03-06'

--Output for daily growth (DayN - (DayN-1)):
Database1	0		Day1
Database1	4091047		Day2
Database1	11121476	Day3
Database1	5209765		Day4
Database1	9210292		Day5

What goes in the third column doesn't really matter. It can be a date, an incrementer, whatever. I just need it to be ordered according to time.

To explain the output, the second column is the size of that date minus the size of the previous date. This column can be negative if a shrink happens (bad!). So this particular output would represent the daily report. I also need weekly and monthly, but I think I can handle that once I see the daily solution. If you want to provide all three, then that'll work too. My goal is to learn this stuff better though.

Any 2005/2008 solutions for this?

If you are interested in the tool, here it is: http://weblogs.sqlteam.com/tarad/archive/2010/07/09/Database-Growth-Tracker-Tool-ndash-New-Version.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-10 : 18:35:17

I've got this so far:

;WITH Growth(DatabaseName, DatabaseSize, CreateDate, Row) 
AS (select *, ROW_NUMBER() OVER(PARTITION BY DatabaseName ORDER BY CreateDate) Row from @dbGrowth)
select A.DatabaseName, A.CreateDate, 
IsNull(A.DatabaseSize-B.DatabaseSize,0) Growth, 'Day' + cast(A.Row as varchar)
from Growth A
left join Growth B on A.DatabaseName=B.DatabaseName AND A.Row=B.Row+1
order by A.Row

I'm not getting the same numbers you posted though. I'll keep working on it and will see if I get the weekly/monthly growth. BTW, what happens if you get 2 entries for the same day? What if you skip a day or more?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-10 : 21:48:50

As far as "duplicates" for a day, I think that whatever the size for the maximum or even minimum for the day is fine.

As far as skipping days, hmmm, not sure. I supposed if the job fails for say 2 weeks, then I get a read on the 15th day, I think what would work is to calculate the number of days between the two samples and average it.

This data doesn't need to be all that accurate, just enough to show a trend and be able to report it to the SAN team, management, dev team, etc...

I've been doing the trending via an Excel graph, but that doesn't really show averages which is really what we need. I'd like to do it via T-SQL as I've got hundreds of servers and thousands of databases, so doing it in the same table would be great (my actual table has servername too).

Thanks!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-10 : 21:52:06

Wow, my numbers were way off. I did them by hand and apparently goofed up completely.

0
5209765
9210292
5101095
6758400

I don't have SQL in front of me at the moment to check it. I'll check it out tomorrow morning.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-10 : 22:17:48

the daily / weeekly / monthly etc can be done with a flag and cte and change the aggregate function on the size to your requirement


with
GrowthData as
(
	select	DatabaseName, DatabaseSize,
		CreateDate 	= case  @rpt
					when 'D' then dateadd(day, datediff(day, 0, CreateDate), 0)
					when 'M' then dateadd(month, datediff(month, 0, CreateDate), 0)
					when 'Y' then dateadd(year, datediff(year, 0, CreateDate), 0)
					end
	from	@dbGrowth
),
Growth as
(
	select 	DatabaseName, DatabaseSize = AVG(DatabaseSize), CreateDate, 
		ROW_NUMBER() OVER(PARTITION BY DatabaseName ORDER BY CreateDate) Row 
	from 	GrowthData
	group by DatabaseName, CreateDate
)

KH
[spoiler]Time is always against us[/spoiler]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-11 : 12:55:47

Thanks Rob, yours works great for the daily report.

KH, I'm not able to get yours to work, and unfortunately I'm not smart enough with CTE's to be able to correct it (I'm mostly a production DBA). I am getting the following error:

quote:

Msg 102, Level 15, State 1, Line 40
Incorrect syntax near ')'.

When I add a select to get rid of the error, I'm not getting the full output. So I'm not sure what query to add to see more data. I added this:
select databasename, databasesize, createdate
from growth

I wish I had better T-SQL skills!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-11 : 13:43:29

Need to add "SELECT * FROM Growth" after khtan's query, must've gotten cut off.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-11 : 13:46:13

When I add that, I only get back one row:


DatabaseName	DatabaseSize	CreateDate	Row
Database1	178209561	2011-03-01 00:00:00.000	1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-11 : 14:16:12

Change the @rpt value to 'D'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-11 : 14:21:48

Oh! I thought I was supposed to declare a variable and set it. That fixed it so that it provides multiple rows back.

But it isn't providing the right output. It's just reporting the size for that day and not the size difference.

Here's what I have (both yours and his included):


DECLARE @dbGrowth table (DatabaseName varchar(20), DatabaseSize int, CreateDate datetime)

INSERT INTO @dbGrowth
SELECT 'Database1', 191403008, '2011-03-10' UNION ALL
SELECT 'Database1', 184644608, '2011-03-09' UNION ALL
SELECT 'Database1', 179543513, '2011-03-08' UNION ALL
SELECT 'Database1', 170333221, '2011-03-07' UNION ALL
SELECT 'Database1', 165123456, '2011-03-06'

;WITH Growth(DatabaseName, DatabaseSize, CreateDate, Row) 
AS (select *, ROW_NUMBER() OVER(PARTITION BY DatabaseName ORDER BY CreateDate) Row from @dbGrowth)
select A.DatabaseName, A.CreateDate, 
IsNull(A.DatabaseSize-B.DatabaseSize,0) Growth, 'Day' + cast(A.Row as varchar)
from Growth A
left join Growth B on A.DatabaseName=B.DatabaseName AND A.Row=B.Row+1
order by A.Row

;with
GrowthData as
(
	select	DatabaseName, DatabaseSize,
		CreateDate 	= case  'D'
					when 'D' then dateadd(day, datediff(day, 0, CreateDate), 0)
					when 'M' then dateadd(month, datediff(month, 0, CreateDate), 0)
					when 'Y' then dateadd(year, datediff(year, 0, CreateDate), 0)
					end
	from	@dbGrowth
),
Growth as
(
	select 	DatabaseName, DatabaseSize = AVG(DatabaseSize), CreateDate, 
		ROW_NUMBER() OVER(PARTITION BY DatabaseName ORDER BY CreateDate) Row 
	from 	GrowthData
	group by DatabaseName, CreateDate
)
select *
from growth

--Rob's
DatabaseName         CreateDate              Growth      
-------------------- ----------------------- ----------- ---------------------------------
Database1            2011-03-06 00:00:00.000 0           Day1
Database1            2011-03-07 00:00:00.000 5209765     Day2
Database1            2011-03-08 00:00:00.000 9210292     Day3
Database1            2011-03-09 00:00:00.000 5101095     Day4
Database1            2011-03-10 00:00:00.000 6758400     Day5

--KH's
DatabaseName         DatabaseSize CreateDate              Row
-------------------- ------------ ----------------------- --------------------
Database1            165123456    2011-03-06 00:00:00.000 1
Database1            170333221    2011-03-07 00:00:00.000 2
Database1            179543513    2011-03-08 00:00:00.000 3
Database1            184644608    2011-03-09 00:00:00.000 4
Database1            191403008    2011-03-10 00:00:00.000 5

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-11 : 16:43:41

Here's a combination. I added some extra data to show ~~differences between~~ averages for weeks and months, and also a statement to generate sizes for another database (commented out):

DECLARE @dbGrowth TABLE (DatabaseName VARCHAR(20), DatabaseSize INT, CreateDate DATETIME)

INSERT INTO @dbGrowth
SELECT 'Database1', 191403008, '2011-03-10' UNION ALL
SELECT 'Database1', 184644608, '2011-03-09' UNION ALL
SELECT 'Database1', 179543513, '2011-03-08' UNION ALL
SELECT 'Database1', 170333221, '2011-03-07' UNION ALL
SELECT 'Database1', 165123456, '2011-03-06'

INSERT @dbGrowth SELECT DatabaseName, 
DatabaseSize-(RAND(CAST(CAST(NEWID() AS BINARY(1)) AS TINYINT))*10000000), DATEADD(WEEK, -1, createdate) FROM @dbGrowth 

/* 
INSERT @dbGrowth SELECT 'Database2', 
DatabaseSize-(RAND(CAST(CAST(NEWID() AS BINARY(1)) AS TINYINT))*10000000), createdate FROM @dbGrowth 
*/

;WITH 
Sizes(DatabaseName, DatabaseSize, CreateDate, ROW, DAY, WEEK, MONTH) 
	AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY DatabaseName ORDER BY CreateDate) ROW,
	DATEADD(DAY, DATEDIFF(DAY, 0, CreateDate), 0) DAY,
	DATEADD(WEEK, DATEDIFF(WEEK, 0, CreateDate), 0) WEEK,
	DATEADD(MONTH, DATEDIFF(MONTH, 0, CreateDate), 0) MONTH
	FROM @dbGrowth),
Daily(DatabaseName, DAY, DailyAvg) 
	AS (SELECT DatabaseName, DAY, AVG(DatabaseSize) FROM Sizes GROUP BY DatabaseName, DAY),
Weekly(DatabaseName, WEEK, WeeklyAvg) 
	AS (SELECT DatabaseName, WEEK, AVG(DatabaseSize) FROM Sizes GROUP BY DatabaseName, WEEK),
Monthly(DatabaseName, MONTH, MonthlyAvg) 
	AS (SELECT DatabaseName, MONTH, AVG(DatabaseSize) FROM Sizes GROUP BY DatabaseName, MONTH)
SELECT S.DatabaseName, S.DatabaseSize, S.CreateDate, 
IsNull(S.DatabaseSize-X.DatabaseSize,0) Growth, DailyAvg, WeeklyAvg, MonthlyAvg
FROM Sizes S
INNER JOIN Daily D ON S.DatabaseName=D.DatabaseName AND S.Day=D.Day
INNER JOIN Weekly W ON S.DatabaseName=W.DatabaseName AND S.Week=W.Week
INNER JOIN Monthly M ON S.DatabaseName=M.DatabaseName AND S.Month=M.Month
LEFT JOIN Sizes X ON S.DatabaseName=X.DatabaseName AND S.Row=X.Row+1
ORDER BY S.DatabaseName, S.Row

The next question is what constitutes growth for weekly and monthly intervals? Is it SUM() of the growth for that interval? Is it MAX()-MIN()? Or is it FIRST()-LAST() size?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-11 : 16:54:43

Yes SUM() of the growth for that interval for weekly/monthly, although MAX() - MIN() or even FIRST - LAST sounds fine too. Anything that represents an approximate storage increase/decrease for the time interval. Doesn't need to be super accurate, just enough to spot trends/problems and help us plan for storage.

The daily growths will help us determine if we had an unusual spike of activity. The weekly/monthly growths will help us with storage. At least that's what I think.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-11 : 17:11:28

Replace the last query with this:

;WITH 
Sizes(DatabaseName, DatabaseSize, CreateDate, ROW, DAY, WEEK, MONTH) 
	AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY DatabaseName ORDER BY CreateDate) ROW,
	DATEADD(DAY, DATEDIFF(DAY, 0, CreateDate), 0) DAY,
	DATEADD(WEEK, DATEDIFF(WEEK, 0, CreateDate), 0) WEEK,
	DATEADD(MONTH, DATEDIFF(MONTH, 0, CreateDate), 0) MONTH
	FROM @dbGrowth),
Daily(DatabaseName, DAY, DailyAvg) 
	AS (SELECT DatabaseName, DAY, AVG(DatabaseSize) FROM Sizes GROUP BY DatabaseName, DAY),
Weekly(DatabaseName, WEEK, WeeklyAvg) 
	AS (SELECT DatabaseName, WEEK, AVG(DatabaseSize) FROM Sizes GROUP BY DatabaseName, WEEK),
Monthly(DatabaseName, MONTH, MonthlyAvg) 
	AS (SELECT DatabaseName, MONTH, AVG(DatabaseSize) FROM Sizes GROUP BY DatabaseName, MONTH),
Growth(DatabaseName, DatabaseSize, CreateDate, Growth, DAY, WEEK, MONTH) 
	AS (SELECT S.DatabaseName, S.DatabaseSize, S.CreateDate, 
	ISNULL(S.DatabaseSize-X.DatabaseSize,0) Growth, S.Day, S.Week, S.Month
	FROM Sizes S
	LEFT JOIN Sizes X ON S.DatabaseName=X.DatabaseName AND S.Row=X.Row+1)
SELECT G.DatabaseName, G.DatabaseSize, G.CreateDate, G.Growth, 
D.DailyAvg, SUM(G.Growth) OVER (PARTITION BY G.DatabaseName, G.Day) DailyGrowth,
W.WeeklyAvg, SUM(G.Growth) OVER (PARTITION BY G.DatabaseName, G.Week) WeeklyGrowth,
M.MonthlyAvg, SUM(G.Growth) OVER (PARTITION BY G.DatabaseName, G.Month) MonthlyGrowth
FROM Growth G
INNER JOIN Daily D ON G.DatabaseName=D.DatabaseName AND G.Day=D.Day
INNER JOIN Weekly W ON G.DatabaseName=W.DatabaseName AND G.Week=W.Week
INNER JOIN Monthly M ON G.DatabaseName=M.DatabaseName AND G.Month=M.Month
ORDER BY G.DatabaseName, G.CreateDate

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-11 : 17:17:20

This is awesome! Thank you!

Now I've got to figure it out so I can learn this stuff.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-11 : 17:32:48

Ah, I knew there was an easier way:

;WITH 
Sizes(DatabaseName, DatabaseSize, CreateDate, ROW, DAY, WEEK, MONTH) 
	AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY DatabaseName ORDER BY CreateDate) ROW,
	DATEADD(DAY, DATEDIFF(DAY, 0, CreateDate), 0) DAY,
	DATEADD(WEEK, DATEDIFF(WEEK, 0, CreateDate), 0) WEEK,
	DATEADD(MONTH, DATEDIFF(MONTH, 0, CreateDate), 0) MONTH
	FROM @dbGrowth),
Growth(DatabaseName, DatabaseSize, CreateDate, Growth, DAY, WEEK, MONTH) 
	AS (SELECT S.DatabaseName, S.DatabaseSize, S.CreateDate, 
	ISNULL(S.DatabaseSize-X.DatabaseSize,0) Growth, S.Day, S.Week, S.Month
	FROM Sizes S
	LEFT JOIN Sizes X ON S.DatabaseName=X.DatabaseName AND S.Row=X.Row+1)
SELECT G.DatabaseName, G.DatabaseSize, G.CreateDate, G.Growth, 
AVG(G.DatabaseSize) OVER (PARTITION BY G.DatabaseName, G.Day) AvgDailySize,  
SUM(G.Growth) OVER (PARTITION BY G.DatabaseName, G.Day) DailyGrowth,
AVG(G.DatabaseSize) OVER (PARTITION BY G.DatabaseName, G.Week) AvgWeeklySize, 
SUM(G.Growth) OVER (PARTITION BY G.DatabaseName, G.Week) WeeklyGrowth,
AVG(G.DatabaseSize) OVER (PARTITION BY G.DatabaseName, G.Month) AvgMonthlySize, 
SUM(G.Growth) OVER (PARTITION BY G.DatabaseName, G.Month) MonthlyGrowth
FROM Growth G
ORDER BY G.DatabaseName, G.CreateDate

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-11 : 17:49:23

Not that it matters, but the daily/weekly/monthly output can be in different result sets. Doesn't need to be one report. Either way is fine with me though.

Thanks for the help on this! This will make my life easier as I've been doing it manually from my DatabaseGrowth table. I've put it in pretty graphs using Excel since that's easy to do, but any type of "how much growth is there on this server in 6 months" question is done manually.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-11 : 18:15:20

Here's another version that grafts khtan's logic and simplifies it somewhat:

DECLARE @interval char(1)
SET @interval='D'  -- D = Day, W = Week, M = Month
;WITH 
Sizes(DatabaseName, DatabaseSize, CreateDate, ROW) 
	AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY DatabaseName ORDER BY CreateDate) ROW FROM @dbGrowth),
Growth(DatabaseName, DatabaseSize, CreateDate, Growth) 
	AS (SELECT S.DatabaseName, CAST(S.DatabaseSize as bigint), S.CreateDate, 
	ISNULL(S.DatabaseSize-X.DatabaseSize,0) Growth
	FROM Sizes S
	LEFT JOIN Sizes X ON S.DatabaseName=X.DatabaseName AND S.Row=X.Row+1)
SELECT G.DatabaseName, G.DatabaseSize, G.CreateDate, G.Growth, @interval AS Interval, 
AVG(G.DatabaseSize) OVER (PARTITION BY G.DatabaseName, 
CASE @interval 
	WHEN 'D' THEN DateAdd(day, Datediff(day, 0, CreateDate), 0)
	WHEN 'W' THEN DateAdd(week, Datediff(week, 0, CreateDate), 0)
	WHEN 'M' THEN DateAdd(month, Datediff(month, 0, CreateDate), 0)
END) AvgIntervalSize,
Sum(G.Growth) OVER (PARTITION BY G.DatabaseName, 
CASE @interval
	WHEN 'D' THEN DateAdd(day, Datediff(day, 0, CreateDate), 0)
	WHEN 'W' THEN DateAdd(week, Datediff(week, 0, CreateDate), 0)
	WHEN 'M' THEN DateAdd(month, Datediff(month, 0, CreateDate), 0)
END) IntervalGrowth
FROM Growth G
ORDER BY G.DatabaseName, G.CreateDate

This lets you parameterize the date interval and have only one set of totals/averages instead of 3. It wouldn't be hard to make this really flexible.

I still think this can be simplified but I think I'm gonna quit after 6-7 versions.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-11 : 18:23:29

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources