October 21, 2015 at 8:15 am
Roads, where we are going we don't need roads.
October 21, 2015 at 5:35 pm
Maybe as below? I think you've overly complicated the date selection and caused the duplicates.
For best performance, the Diskspace_Global table should probably be clustered either on Date_ (with the code below) or ( Server, Domain, Date_ ) (and the GROUPing changed to that order).
SELECT
YEAR(dg.MinDate) AS Year_,
MONTH(dg.MinDate) AS Month_,
dg.Server,
dg.Domain,
drc.COUNTRY,
drc.Region,
drc.SITE,
(
select top 1 rsag2.OU from dbo.Reporting_Servers_AD_Global rsag2
where rsag2.Server = dg.Server
and rsag2.Domain = dg.Domain
and rsag2.Date_ = dg.MinDate
) As OU,
dg.TotalDiskSpace,
dg.TotalDiskspaceUsed,
dg.TotalDiskspaceFree
into [stg].[Report_Global_Storage_Capacity]
FROM (
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date_]), 0) AS Month,
Server,
Domain,
MIN([Date_]) AS MinDate,
MAX(Total_Diskspace) AS TotalDiskSpace,
MAX(Total_Diskspace_Used) AS TotalDiskspaceUsed,
MAX(Total_Diskspace_Free) AS TotalDiskspaceFree
FROM dbo.Diskspace_Global
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date_]), 0), Server, Domain
) AS dg
inner join dbo.Diskspace_Region_Country drc
on drc.Server = dg.Server
and drc.Domain = dg.Domain
and drc.Report = 'YES'
order by Year_,
Month_,
dg.Server,
dg.Domain,
drc.COUNTRY,
drc.Region,
drc.SITE,
OU,
TotalDiskspace,
TotalDiskspaceUsed
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 22, 2015 at 1:01 am
Good morning,hope the trafic in the skies were not bad this morning
(for anyone reading this in the "future" the day I'm posting this is October 22th 2015, a day after the date Marty and Doc Brown travelled to from 1985, in the 1989 movie "Back to the Future 2",which had flying cars in the year 2015)
ok back to query results
ChrisM@Work
--distinct
Time 04:25
#Records 76974
--no distinct
Time 04:18
#Records 130038
ScottPletcher
ScottPletcher
--distinct
Time 04:01
#Records 76974
--no distinct
Time 03:57
#Records 130038
Quick look at the data I spot some issues with not using distinct,for instance when a server has multiple records within a month where the disk space are equal to the max of disk space for that month for that server.
October 22, 2015 at 1:42 am
You must be in Yerp to be active at this ungodly hour.
We're homing in on a decent solution now. Try this:
SELECT
Year_ = YEAR(dg.Date_),
Month_ = MONTH(dg.Date_),
dg.[Server],
dg.Domain,
COUNTRY = MAX(drc.COUNTRY),
Region = MAX(drc.Region),
[SITE] = MAX(drc.[SITE]),
y.OU,
TotalDiskspace = MAX(dg.Total_Diskspace),
TotalDiskspaceUsed = MAX(dg.Total_Diskspace_Used),
TotalDiskspaceFree = MAX(dg.Total_Diskspace_Free)
FROM dbo.Diskspace_Global dg
INNER JOIN dbo.Diskspace_Region_Country drc
ON drc.[Server] = x.[Server]
AND drc.Domain = x.Domain
CROSS APPLY (
SELECT TOP 1 rsag2.OU -- TOP without ORDER BY?
FROM dbo.Reporting_Servers_AD_Global rsag2
WHERE rsag2.[Server] = dg.[Server]
AND rsag2.Domain = dg.Domain
AND rsag2.Date_ = DATEADD(MONTH,DATEDIFF(MONTH,0,dg.Date_),0)
) y
WHERE drc.Report = 'YES'
GROUP BY dg.[Server], dg.Domain, YEAR(dg.Date_), MONTH(dg.Date_)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 22, 2015 at 2:00 am
I'm currently doing a 7AM to 4PM shift.
I might be missing something
Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "x.Server" could not be bound.
Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "x.Domain" could not be bound.
October 22, 2015 at 2:06 am
Resender (10/22/2015)
I'm currently doing a 7AM to 4PM shift.I might be missing something
Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "x.Server" could not be bound.
Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "x.Domain" could not be bound.
FROM dbo.Diskspace_Global dg
INNER JOIN dbo.Diskspace_Region_Country drc
ON drc.[Server] = dg.[Server]
AND drc.Domain = dg.Domain
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 23, 2015 at 6:20 am
ok ounce sorry for the late response busy,busy,busy
Latest version
07:16
#Records 45193
Attached the actual execution plan.
October 23, 2015 at 6:24 am
Resender (10/23/2015)
ok ounce sorry for the late response busy,busy,busy
Latest version
07:16
#Records 45193
Attached the actual execution plan.
Is this new rowcount correct?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 23, 2015 at 6:40 am
That's the rowcount the query gave as for whether it's correct that will have to wait thill next week as I'm rather busy today again to check if they match.
October 23, 2015 at 7:16 am
Sure no worries.
None of your tables have a clustered index. One of them has a nonclustered index but it's not covering (hence the key lookup). When you find the time, that's where you should be looking first.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply