Hello,
I'm ing to tuning the SQL 2000 SP2. Because a lot of stored procedures
are using tempdb heavily, I learned that spliting tempdb into equal size of
files will improve the performance. Can anyone send me the step by step on
how to do this? It is much appreciated.

Sarah

Sarah,

Here is what I used. Make sure to change the file paths to fit your
situation. This set 4 data files for tempdb. Be sure to test, as I
am having an issue now that I'm having trouble figuring out if it is
related.

David Hay


----- Move Data File for TempDB
USE master
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'e:\mssql\data\tempdb.mdf',
SIZE = 100MB, FILEGROWTH = 100MB) -- New Location
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'd:\mssql\data\templog.ldf',
SIZE = 100MB, FILEGROWTH = 100MB) -- New Location
GO

ALTER DATABASE TempDB
ADD FILE
(NAME = tempdev2, FILENAME = 'f:\mssql\data\tempdev2.ndf', SIZE =
100MB, FILEGROWTH = 100MB) -- New Location

ALTER DATABASE TempDB
ADD FILE
(NAME = tempdev3, FILENAME = 'g:\mssql\data\tempdev3.ndf', SIZE =
100MB, FILEGROWTH = 100MB) -- New Location

ALTER DATABASE TempDB
ADD FILE
(NAME = tempdev4, FILENAME = 'h:\mssql\data\tempdev4.ndf', SIZE =
100MB, FILEGROWTH = 100MB) -- New Location


On Sep 9, 12:36pm, sg <s...@discussions.microsoft.com> wrote:
> Hello,
> I'm ing to tuning the SQL 2000 SP2. Because a lot of stored procedures
> are using tempdb heavily, I learned that spliting tempdb into equal size of
> files will improve the performance. Can anyone send me the step by step on
> how to do this? It is much appreciated.
>
> Sarah

Oh, and in case you need to revert...

use tempdb
go
dbcc shrinkfile(tempdev2, emptyfile)
go
dbcc shrinkfile(tempdev3, emptyfile)
go
dbcc shrinkfile(tempdev4, emptyfile)
go

--wait till this completes then run the following

alter database tempdb
remove file tempdev2
go
alter database tempdb
remove file tempdev3
go
alter database tempdb
remove file tempdev4
go


David Hay

Hi David,

You are more than ful. Thanks a lot.
one more question, if network admin is telling me that they have 2 cpus
(quad core), can I split the tempdb into 8 files which one for each cpu or 7
files, reserve cpu 0 for OS?

Thanks a lot,
Sarah

"David Hay" wrote:

> Oh, and in case you need to revert...
>
> use tempdb
> go
> dbcc shrinkfile(tempdev2, emptyfile)
> go
> dbcc shrinkfile(tempdev3, emptyfile)
> go
> dbcc shrinkfile(tempdev4, emptyfile)
> go
>
> --wait till this completes then run the following
>
> alter database tempdb
> remove file tempdev2
> go
> alter database tempdb
> remove file tempdev3
> go
> alter database tempdb
> remove file tempdev4
> go
>
>
> David Hay
>

Hi Sarah,

Having multiple tempdb files can but it can be useless too. It can
with contention so you must check if contention is what you are experiencing.

Read this KB: http://support.microsoft.com/kb/328551

Rubén Garrigós
Solid Quality Mentors

"sg" wrote:

> Hello,
> I'm ing to tuning the SQL 2000 SP2. Because a lot of stored procedures
> are using tempdb heavily, I learned that spliting tempdb into equal size of
> files will improve the performance. Can anyone send me the step by step on
> how to do this? It is much appreciated.
>
> Sarah

Ruben,

Thanks for your response. I'm guessing it is the issue, I will do the test
before going to production.

Thanks,
sarah

"Rubén Garrigós" wrote:

> Hi Sarah,
>
> Having multiple tempdb files can but it can be useless too. It can
> with contention so you must check if contention is what you are experiencing.
>
> Read this KB: http://support.microsoft.com/kb/328551
>
> Rubén Garrigós
> Solid Quality Mentors
>
> "sg" wrote:
>
> > Hello,
> > I'm ing to tuning the SQL 2000 SP2. Because a lot of stored procedures
> > are using tempdb heavily, I learned that spliting tempdb into equal size of
> > files will improve the performance. Can anyone send me the step by step on
> > how to do this? It is much appreciated.
> >
> > Sarah

Sarah,

If you have 2 quad core CPU's then it would be 8 data files plus the
log file according to the KB article Ruben pointed you to.

Note that After I implemented this, I am having an issue with one
datawarehouse job that went from 30 minutes to 11 hours, but many
other things have sped up considerably. Just prepare to roll back if
need be!

Good luck!

David Hay

My Solution:

DECLARE @Count AS INT, @Name AS VARCHAR(300)
SELECT @Count = 32

WHILE @Count > 0
BEGIN
SET @Count = @Count-1
SET @Name = RIGHT('00' + CAST(@Count AS VARCHAR),2)

EXEC ('ALTER DATABASE TempDB
ADD FILE
(NAME = tempdb' + @Name+ ' , FILENAME = ''J:\tempdb' + @Name +
'.ndf'', SIZE = 5000MB, FILEGROWTH = 300MB)'
)
END

"sg" wrote:

> Ruben,
>
> Thanks for your response. I'm guessing it is the issue, I will do the test
> before going to production.
>
> Thanks,
> sarah
>
> "Rubén Garrigós" wrote:
>
> > Hi Sarah,
> >
> > Having multiple tempdb files can but it can be useless too. It can
> > with contention so you must check if contention is what you are experiencing.
> >
> > Read this KB: http://support.microsoft.com/kb/328551
> >
> > Rubén Garrigós
> > Solid Quality Mentors
> >
> > "sg" wrote:
> >
> > > Hello,
> > > I'm ing to tuning the SQL 2000 SP2. Because a lot of stored procedures
> > > are using tempdb heavily, I learned that spliting tempdb into equal size of
> > > files will improve the performance. Can anyone send me the step by step on
> > > how to do this? It is much appreciated.
> > >
> > > Sarah

You're going to add 32 files? All on the same J: drive? Interesting
approach. Curious how much you think you will gain from this.




On 10/26/08 9:51 AM, in article
B9673277-966E-4CF9-A7AF-72A4B0EC75BE@microsoft.com, "jc"
<jc@discussions.microsoft.com> wrote:

> My Solution:
>
> DECLARE @Count AS INT, @Name AS VARCHAR(300)
> SELECT @Count = 32
>
> WHILE @Count > 0
> BEGIN
> SET @Count = @Count-1
> SET @Name = RIGHT('00' + CAST(@Count AS VARCHAR),2)
>
> EXEC ('ALTER DATABASE TempDB
> ADD FILE
> (NAME = tempdb' + @Name+ ' , FILENAME = ''J:\tempdb' + @Name +
> '.ndf'', SIZE = 5000MB, FILEGROWTH = 300MB)'
> )
> END
>
> "sg" wrote:
>
>> Ruben,
>>
>> Thanks for your response. I'm guessing it is the issue, I will do the test
>> before going to production.
>>
>> Thanks,
>> sarah
>>
>> "Rubén Garrigós" wrote:
>>
>>> Hi Sarah,
>>>
>>> Having multiple tempdb files can but it can be useless too. It can
>>> with contention so you must check if contention is what you are
>>> experiencing.
>>>
>>> Read this KB: http://support.microsoft.com/kb/328551
>>>
>>> Rubén Garrigós
>>> Solid Quality Mentors
>>>
>>> "sg" wrote:
>>>
>>>> Hello,
>>>> I'm ing to tuning the SQL 2000 SP2. Because a lot of stored procedures
>>>> are using tempdb heavily, I learned that spliting tempdb into equal size of
>>>> files will improve the performance. Can anyone send me the step by step on
>>>> how to do this? It is much appreciated.
>>>>
>>>> Sarah