January 29, 2007 at 4:42 pm
Hi All,
We are running a new install of SQL 2000 Enterprise 32bit SP4 on Windows 2003 Enterprise 64bit. We are running on a 8 way, 16GB, RAID5 box. We are seeing a problem where it does not appear that sql is running correctly in a SMP mode. CPU utlization spikes/maxes on one CPU but not on all CPUs simultaneously. We have the box configured with /3gb and PAE. Is there anything we are missing, suggestions?
Thanks
January 29, 2007 at 4:56 pm
Sometimes SQL Server decides that the queries which are being sent are not best split into parallel operations.
That being said... you probably want to check/post settings for your CPU affinity, max degree of parallelism and cost threshold for parllelism.
January 30, 2007 at 6:06 am
most queries don't tend to generally get split into parallel plans, this is generally good as you'd not really want all your queries to be that costly. There's some ms blogs about how the optimiser works and it's in Kalen's inside SQL books.
I suggest you seriously don't try changing any settings on your server, I assume all settings are out of the box?
I'd be careful of the 3Gb switch, this limits lower memory quite considerably. SMP speads the load across the processors, not speads every query across all processors.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 31, 2007 at 7:37 am
I was following you right up to the /3Gb comment limiting lower memory. It is my recollection that only needs to be removed after exceeding 16Gb of RAM?
February 2, 2007 at 5:02 am
depending what your server does the 3gb switch can cause some serious problems, typical situations are extensive use of the sysmaint.exe, dts, sp_preparexml ( may have the actual name wrong ), openquery, oledb, extended procs, large ad hoc query plans, large stored procedure plans, use of extended stored procedures, custom dll's, other applications/services running on the physical server.
The 3gb switch limits lower memory.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 2, 2007 at 5:03 am
actually I'm pretty daft !! 64 bit doesn't use 3gb switch as it's a flat memory model.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply