June 30, 2008 at 7:45 am
Hi,
I have a 64bit 2005 cluster (9.0.3228) with Win2003 Server R2, 32GB memory en 16 CPU (4 * QUAD core)
Database has 16 datafiles and 16 indexfiles, all datafiles are sized equally, same to indexfiles. 16 TempDB files
If I look at the processor usage, I notified that processor 0,4,8 and 12 is being used for about 60% and all the other processors < 10%
(seems like 1 core on each physical processor is doing the job)
I was expecting an equally processor usage, especially, because all files are equally sized and processor affinity en IO affinity is set to automatically for all processors. Does somebody have an explanation?
Wilfred
The best things in life are the simple things
June 30, 2008 at 7:49 am
There's no file-processor affinity for any database other than TempDB.
The recomendation I've seen for user databases is that they should have a max number of data files = 0.25 to 0.5* the number of CPUs.
See here for more info on workers and IO
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx
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
July 1, 2008 at 1:15 am
Make sure the data files are not compressed or in a compressed folder.
Geoff.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply