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

Can anyone help with this ?

Cookies on SQLServerCentral