SQL Server Performance

SQL 2005 on 64bit Multiprocessor Server

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by frank.uray, Apr 5, 2006.

  1. frank.uray New Member


    I have the following configuration:
    - Server with 8 AMD Opteron Processors
    - 24 GB Memory
    - Windows Server 2003 64 bit
    - SQL 2005 64 bit
    - EMC Storage

    The main instance is defined to use 7 Processors and
    has about 10 databases.

    The problem is:
    When I run a "big" Query (DISTINCT or GROUP BY on a Table with about 100 Fields
    and something like 1 million rows) the Server uses all 7 Processors.
    So far so good but while this query is running all other databases
    on this instance are almost not accessible (Timeout ...).

    Does anybody has some ideas how to configure the system
    to make it more useful ... ??

    Thanks for any comment.

    Best regards
    Frank Uray
  2. joechang New Member

    do you want a big query to come back fast
    or do you want best response for multiple users?

    for 1st, let it run on all procs,
    for 2, limit max degree of parallelism to 1

    or choose something in between

    learn how to use sp_configure,
    set the Show Advanced Options on

    anyways, i would not restrict SQL to 7 procs, as the OS is unlikely to use just the unused proc,
    so let SQL use all 8

Share This Page