Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Learning More About CPU/Physical IO


Learning More About CPU/Physical IO

Author
Message
jan michael woo cerna-336610
jan michael woo cerna-336610
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 179
Hi;

I'm rephrasing the question below because I also stumped.

Where can I find white papers or technical documents regarding SQL Server 2005 Activity Monitor CPU and Physical IO.

I would like to learn more about who and how a client may consume CPU utilization running queries etc. and its impact.

src: http://msdn.microsoft.com/en-us/library/ms178520.aspx

Thanks,
Jan
sharadov
sharadov
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 776
You need to pick up a book on SQL server performance tuning.
Also there are several articles here on sql server central, also check out blogs etc.
Books online is a great resource as well.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
I'd say the first thing to do is try running a few queries with "set statistics io on" and "set statistics time on". Look at the Messages tab in Management Studio when you do that.

Here are a couple of examples:


set statistics time on
set statistics io on

select getdate()




set statistics time on
set statistics io on

declare @Date datetime

;with CTE (Number) as
(select row_number() over (order by t1.object_id)
from sys.all_objects t1
cross join sys.all_objects t2)
select @Date = dateadd(minute, number, '1/1/1900')
from CTE



You'll see messages about how much CPU time was used, how much total time was used, how many scans and reads were done any tables involved, etc.

Check the statistics on some of the more common queries in the databases you administer (assuming you have some).

Once you've seen those numbers, look up any parts that you don't understand specifically. Or ask about them here.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
jan michael woo cerna-336610
jan michael woo cerna-336610
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 179
Thanks... However I already used the Activity Monitor in SQL Management Studi 2005.
My problem here is that how would I know if a certain application is consuming more CPU, Phsyical I/O, etc.. in connecting SQL basing with Acitivity Monitor. Are there any basis or recommended values?
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
Since applications are so specific and individual, there really isn't a way to say what "recommended" IO levels are. The best measure is: Is the cost of running the program less than the benefit? That's going to be highly subjective.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search