Hi

I'm setting up a new Windows 2008 SQL Server 64 bit Enterprise Edt.. To add
files to the temdb, I used SQL Server Management Studio, and opened tempdb
properties. I went to the "Files" node, and added new files to the tempdb.
Alle files have the same size, and same settings for Autogrowth, same
location as well.
Closed properties windows by clicking OK, and the files where generated.
Any addition steps that should be taken?

/MRW

If you have multiple disks at your disposal, an additional benefit of having
multiple tempdb files is that you can place them on separate drives. If you
put 8 or 16 tempdb files all in the same location, you may get some gain in
CPU performance, but your I/O is not going to get any better... in fact it
may get inversely worse as your CPU improves.

"Aaron Bertrand" wrote:

logfiles.

/MRW

I thought that it was best to not allow autogrow when you have multiple
tempdb data files? This was because of the way that the different files
would get filled.

Chris

"MRWildiac" <MRWildiac (AT) discussions (DOT) microsoft.com> wrote

You can't disable autogrow on all of them; your system grinds to a halt if
you use up all the space for some reason. And you don't want autogrow on
only one of them, because it then becomes the primary source and will likely
just keep growing and growing and your other files go unused. So the best
combination IMHO:

- allow autogrow on all
- monitor the differences and alert whenever they are not equal
- proactively adjust all of the files to the same size when one has grown

This shouldn't happen too often, unless you start them all at the default (8
MB I believe?). If you size them right up front, you shouldn't have to
worry about growth, but disabling autogrowth means disaster when the
unforeseen happens.

A



On 9/17/09 11:19 AM, in article #NTyOp6NKHA.4028 (AT) TK2MSFTNGP05 (DOT) phx.gbl,
"Chris Wood" <anonymous (AT) microsoft (DOT) com> wrote:

Is the tempdb log file with the tempdb data files or on a separate array?
You want to separate the log files from the data files even with tempdb.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"MRWildiac" <MRWildiac (AT) discussions (DOT) microsoft.com> wrote

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4434 (20090917) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

Hi MRWildiac,

From what I've read, the main performance optimisation steps you can
take for the tempDB is to ensure you have the same number of tempDB
data files as you have CPU cores in the host machine. Then have these
located on a dedicated tempDB disk.

As Aaron pointed out, there are gains to be had if you have spare
disks available to spread these tempDB data files across but in my
experience this is generally overkill for the performance improvement
you might get from this, and spare disks would better be spent
splitting the other data files from the log files, or even high disk
IO databases onto their own disk.

Regards,


Sia
www.universql.com