June 16, 2005 at 8:36 am
I'm trying to monitor databases and am using Perfmon to collect the data and then write it out to the tables generated by the set 'Log File Type= SQL Database'. the collection of data is okay but the format not so good so I've written some SQL to collect the cpu usage stats and produce a single row per collection rather than having the 5 entries on 5 rows. The only problem is the query is extremely slow and has been written by me!!. I know it can be made better - so who wants a go?
select distinct a.CounterDateTime as ' Time' ,
a.CounterValue as 'Total % Used',
b.CounterValue as 'P1 % Used',
c.CounterValue as 'P2 % Used',
d.CounterValue as 'P3 % Used',
e.CounterValue as 'P4 % Used'
from dbo.CounterData a,
dbo.CounterData b,
dbo.CounterData c,
dbo.CounterData d,
dbo.CounterData e,
dbo.counterdetails f
where a.countervalue = (select a1.countervalue
from dbo.Counterdata a1,
dbo.Counterdetails a2
where a1.counterid = a2.counterid
and a1.counterID=A.CounterId
and a.counterdatetime=a1.counterdatetime
and a2.instancename='_Total'
and a2.objectname='processor'
and a2.countername='% Processor Time'
and a2.Machinename='\\notnt32')
and b.countervalue = (select b1.countervalue
from dbo.Counterdata b1,
dbo.Counterdetails b2
where b1.counterid = b2.counterid
and b1.counterID=B.CounterId
and a.counterdatetime=b1.counterdatetime
and b2.instancename='0'
and b2.objectname='processor'
and b2.countername='% Processor Time'
and b2.Machinename='\\notnt32')
and c.countervalue = (select c1.countervalue
from dbo.Counterdata c1,
dbo.Counterdetails c2
where c1.counterid = c2.counterid
and c1.counterID=c.CounterId
and a.counterdatetime=c1.counterdatetime
and c2.instancename='1'
and c2.objectname='processor'
and c2.countername='% Processor Time'
and c2.Machinename='\\notnt32')
and d.countervalue = (select d1.countervalue
from dbo.Counterdata d1,
dbo.Counterdetails d2
where d1.counterid = d2.counterid
and d1.counterID=d.CounterId
and a.counterdatetime=d1.counterdatetime
and d2.instancename='2'
and d2.objectname='processor'
and d2.countername='% Processor Time'
and d2.Machinename='\\notnt32')
and e.countervalue = (select e1.countervalue
from dbo.Counterdata e1,
dbo.Counterdetails e2
where e1.counterid = e2.counterid
and e1.counterID=e.CounterId
and a.counterdatetime=e1.counterdatetime
and e2.instancename='2'
and e2.objectname='processor'
and e2.countername='% Processor Time'
and e2.Machinename='\\notnt32')
and f.objectname='processor'
and f.countername='% Processor Time'
and f.Machinename='\\notnt32'
order by a.counterdatetime
June 16, 2005 at 8:40 am
Can you use the real inner join syntaxe. You might have a big fat series of cross joins in that query.
Can you post the execution plan?
June 16, 2005 at 9:13 am
How do I do this ? If I drag the doc into the window It opens it and I lose this window. You'll have to bear with me but I'm new to this!!
June 16, 2005 at 9:16 am
set showplan_text on
go
--query goes here
go
set showplan_text oFF
June 16, 2005 at 9:19 am
I've done that!! do you just want me to cut and paste it into this dialogue or is there some way of attaching the saved excel file ?
June 16, 2005 at 9:22 am
Just paste the text here.
June 16, 2005 at 9:23 am
|--Stream Aggregate(GROUP BY[a].[CounterDateTime], [a].[CounterValue], .[CounterValue], [c].[CounterValue], [d].[CounterValue], [e].[CounterValue]))
|--Nested Loops(Inner Join)
|--Sort(ORDER BY[a].[CounterDateTime] ASC, [a].[CounterValue] ASC, .[CounterValue] ASC, [c].[CounterValue] ASC, [d].[CounterValue] ASC, [e].[CounterValue] ASC))
| |--Filter(WHERE[e].[CounterValue]=[Expr1022]))
| |--Nested Loops(Inner Join, OUTER REFERENCES[a].[CounterDateTime], [e].[CounterID]))
| |--Nested Loops(Inner Join)
| | |--Filter(WHERE[d].[CounterValue]=[Expr1024]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES[a].[CounterDateTime], [d].[CounterID]))
| | | |--Nested Loops(Inner Join)
| | | | |--Filter(WHERE[c].[CounterValue]=[Expr1026]))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[a].[CounterDateTime], [c].[CounterID]))
| | | | | |--Nested Loops(Inner Join)
| | | | | | |--Filter(WHERE.[CounterValue]=[Expr1028]))
| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[a].[CounterDateTime], .[CounterID]))
| | | | | | | |--Nested Loops(Inner Join)
| | | | | | | | |--Filter(WHERE[a].[CounterValue]=[Expr1030]))
| | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[a].[CounterID], [a].[CounterDateTime]))
| | | | | | | | | |--Clustered Index Scan(OBJECT[monitor].[dbo].[CounterData].[PK__CounterData__4E88ABD4] AS [a]))
| | | | | | | | | |--Hash Match(Cache, HASH[a].[CounterID], [a].[CounterDateTime]), RESIDUAL[a].[CounterID]=[a].[CounterID] AND [a].[CounterDateTime]=[a].[CounterDateTime]))
| | | | | | | | | |--Assert(WHEREIf ([Expr1029]>1) then 0 else NULL))
| | | | | | | | | |--Stream Aggregate(DEFINE[Expr1029]=Count(*), [Expr1030]=ANY([a1].[CounterValue])))
| | | | | | | | | |--Nested Loops(Inner Join)
| | | | | | | | | |--Filter(WHERE([a2].[ObjectName]='processor' AND [a2].[MachineName]='\\notnt32') AND [a2].[InstanceName]='_Total'))
| | | | | | | | | | |--Clustered Index Seek(OBJECT[monitor].[dbo].[CounterDetails].[PK__CounterDetails__4CA06362] AS [a2]), SEEK[a2].[CounterID]=[a].[CounterID]
| | | | | | | | | |--Index Spool(SEEK[a1].[CounterID]=[a].[CounterID] AND [a1].[CounterDateTime]=[a].[CounterDateTime]))
| | | | | | | | | |--Clustered Index Scan(OBJECT[monitor].[dbo].[CounterData].[PK__CounterData__4E88ABD4] AS [a1]))
| | | | | | | | |--Table Spool
| | | | | | | | |--Index Scan(OBJECT[monitor].[dbo].[CounterData].[CounterData_Index_1] AS ))
| | | | | | | |--Hash Match(Cache, HASH[a].[CounterDateTime], .[CounterID]), RESIDUAL[a].[CounterDateTime]=[a].[CounterDateTime] AND .[CounterID]=.[CounterID]))
| | | | | | | |--Assert(WHEREIf ([Expr1027]>1) then 0 else NULL))
| | | | | | | |--Stream Aggregate(DEFINE[Expr1027]=Count(*), [Expr1028]=ANY([b1].[CounterValue])))
| | | | | | | |--Nested Loops(Inner Join)
| | | | | | | |--Filter(WHERE([b2].[ObjectName]='processor' AND [b2].[MachineName]='\\notnt32') AND [b2].[InstanceName]='0'))
| | | | | | | | |--Clustered Index Seek(OBJECT[monitor].[dbo].[CounterDetails].[PK__CounterDetails__4CA06362] AS [b2]), SEEK[b2].[CounterID]=.[CounterID]), WHERE[b2]
| | | | | | | |--Index Spool(SEEK[b1].[CounterID]=.[CounterID] AND [b1].[CounterDateTime]=[a].[CounterDateTime]))
| | | | | | | |--Clustered Index Scan(OBJECT[monitor].[dbo].[CounterData].[PK__CounterData__4E88ABD4] AS [b1]))
| | | | | | |--Table Spool
| | | | | | |--Index Scan(OBJECT[monitor].[dbo].[CounterData].[CounterData_Index_1] AS [c]))
| | | | | |--Hash Match(Cache, HASH[a].[CounterDateTime], [c].[CounterID]), RESIDUAL[a].[CounterDateTime]=[a].[CounterDateTime] AND [c].[CounterID]=[c].[CounterID]))
| | | | | |--Assert(WHEREIf ([Expr1025]>1) then 0 else NULL))
| | | | | |--Stream Aggregate(DEFINE[Expr1025]=Count(*), [Expr1026]=ANY([c1].[CounterValue])))
| | | | | |--Nested Loops(Inner Join)
| | | | | |--Filter(WHERE([c2].[ObjectName]='processor' AND [c2].[MachineName]='\\notnt32') AND [c2].[InstanceName]='1'))
| | | | | | |--Clustered Index Seek(OBJECT[monitor].[dbo].[CounterDetails].[PK__CounterDetails__4CA06362] AS [c2]), SEEK[c2].[CounterID]=[c].[CounterID]), WHERE[c2].[CounterName]=
| | | | | |--Index Spool(SEEK[c1].[CounterID]=[c].[CounterID] AND [c1].[CounterDateTime]=[a].[CounterDateTime]))
| | | | | |--Clustered Index Scan(OBJECT[monitor].[dbo].[CounterData].[PK__CounterData__4E88ABD4] AS [c1]))
| | | | |--Table Spool
| | | | |--Index Scan(OBJECT[monitor].[dbo].[CounterData].[CounterData_Index_1] AS [d]))
| | | |--Hash Match(Cache, HASH[a].[CounterDateTime], [d].[CounterID]), RESIDUAL[a].[CounterDateTime]=[a].[CounterDateTime] AND [d].[CounterID]=[d].[CounterID]))
| | | |--Assert(WHEREIf ([Expr1023]>1) then 0 else NULL))
| | | |--Stream Aggregate(DEFINE[Expr1023]=Count(*), [Expr1024]=ANY([d1].[CounterValue])))
| | | |--Nested Loops(Inner Join)
| | | |--Filter(WHERE([d2].[ObjectName]='processor' AND [d2].[MachineName]='\\notnt32') AND [d2].[InstanceName]='2'))
| | | | |--Clustered Index Seek(OBJECT[monitor].[dbo].[CounterDetails].[PK__CounterDetails__4CA06362] AS [d2]), SEEK[d2].[CounterID]=[d].[CounterID]), WHERE[d2].[CounterName]='% Processor Ti
| | | |--Index Spool(SEEK[d1].[CounterID]=[d].[CounterID] AND [d1].[CounterDateTime]=[a].[CounterDateTime]))
| | | |--Clustered Index Scan(OBJECT[monitor].[dbo].[CounterData].[PK__CounterData__4E88ABD4] AS [d1]))
| | |--Table Spool
| | |--Index Scan(OBJECT[monitor].[dbo].[CounterData].[CounterData_Index_1] AS [e]))
| |--Hash Match(Cache, HASH[a].[CounterDateTime], [e].[CounterID]), RESIDUAL[a].[CounterDateTime]=[a].[CounterDateTime] AND [e].[CounterID]=[e].[CounterID]))
| |--Assert(WHEREIf ([Expr1021]>1) then 0 else NULL))
| |--Stream Aggregate(DEFINE[Expr1021]=Count(*), [Expr1022]=ANY([e1].[CounterValue])))
| |--Nested Loops(Inner Join)
| |--Filter(WHERE([e2].[ObjectName]='processor' AND [e2].[MachineName]='\\notnt32') AND [e2].[InstanceName]='2'))
| | |--Clustered Index Seek(OBJECT[monitor].[dbo].[CounterDetails].[PK__CounterDetails__4CA06362] AS [e2]), SEEK[e2].[CounterID]=[e].[CounterID]), WHERE[e2].[CounterName]='% Processor Time') ORDERED FO
| |--Index Spool(SEEK[e1].[CounterID]=[e].[CounterID] AND [e1].[CounterDateTime]=[a].[CounterDateTime]))
| |--Clustered Index Scan(OBJECT[monitor].[dbo].[CounterData].[PK__CounterData__4E88ABD4] AS [e1]))
|--Row Count Spool
|--Filter(WHERE[f].[CounterName]='% Processor Time' AND [f].[MachineName]='\\notnt32'))
|--Clustered Index Scan(OBJECT[monitor].[dbo].[CounterDetails].[PK__CounterDetails__4CA06362] AS [f]), WHERE[f].[ObjectName]='processor'))
June 16, 2005 at 9:31 am
You have 11 scans in this query... no wonder why it's so slow.
Can you rewrite the query so it uses ansi92 standards?
(Select 1 from a inner join b on a.id = b.id........)
June 16, 2005 at 10:10 am
I tried and got the first one:
select b.CounterDateTime as ' Time' ,
b.CounterValue as 'Total % Used'
from
dbo.CounterDetails a Inner join
dbo.Counterdata b on a.counterID=B.CounterId
where a.objectname='processor'
and a.countername='% Processor Time'
and a.Machinename='\\notnt32'
but as soon as I tried to put a second inner join in I wasn't getting the data I needed - I don't think I was specifying the correct conditions to do this.
June 16, 2005 at 10:27 am
This is one case where the wizards of enterprise manager can be usefull... Just create a new view and use the wizard to connect the tables (if you don't already have foreign keys). Then you'll just have to add the where conditions.
June 16, 2005 at 1:51 pm
From what I read this query MIGHT be what you need
SELECT CounterID, CounterDateTime,MAX(C1) AS 'Total % Used',MAX(C2) AS 'P1 % Used',
MAX(C3) AS 'P2 % Used', MAX(C4) AS 'P3 % Used', MAX(C5) AS 'P4 % Used'
FROM
(SELECT CounterDateTime , C1.CounterID AS CounterID
CASE WHEN C2.IntsanceName='_Total' CounterValue Else 0 END C1,
CASE WHEN C2.IntsanceName='0' CounterValue Else 0 END C2,
CASE WHEN C2.IntsanceName='1' CounterValue Else 0 END C3,
CASE WHEN C2.IntsanceName='2' CounterValue Else 0 END C4,
CASE WHEN C2.IntsanceName='2' CounterValue Else 0 END C5
FROM dbo.Counterdata c1,dbo.Counterdetails c2
WHERE c1.counterid = c2.counterid
and (c2.instancename='1' OR c2.instancename='_Total' OR c2.instancename='0' OR c2.InstanceName='1' OR c2.InstanceName='2')
and c2.objectname='processor'
and c2.countername='% Processor Time'
and c2.Machinename='\\notnt32')
GROUP BY CounterID, CounterDateTime
Order By CounterDateTime
If you post the tables and sample data with the result you might be able to get more help
Vasc
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply