Graph multiple databases growth using SSMS only

Question

Graph multiple databases growth using SSMS only

Viewing 6 posts - 16 through 20 (of 20 total)

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

Pablo Echeverria Say Hey Kid Points: 699 More actions · Answer 1

Hi eliasal, in that case you can rewrite the query as follows:

SELECT [Name], 0, b
  FROM [lr] GROUP BY [Name], [m], b
 UNION ALL
SELECT [Name], 90, [m]*90+b
  FROM [lr] GROUP BY [Name], [m], b

This reply was modified 2 years, 11 months ago by Pablo Echeverria.

eliassal Default port Points: 1467 More actions · Answer 2

So this code should replace

SELECT [Name], GEOMETRY::STGeomFromText('LINESTRING(0 '+CAST( AS VARCHAR(53))+',90 '+CAST([m]*90+ AS VARCHAR(53))+')',0) [Geom]
FROM [lr] GROUP BY [Name], [m],

If I am not mistaken?

eliassal Default port Points: 1467 More actions · Answer 3

I tried the following but results confused me more, got only 0s and 90, how should this interpreted

QueryLinear_002

Here is the modified code as your suggestion

USE [msdb]
;WITH [BackupSize] AS (
SELECT [bs].[database_name] [Name], DATEDIFF(DD, DATEADD(DD, -45, GETDATE()), [bs].[backup_start_date]) [Date], SUM([bs].[backup_size])/1024/1024/1024 [Size]
FROM [backupmediafamily] [bmf]
INNER JOIN [backupset] [bs] ON [bmf].[media_set_id] = [bs].[media_set_id]
WHERE [bs].[backup_start_date] >= DATEADD(DD, -45, GETDATE())
AND [bs].[type] = 'D'
GROUP BY [bs].[database_name], DATEDIFF(DD, DATEADD(DD, -45, GETDATE()), [bs].[backup_start_date]))
SELECT [Name], [Date], [Size],
PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY [Size]) OVER(PARTITION BY [Name]) [Q1],
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY [Size]) OVER(PARTITION BY [Name]) [Q2],
PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY [Size]) OVER(PARTITION BY [Name]) [Q3]
INTO #Info FROM [BackupSize]
UPDATE #Info SET [Size] = [Q2] WHERE [Size] < 2*[Q1]-[Q3] OR [Size] > 2*[Q3]-[Q1]
;WITH [slope] AS (
SELECT [Name], MAX([DateAvg]) [DateAvg], MAX([SizeAvg]) [SizeAvg],
CASE WHEN SUM(([Date]-[DateAvg])*([Date]-[DateAvg]))=0 THEN 0 ELSE SUM(([Date]-[DateAvg])*([Size]-[SizeAvg]))/SUM(([Date]-[DateAvg])*([Date]-[DateAvg])) END [m]
FROM (
SELECT [Name], [Date], AVG([Date]) OVER(PARTITION BY [Name]) [DateAvg], [Size], AVG([Size]) OVER(PARTITION BY [Name]) [SizeAvg]
FROM #Info) x
GROUP BY [Name]),
[lr] AS (SELECT [Name], [m], [SizeAvg]-[DateAvg]*[m]  FROM [slope])
SELECT [Name], 0, b
FROM [lr] GROUP BY [Name], [m], b
UNION ALL
SELECT [Name], 90, [m]*90+b
FROM [lr] GROUP BY [Name], [m], b

Pablo Echeverria Say Hey Kid Points: 699 More actions · Answer 4

Hi eliassal, yes the code looks good, so you have the database name, the starting point (day 0, 45 days ago, with size 47 for StackOverflow2013), and the final point (day 90, 45 days in the future, with size 0.18 for AdventureWorks2012). Remember this is the projection, so you can trace a line from start to end. The original data is in the second part of the final query after UNION ALL.

eliassal Default port Points: 1467 More actions · Answer 5

Great, got it, but shouldn't we have something like

0 > 45 days ago

X > Current

90 > 45 days in the future

so it might look like this

-45 > 45 days ago

0 > Current

90 > 45 days in the future

Pablo Echeverria Say Hey Kid Points: 699 More actions · Answer 6

Hi eliassal, you can easily modify the script to substract 45 days from the X value, then you will get what you want. Just remember the value of in the form y=mx+b will also change.