High CPU utilization

The CPU utilization on one of the servers is always around 50%, sometimes
it went to 100%, I turned on Profiler, did not find T-SQL statement using
long CPU time. Also check sp_who2 and find msdb is the most CPU user, the cumulated CPU time is 2742463. There were no other jobs running, just sqlserver agent-alert, which is default How to find the top cpu users? Thanks
Hi,
Can you check the taskmanager and confirm SQLSERVR.EXE is using more CPU? IF
SQL Server then
analyze the each connections connected to server using sp_who, sp_who2 and
dbcc inputbuffer commands.
You can try stopping the SQLSERVERAGENT service as well.
Thanks
Hari
MCDBA
"lhse" <lhse@.discussions.microsoft.com> wrote in message
news:A8BBBC49-4647-4206-B515-B4904E7553A2@.microsoft.com...
> The CPU utilization on one of the servers is always around 50%, sometimes
> it went to 100%, I turned on Profiler, did not find T-SQL statement using
> long CPU time. Also check sp_who2 and find msdb is the most CPU user, the
cumulated CPU time is 2742463. There were no other jobs running, just
sqlserver agent-alert, which is default How to find the top cpu users?
Thanks
>
|||You can run profiler and sort by cpu.. You can also insert the profiler data
into a SQL table and start doing select group by user, app, etc to find the
largest CPU user..
Remember that other maintenance type things go on, like checkpoint, lazy
writer, etc which can use CPU as well.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"lhse" <lhse@.discussions.microsoft.com> wrote in message
news:A8BBBC49-4647-4206-B515-B4904E7553A2@.microsoft.com...
> The CPU utilization on one of the servers is always around 50%, sometimes
> it went to 100%, I turned on Profiler, did not find T-SQL statement using
> long CPU time. Also check sp_who2 and find msdb is the most CPU user, the
cumulated CPU time is 2742463. There were no other jobs running, just
sqlserver agent-alert, which is default How to find the top cpu users?
Thanks
>

High CPU utilization

The CPU utilization on one of the servers is always around 50%, sometimes
it went to 100%, I turned on Profiler, did not find T-SQL statement using
long CPU time. Also check sp_who2 and find msdb is the most CPU user, the cu
mulated CPU time is 2742463. There were no other jobs running, just sqlserve
r agent-alert, which is default How to find the top cpu users? ThanksHi,
Can you check the taskmanager and confirm SQLSERVR.EXE is using more CPU? IF
SQL Server then
analyze the each connections connected to server using sp_who, sp_who2 and
dbcc inputbuffer commands.
You can try stopping the SQLSERVERAGENT service as well.
Thanks
Hari
MCDBA
"lhse" <lhse@.discussions.microsoft.com> wrote in message
news:A8BBBC49-4647-4206-B515-B4904E7553A2@.microsoft.com...
> The CPU utilization on one of the servers is always around 50%, sometimes
> it went to 100%, I turned on Profiler, did not find T-SQL statement using
> long CPU time. Also check sp_who2 and find msdb is the most CPU user, the
cumulated CPU time is 2742463. There were no other jobs running, just
sqlserver agent-alert, which is default How to find the top cpu users?
Thanks
>|||You can run profiler and sort by cpu.. You can also insert the profiler data
into a SQL table and start doing select group by user, app, etc to find the
largest CPU user..
Remember that other maintenance type things go on, like checkpoint, lazy
writer, etc which can use CPU as well.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"lhse" <lhse@.discussions.microsoft.com> wrote in message
news:A8BBBC49-4647-4206-B515-B4904E7553A2@.microsoft.com...
> The CPU utilization on one of the servers is always around 50%, sometimes
> it went to 100%, I turned on Profiler, did not find T-SQL statement using
> long CPU time. Also check sp_who2 and find msdb is the most CPU user, the
cumulated CPU time is 2742463. There were no other jobs running, just
sqlserver agent-alert, which is default How to find the top cpu users?
Thanks
>

Hiding series in a chart

Good afternoon
I have a hidiously complicated union select statement that i am feeding in
to a chart.
This creates two series and two data fields, some of these combinations dont
hold usefull data and so i would like to hide them. e.g
Series 1 and data field 1 = good data
Series 1 and data field 2 = crap data
Series 2 and data field 1 = crap data
Series 2 and data field 2 = good data
I cant find a way of hiding the series-datafield combinations that i dont
need.
The crap data that i dont want to show is always 0's but by filtering i
remove a whole data field not just part of it.
Please help
Thanks
Steve Dcan you get needed fields from your existing query?
something like this:
select Series1, Field1 from ( your complicated query ) as
ExistingQuery
where Series1 = 'whatever'
union
select Series2, Field2 as Field1 from ( your complicated query) as
ExistingQuery
where Series2 = 'whatever'
you might add extra integer field to your existing query
when Series 1 then 1
Series 2 = 2
and use it in where statement above
Steve Dearman wrote:
> Good afternoon
> I have a hidiously complicated union select statement that i am feeding in
> to a chart.
> This creates two series and two data fields, some of these combinations dont
> hold usefull data and so i would like to hide them. e.g
> Series 1 and data field 1 = good data
> Series 1 and data field 2 = crap data
> Series 2 and data field 1 = crap data
> Series 2 and data field 2 = good data
> I cant find a way of hiding the series-datafield combinations that i dont
> need.
> The crap data that i dont want to show is always 0's but by filtering i
> remove a whole data field not just part of it.
> Please help
> Thanks
> Steve D

hideous join statement

just came across this JOIN stmt

wtf?!

LEFT OUTER JOIN (SELECT DealID, MAX(PersonIDInitiator) PersonIDInitiator, MAX(PersonIDExecutor) PersonIDExecutor, MAX(PersonIDSalesPerson) PersonIDSalesPerson FROM (SELECT DealID, CASE WHEN RoleID = 1 THEN PersonID END PersonIDInitiator, CASE WHEN RoleID = 2 THEN PersonID END PersonIDExecutor, CASE WHEN RoleID = 3 THEN PersonID END PersonIDSalesPerson FROM dbo.DealRole WHERE RoleID BETWEEN 1 AND 3) dr GROUP BY DealID) Roles ON Roles.DealID = D.DealID

ugh!

can someone translate? why are there selects in the join?

You are dealing with what are called "derived tables". Please take a look at the way I have restructured your query:

LEFT OUTER JOIN
( SELECT DealID,
MAX(PersonIDInitiator) PersonIDInitiator,
MAX(PersonIDExecutor) PersonIDExecutor,
MAX(PersonIDSalesPerson) PersonIDSalesPerson
FROM ( SELECT DealID,
CASE WHEN RoleID = 1 THEN PersonID END PersonIDInitiator,
CASE WHEN RoleID = 2 THEN PersonID END PersonIDExecutor,
CASE WHEN RoleID = 3 THEN PersonID END PersonIDSalesPerson
FROM dbo.DealRole
WHERE RoleID BETWEEN 1 AND 3
) dr
GROUP BY DealID
) Roles
ON Roles.DealID = D.DealID

There are two different derived tables in your join. Derived tables are select expressions that can be substituted for and used for tables similar to the way that views can be substituted for and used in the place of tables. I colored the inner derived table in green. Note the inner derived table has "dr" for an alias that I colored brown.

The outer derived table includes the inner derived table so even though I have colored the inner derived table in green, it is still part of the outer derived table. Notice that the outer derived table has "Roles" for an alias and is colored blue.

I notieced by your "ugh" response to the derived table that you are initial repulsed by the use. This is not uncommon; however, derived tables are an integral part of many advanced queries and I would certainly recommend that you take time to learn the use.

Also, SQL Server 2005 introduced the use of "Common Table Expressions" -- CTEs. I would suggest that you might also want to give the CTE beasties a look

|||ok, so bearing in mind that the [deal role].[deal role id] appears to be a number incremented each time the person id on a deal is changed, then it would appear the code is wrong

i.e. it is returning the highest personid, rather than the person id of the highest role id

so if person A was id = 5 was the original initiator, but then person B with person id of 2 is then the originator, it would wrongly show A instead of B as 'current/latest initiator'|||Yes, it sounds like that logic problem exists. Are you using SQL 2005 or SQL Server 2000?|||sql2k5 sp2|||This looks like a job for the ROW_NUMBER function; please give a look to ROW_NUMBER() and OVER in books online while I cook up an example with what you have given.|||

Adolf:

Here is an example of code using (1) a derived table and (2) the ROW_NUMBER() function with the OVER, PARTITION BY and ORDER BY syntax:

Code Snippet

declare @.dealRole table
( drId integer,
DealID integer,
roleId integer,
PersonId integer,
eventDt datetime
)
insert into @.dealRole
select 1, 11, 1, 51, '3/4/7' union all
select 2, 11, 2, 57, '3/5/7' union all
select 3, 11, 3, 54, '3/8/7' union all
select 4, 11, 1, 47, '4/3/7' union all
select 5, 11, 2, 51, '4/3/7' union all
select 6, 11, 1, 44, '4/5/7' union all
select 7, 12, 1, 52, '4/8/7' union all
select 8, 11, 3, 55, '4/8/7' union all
select 9, 11, 4, 14, '4/15/7'

select dealId,
RoleId,
PersonId,
eventDt
from ( select dealId,
roleId,
row_number() over
( partition by dealId, roleId
order by eventDt desc, drId desc
) as seQ,
PersonId,
eventDt
from @.dealRole
where roleId between 1 and 3
) a
where seq = 1

/*
dealId RoleId PersonId eventDt
-- -- -- --
11 1 44 2007-04-05 00:00:00.000
11 2 51 2007-04-03 00:00:00.000
11 3 55 2007-04-08 00:00:00.000
12 1 52 2007-04-08 00:00:00.000
*/