Can anyone help with this ?

Question

Post reply

Can anyone help with this ?

Paul Pennock

Ten Centuries

Points: 1091
More actions
June 16, 2005 at 8:36 am

#109547

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

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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?

Paul Pennock Ten Centuries Points: 1091 More actions · Answer 2

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!!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

set showplan_text on

go

--query goes here

go

set showplan_text oFF

Paul Pennock Ten Centuries Points: 1091 More actions · Answer 4

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 ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

June 16, 2005 at 9:22 am

#566150

Just paste the text here.

Paul Pennock Ten Centuries Points: 1091 More actions · Answer 6

|--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]))

| | | | | | |--Filter(WHERE.[CounterValue]=[Expr1028]))

| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[a].[CounterDateTime], .[CounterID]))

| | | | | | | | |--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])))

| | | | | | | | | |--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]))

| | | | | | | | |--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])))

| | | | | | | |--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]))

| | | | | | |--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])))

| | | | | |--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]))

| | | | |--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'))

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

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........)

Paul Pennock Ten Centuries Points: 1091 More actions · Answer 8

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

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.

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 10

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

Kindest Regards,

Vasc