September 3, 2008 at 8:47 am
I wrote a small C# app to sit on one of my servers and query the WMI on each of my SQL Servers. This app gets capacity and space used information off of each server's disks and writes it back to a table on my monitoring server.
I wrote a SSRS report to give me a daily snapshot of how the disks are doing. Now I want to create a monthly report that shows movement so I can keep on top of growth and any other events.
The code below creates a temp table similar to the table I am writing to and populates it with data for 2 servers for 3 points in time. In reality I collect data every day but only provided 3 different days for clarity.
CREATE TABLE #DriveSpace (
RecId INT IDENTITY(1,1),
[Server] VARCHAR(100),
Drive CHAR(1),
Capacity DECIMAL(18,2),
SpaceUsed DECIMAL(18,2),
CollectedOn DATETIME
)
INSERT INTO #DriveSpace
SELECT 'SQL1', 'C', '184316.00', '10000', '08/03/2007'
UNION
SELECT 'SQL1', 'D', '284316.00', '44000', '08/03/2007'
UNION
SELECT 'SQL1', 'L', '84316.00', '5000', '08/03/2007'
UNION
SELECT 'SQL2', 'C', '144316.00', '6000', '08/03/2007'
UNION
SELECT 'SQL2', 'D', '384316.00', '304316', '08/03/2007'
UNION
SELECT 'SQL2', 'L', '84316.00', '8900', '08/03/2007'
UNION
SELECT 'SQL1', 'C', '184316.00', '10000', '08/15/2007'
UNION
SELECT 'SQL1', 'D', '284316.00', '44550', '08/15/2007'
UNION
SELECT 'SQL1', 'L', '84316.00', '5000', '08/15/2007'
UNION
SELECT 'SQL2', 'C', '144316.00', '5900', '08/15/2007'
UNION
SELECT 'SQL2', 'D', '384316.00', '314316', '08/15/2007'
UNION
SELECT 'SQL2', 'L', '84316.00', '8900', '08/15/2007'
UNION
SELECT 'SQL1', 'C', '184316.00', '11000', '09/03/2007'
UNION
SELECT 'SQL1', 'D', '284316.00', '45650', '09/03/2007'
UNION
SELECT 'SQL1', 'L', '84316.00', '5050', '09/03/2007'
UNION
SELECT 'SQL2', 'C', '144316.00', '5900', '09/03/2007'
UNION
SELECT 'SQL2', 'D', '384316.00', '316716', '09/03/2007'
UNION
SELECT 'SQL2', 'L', '84316.00', '8900', '09/03/2007'
SELECT
*
FROM
#DriveSpace
--DROP TABLE #DriveSpace
I can query the table like this to get data from 2 points in time I want to compare:
DECLARE
@Date1 VARCHAR(10),
@Date2 VARCHAR(10)
SET @Date1 = '08/03/2007'
SET @Date2 = '09/03/2007'
SELECT
A.[Server],
A.Drive,
A.Capacity,
A.SpaceUsed,
Convert(VARCHAR(10), A.CollectedOn, 101) AS DateCollected
FROM
#DriveSpace AS A
WHERE
A.RecId = (
SELECT
MAX(B.RecId)
FROM
#DriveSpace B
WHERE B.[Server] = A.[Server]
AND B.Drive = A.Drive
AND Convert(VARCHAR(10), B.CollectedOn, 101) = @Date2)
UNION
SELECT
A.[Server],
A.Drive,
A.Capacity,
A.SpaceUsed,
Convert(VARCHAR(10), A.CollectedOn, 101) AS DateCollected
FROM
#DriveSpace AS A
WHERE A.RecId = (
SELECT
MAX(B.RecId)
FROM
#DriveSpace AS B
WHERE B.[Server] = A.Server
AND B.Drive = A.Drive
AND Convert(VARCHAR(10), B.CollectedOn, 101) = @Date1)
ORDER BY
A.[Server],
A.Drive,
Convert(VARCHAR(10), A.CollectedOn, 101) ASC
And I get output like this:
Server Drive Capacity SpaceUsed CollectedOn
------------------------------------------------------------
SQL1 C 184316.00 10000.00 08/03/2007
SQL1 C 184316.00 11000.00 09/03/2007
SQL1 D 284316.00 44000.00 08/03/2007
SQL1 D 284316.00 45650.00 09/03/2007
SQL1 L 84316.00 5000.00 08/03/2007
SQL1 L 84316.00 5050.00 09/03/2007
SQL2 C 144316.00 6000.00 08/03/2007
SQL2 C 144316.00 5900.00 09/03/2007
SQL2 D 384316.00 304316.00 08/03/2007
SQL2 D 384316.00 316716.00 09/03/2007
SQL2 L 84316.00 8900.00 08/03/2007
SQL2 L 84316.00 8900.00 09/03/2007
This is not something I can use to report with though. I plug it into a spreadsheet to work my formulas (some of which need to operate on 2 rows at a time). Ultimately what I'm showing management is something like this:
Server Drive FreeSpace % Growth / mo
------------------------------------------------------------
SQL1 C 82.6% 0.27%
SQL1 L 37.6% -29.90%
SQL1 P 20.7% -20.51%
They can easily see the trend, know if it's a hotspot and evaluate my suggestions.
How can I write the query to get my closer to the final output? Is is possible to get my output to look more like this at least?
Server Drive Curr. Capacity StartSpaceUsed EndSpaceUsed
----------------------------------------------------------------------------
SQL1 C 184316.00 10000.00 11002.12
If I can get it like the above (basically combining 2 rows into one), it will be easy to write my formulas into the SQL and skip the spreadsheet entirely.
Maybe I should try a different approach entirely?
Thanks,
Rob
September 4, 2008 at 12:45 pm
How about adding a column in #DriveSpace called PrevSpaceUsed
and updating it as needed with the data to compare against?
This is rough SQL code for comparing against the preceding month:
Update #DriveSpace
set PrevSpaceUsed = b.SpaceUsed
from #DriveSpace a inner join #DriveSpace b
on a.[server] = b.[server] and a.drive = b.drive
where datediff(mm, a.CollectedOn, b.CollectedOn) = -1
September 18, 2008 at 11:15 am
Some of the problems originate from the fact that there could be multiple samples in that table for a given date. The solution I've settled upon at the moment:
DECLARE
@Date1 VARCHAR(10),
@Date2 VARCHAR(10)
SELECT
@Date1 = '08/11/2008',
@Date2 = '09/11/2008'
SELECT
Sample2.[Server],
Sample2.Drive,
Sample2CollectedOn=Convert(VARCHAR(10), Sample2.CollectedOn, 101),
Sample2AvgCapacityMB=Avg(Sample2.Capacity),
Sample2AvgFreeSpace=Avg(Sample2.FreeSpace),
Sample1CollectedOn=Convert(VARCHAR(10), Sample1.CollectedOn, 101),
Sample1AvgCapacityMB=ISNULL(Avg(Sample1.Capacity), 0),
Sample1AvgFreeSpace=ISNULL(Avg(Sample1.FreeSpace), 0),
FreeSpaceChangeMB=Avg(Sample2.FreeSpace) - Avg(Sample1.FreeSpace)
FROM
DrivesFreeSpace AS Sample2
LEFT OUTER JOIN DrivesFreeSpace AS Sample1
ON Sample1.[Server] = Sample2.[Server]
AND Sample1.Drive = Sample2.Drive
AND Convert(VARCHAR(10), Sample1.CollectedOn, 101) = @Date1
WHERE Convert(VARCHAR(10), Sample2.CollectedOn, 101) = @Date2
GROUP BY
Sample2.[Server],
Sample2.Drive,
Convert(VARCHAR(10), Sample2.CollectedOn, 101),
Convert(VARCHAR(10), Sample1.CollectedOn, 101)
ORDER BY
Sample2.[Server],
Sample2.Drive
This will work for the reporting and returns NULL for dates that don't exist to compare. I didn't want to add columns to my table to hold derivative data as that gets messy fast. So far, I have checked this query against the raw data for about 2mo worth of monitoring and it is accurate.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply