Post reply

Performance problem- SQL Server 2005 x64 on AMD64 platform

  • February 11, 2009 at 3:01 am

    #199795

    Hi all,

    I have the following configuration on a test environment server:

    AMD 4-way dual core, so 8 procs

    10 GB (8GB reserverd to SQL Server)

    Microsoft Windows Enterprise Edition R2 X64

    MSCS

    SQL Server 2005 X64 failover cluster

    performance flags:

    number of connections: ~25

    paging: ~0

    disk queue: ~0

    cpu utilization: in general is below 10%, but I from time to time: processor X is running at 100%

    I have 4-10 runnable spids with a wait type of SOS_SCHEDULER_YIELD.

    I tried everything:

    - max degree of parallelism - 0,8,4 - no results

    - query hints

    I can't get rid of SOS_SCHEDULER_YIELD wait type.

    Any clues?

  • February 12, 2009 at 3:16 pm

    #943233

    slavao should be able to help you out

    http://blogs.msdn.com/slavao/

  • February 13, 2009 at 4:35 am

    #943440

    Hi,

    Have you identified what spid is utilizing CPU during 100% utilization?

    Thanks,

    Phillip Cox

  • February 13, 2009 at 5:03 am

    #943451

    Dan Puiu (2/11/2009)

    I tried everything:

    I can't get rid of SOS_SCHEDULER_YIELD wait type.

    Any clues?

    Have you tried optimising the queries involved? (and I don't mean applying query hints)

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 13, 2009 at 5:09 pm

    #943996

    Have you tried 1 for the MAXDOP? 0,4 and 8 won't buy you much if you have queries running concurrently that SQL server wants to run parallel.

    Anything else on the box that may be utilizing the CPUs?

  • February 14, 2009 at 2:03 pm

    #944153

    Kinda unrelated but you may want to check out the boot.ini parameter /USEPMTIMER (http://support.microsoft.com/kb/895980) if your on AMD x64.

  • February 16, 2009 at 2:03 am

    #944360

    Is the o/s fully patched and up to date?

    ditto sql server?

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • February 16, 2009 at 2:04 am

    #944362

    btw. I'd have 2gb for o/s as totally inadequate - 4Gb maybe, ideally 6GB

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • February 18, 2009 at 11:45 am

    #946180

    Hi all,

    So on another machine the same query that has that wait type runs in 2 mins and on this new machine is running for aprox. 2 hours;

    The query is not complicated, it has an agregation and a subquery(if it's necessary I can post here);

    The ideea is that after some optimization on that query i get results in 10 secs;

    So it's realy strange

  • February 19, 2009 at 6:06 am

    #946586

    colin Leversuch-Roberts (2/16/2009)

    btw. I'd have 2gb for o/s as totally inadequate - 4Gb maybe, ideally 6GB

    why do you sai that? in my case(2GBs reserved for OS) I have total of 300MB free all the time, no matter the workload on the server

Viewing 10 posts - 1 through 9 (of 9 total)

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