April 26, 2006 at 1:05 pm
I'm installing sql2k5 on a new database server.
It has 4 processors, running 64 bit, and storage is a SAN.
I heard that a seperate Tempdb should be created
for each processor. Why and if i should how do I
create 4 tempdb, what size should I make them,
should they each be auto grow? Any help would be appreciated
April 26, 2006 at 1:11 pm
You cannot create four tempdb databases......are they perhaps talking about four files for the tempdb? And did whoever told you this include any logic for it? I've split out multiple files for tempdb in particular instances in the past, but never for the multiple processor situation, so am wondering what their logic may be....
April 26, 2006 at 1:19 pm
Hi scorpion
This is part of my problem. I remember something being mention in a newsletter but I can't find it. I do know it had was dealing with performance issues for sql2k5.
You've split out sperate files? what does that mean? I only thought that each db had 2 files (data and log files)
April 27, 2006 at 1:03 am
The creation of multiple tempdb files (not databases) be processor number of to do with the parallel processing nature of SQL Server enabling it access a thread per processor for the same job on each tempdb file. I've also seen the article, written by a SQL Server performance consultant working for Microsoft but can't lay my hands on it.
The only additional stipulation is that each file should be the same size, on separate physical drives (or separate spindles) and presized to prevent fragmentation.
This approach however is really only used in the highest activity setups.
I'll have a look around and if I find the article link I'll stick it up here.
April 27, 2006 at 1:05 am
The creation of multiple tempdb files (not databases) be processor number of to do with the parallel processing nature of SQL Server enabling it access a thread per processor for the same job on each tempdb file. I've also seen the article, written by a SQL Server performance consultant working for Microsoft but can't lay my hands on it.
The only additional stipulation is that each file should be the same size, on separate physical drives (or separate spindles) and presized to prevent fragmentation.
This approach however is really only used in the highest activity setups.
I'll have a look around and if I find the article link I'll stick it up here.
April 27, 2006 at 5:34 am
See the bottom of this article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
HTH,
Art
April 27, 2006 at 5:53 am
Hi Grasshopper:
The link you provided was great. it clear up the situation for me.
Thank you all for responding.
April 27, 2006 at 7:40 am
Here is another link that dicusses this option
http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=8be9d388-b354-429f-8c98-e45989a4bebe
There is also a webcast for SQL Server 2005 that reference this option. It is suggested maybe only 1 file per 2 processors is needed. Hyperthreaded processors count as 1 proc while dual core procs count as two.
See Part 3 of 11 - Installation http://www.microsoft.com/events/series/technetsqlserver2005.mspx
David Bird
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply