January 19, 2010 at 4:23 am
Hello,
We have some performance problems and are now looking at our configuration of the SQL 2005. We have a 300GB database devided over 6 datafiles.
Enviroment:
OS : Windwos 2003 R2 64 bit Enterprise edition
CPU: 8
RAM : 64 GB
SQL Server : Microsoft SQL Server Enterprise Edition (64-bit)
Service Pack : SP2
Version : 9.00.4035.00
On this moment all CPU are assigned to SQL. We want to change this because we understood that when you have 6 datafiles 6 CPU should be sufficient for the performance. Now the question is which CPU should we preserve for the OS. Some say the first one some say the last one.
Any suggestions?
January 19, 2010 at 4:50 am
Don't play with the CPU affinity unless you have a really good reason. In most cases it should be left at default.
The number of data files is not linked to the number of CPUs except for TempDB. For user databases there's no such one data file per cpu recommendation. The number of CPUs that you need is a function of what you're running, not the number (or size) of the data files.
If you've having performance problems, first place to look is at your queries and indexes. Make sure both are completely optimal before playing with configuration
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
January 19, 2010 at 5:29 am
Hello Gail,
We have for the tempDb also 6 datafiles. The recommendation for using only 6 CPU's came from a tirth party which say's that microsoft recommend to use only 6 cpu's if you have 6 datafiles for the tempDb.
regards,
Bennie
January 19, 2010 at 5:35 am
As far as I know, it's 1 temp datafile per CPU Core....
I agree with Gail, leave your CPU Affinity to default, and rather set your memory usage if you need to.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
January 19, 2010 at 5:45 am
Henrico Bekker (1/19/2010)
As far as I know, it's 1 temp datafile per CPU Core....
Depending who you ask it's anything from 0.25 files/core to 1 file/core. But that's just the optimum number of files for the number of CPUs to avoid contention on the allocation pages in tempDB.
The recommendation does not work in reverse, if you have 6 files then you need 6 CPUs. The number of CPUs that you need is determined by the workload of the server, not the number of files of anything.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply