SQL Server Performance Forum – Threads Archive

SQL 2005 on 64bit Multiprocessor Server

Hi 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

---

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

---

]]>