Post reply

Learning More About CPU/Physical IO

  • May 19, 2008 at 10:50 am

    #185798

    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

  • May 19, 2008 at 10:58 am

    #817318

    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.

  • May 19, 2008 at 11:34 am

    #817343

    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

  • May 19, 2008 at 1:35 pm

    #817423

    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?

  • May 20, 2008 at 6:55 am

    #817823

    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

Viewing 5 posts - 1 through 4 (of 4 total)

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