March 9, 2010 at 8:46 am
for the last three weeks or so i've had perfmon running against most of our SQL servers and now i'm trying to code queries to develop reports against the data. Also trying to put as much data as possible into a single report so it won't be like the ITunes App Store.
here is my latest one. without the last OR clause of Pages Input/sec it returns the right data. when i added the last part i'm now getting % Committed Bytes In Use data returned that is less than 65.
trying to avoid having to use a UNION clause
with repl_latency_cte
as
(
select a.counterid, substring(a.machinename,3,20) as machinename, a.objectname, a.countername, a.instancename, convert(int, b.countervalue) as CounterValue, max(convert(datetime, substring(b.counterdatetime,1, 16))) as TimeRead
from counterdetails a inner join counterdata b
on a.counterid = b.counterid
where
--a.machinename = '\\sqlrepl'
a.objectname in ('Memory')
and a.countername in ('% Committed Bytes In Use', 'Pages/sec', 'Pages Input/sec')
and convert(datetime, substring(b.counterdatetime,1, 16)) > getdate() -7
group by a.machinename, a.instancename, a.counterid, a.objectname, a.countername, b.countervalue
)
select MachineName, ObjectName, CounterName, Countervalue, TimeRead
from repl_latency_cte
where countervalue in
(select countervalue from repl_latency_cte where objectname in ('Memory')
and countername in ('Pages/sec') and countervalue > 300)
or countervalue in (select countervalue from repl_latency_cte where objectname in ('Memory')
and countername in ('% Committed Bytes In Use') and countervalue > 65)
or countervalue in (select countervalue from repl_latency_cte where objectname in ('Memory')
and countername in ('Pages Input/sec') and countervalue > 20)
order by MachineName, countername, timeread desc
March 9, 2010 at 9:13 am
interesting, i tried to run the code below and i'm still getting all the extra data
with repl_latency_cte
as
(
select a.counterid, substring(a.machinename,3,20) as machinename, a.objectname, a.countername, a.instancename, convert(int, b.countervalue) as CounterValue, max(convert(datetime, substring(b.counterdatetime,1, 16))) as TimeRead
from counterdetails a inner join counterdata b
on a.counterid = b.counterid
where
--a.machinename = '\\sqlrepl'
a.objectname in ('Memory')
and a.countername in ('% Committed Bytes In Use', 'Pages/sec', 'Pages Input/sec')
and convert(datetime, substring(b.counterdatetime,1, 16)) > getdate() -7
group by a.machinename, a.instancename, a.counterid, a.objectname, a.countername, b.countervalue
)
select MachineName, ObjectName, CounterName, Countervalue, TimeRead
from repl_latency_cte
where countervalue in
(select countervalue from repl_latency_cte where objectname in ('Memory')
and countername in ('Pages Input/sec') and countervalue > 20)
March 9, 2010 at 12:21 pm
What do you get when you rewrite the select query as follows:
select MachineName, ObjectName, CounterName, Countervalue, TimeRead
from repl_latency_cte
where ObjectValue = 'Memory'
And CounterName = 'Pages Input/sec'
And countervalue > 20
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 9, 2010 at 12:49 pm
Not specific on the AND/OR problem, but why reinvent the wheel?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 9, 2010 at 1:06 pm
rewrote it to use Unions during the day and changed it on SSRS to use Report Parameters. I was hoping to email the data once a day as an alert that a server may be having resource issues, but decided it was too much and wrote the report to be on the website and for the hostname to be a parameter to look at data on a server by server basis.
we don't have SQL 2008 and don't want to run the dashboard on every single server. so i'm dumping the performance counter data into a single database and writing queries to alert us of any problems. need some data to show why we need 72GB of RAM on a server
declare @machinename varchar(50)
set @machinename = '\\sqlclust';
with repl_latency_cte
as
(
select a.counterid, substring(a.machinename,3,20) as machinename, a.objectname, a.countername, a.instancename, convert(int, b.countervalue) as CounterValue, convert(datetime, substring(b.counterdatetime,1, 16)) as TimeRead
from counterdetails a inner join counterdata b
on a.counterid = b.counterid
where
a.machinename = @machinename
and a.objectname in ('Memory', 'SQLServer:Buffer Manager')
and a.countername in ('% Committed Bytes In Use', 'Pages/sec', 'Pages Input/sec', 'Page Faults/sec', 'Pool Nonpaged Bytes', 'Buffer cache hit ratio')
and convert(datetime, substring(b.counterdatetime,1, 16)) > getdate() -7
group by a.machinename, a.instancename, a.counterid, a.objectname, a.countername, b.countervalue, b.counterdatetime
)
select MachineName, ObjectName, CounterName, instancename, Countervalue, TimeRead
from repl_latency_cte
where objectname in ('Memory')
and countername in ('Page Faults/sec') and countervalue > 2000
union
select MachineName, ObjectName, CounterName, instancename, Countervalue, TimeRead
from repl_latency_cte
where objectname in ('Memory')
and countername in ('% Committed Bytes In Use') and countervalue > 70
union
select MachineName, ObjectName, CounterName, instancename, Countervalue, TimeRead
from repl_latency_cte
where objectname in ('Memory')
and countername in ('Pages/sec')
and countervalue > 40
union
select MachineName, ObjectName, CounterName, instancename, Countervalue, TimeRead
from repl_latency_cte
where objectname in ('SQLServer:Buffer Manager')
and countername in ('Buffer cache hit ratio')
--and instancename not in ('model', 'msdb')
and countervalue < 90
order by timeread desc
March 9, 2010 at 1:13 pm
Thanks for posting back. I presume this query solves your unwanted data issue.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply