TEMPDB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

TEMPDB

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-09-16 : 07:11:54

I have a brand new 14 disks just for SQL Server three Drives all dedicated. All RAID 10.
Performed IOSTRESS tests on Old and New 200 IOPS old and now 10,000 IOPS fantastic i thought.

Now got SQL Server with 4 CPU even better.

Created additional files
tempdev
templog
tempdev2
tempdev3
tempdev4
all same size

Ran one application within our ERP system.
CPU starts at 0% and goes to 25% and stays there until it finishes. Captured profiler and yes i can see the code its doing some massive inserts and updates and build Z_ tables and truncates etc.

I look in TEMPDB and i see probrably 200 entries for EXT X in here.

Now with or without the additional TEMP files same thing 25%.

Stuck - cannot change code of ERP .

All indexes are updated all statistics .

How do i fix or is there a fix. We are on build 8.00.2039 SQL 2000 SP4
Is there a later build for SQL

I see this T1118 (But im not sure if this is for SQL 7 or not).

Any ideas or suggestions

Kristen
Test

22859 Posts

Posted - 2007-09-16 : 07:43:23

Have you basically just left the architecture the same, but split TEMPDB into 4 files?

Has TEMPDB been extended from the initial size it was created as?

Are the 4 TEMPDB Files on separate physical drive [arrays]? (otherwise little point having separate files!)

If so:

Are MDF and LDF on separate physical drive [arrays]?

Also TEMPDB on a separate drive?

Those are the first things that will give performance improvement, if not already in place.

Kristen

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-09-16 : 08:23:54

The SAN has been split into three drives for me.
4 for the MDF
4 for the LDF
4 for all Temp files
(All 4 new tempdev files for Temp are on its separate drives away from MDF and LDF)

Has TEMPDB been extended from the initial size it was created as? (Usually around 4 gig on production ) just put them all to 60 MB for now.

Does not grew when run the application just see all EXT in TEMPDB

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-09-16 : 08:24:53

I keep reading about the T1118 but is this just for SQL 2005 i cannot see anything about this for SQL 2000 SP 4 Our build of sql is 8.00.2039

Kristen
Test

22859 Posts

Posted - 2007-09-16 : 09:51:03

"4 for all Temp files"

Is this a single logical drive, or 4 separate drives?

If its a single logical drive there is no point (AFAIK) having 4 files for TEMPDB. For that to work well you need 4 separate physical drives.

"4 for the MDF
4 for the LDF"

I assume this is 4 drives arranged as a single device. So its just "a large device"

. Is this the same as you had before, or is it different? (i.e. as distinct from the changes for TEMPDB)

Kristen

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-09-16 : 15:15:15

The SAN gave me three letters M N O and M is made up of (4 drives) N (4 drives) O (4 drives).

Before just had all mdf and ldf sitting on first 4 drives of SAN (which is where operating system is) and RAID 5.

So for the TEMPDB to work as 4 separate files do i need to put it on M and N and O...?
But i have the 4 disks (but all lumped into one).

With or without the TEMPDB as one or four i still see EXT in TEMPDB when i run my processes and 25% increase in CPU (Thats one one process running) ... When i have other processes applications running i see it go to 100%....

So san has no effect ....so now trying to figure out how to relieve the TEMPDB issue.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-16 : 17:51:27

Did you trace in profiler to see what kind of processes hit the tempdb?

Kristen
Test

22859 Posts

Posted - 2007-09-17 : 04:13:13

You have 3 drives (m, N and O) and four TEMPDB files?

If so I think you should use only 3, one on each drive.

However, if these SAME drives are used for MDF / LDF Data / Log files I don't think there is any point.

You should have MDF and LDF on different drives.

If you only have 3 drives then I think your best configuration is:

M : Data
N : Logs
O : TempDB

I suspect your best location for backups is O: too.

If you are getting extension in TEMPDB you may want to configure the minimum size to be what you normally see the TEMPDB grow to. That way when you restart SQL Server TEMPDB will be created at its normal size, and won't have to extend several times.

Kristen

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-09-17 : 08:23:07

M : Data
N : Logs
O : TempDB

(Thats what we have 4 disks for each and separate just like above).

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-17 : 08:27:43

POST SP4 hotfix build 2187

http://support.microsoft.com/default.aspx?scid=KB;%5bLN%5d;916287

sql 2000 builds
http://www.aspfaq.com/sql2000Builds.asp

sql 2005 builds
http://www.sqlservercentral.com/columnists/sjones/2960.asp

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-09-17 : 08:38:14

So the SQL Server 2000 build 8.00.2187 is hot fix for SQL Server 2000 sp4.
Let me download and try it.

Thanks

Kristen
Test

22859 Posts

Posted - 2007-09-17 : 11:37:47

"Thats what we have 4 disks for each and separate just like above"

Sorry, but I don't understand.

If you only have 3 drive letters available you don't really have a configuration suitable for splitting TEMPDB into 4 files.

Kristen

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-09-17 : 12:20:22

Three Letters (But 4 drives in each)...All 12 disks

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-17 : 12:34:55

Okay, let's clarify something.

You have 3 drive arrays, each array is made up of 4 disk drives setup in a raid 1+0 config and the arrays are presented to the OS as M (Log), N (Data), O (TempDB).

SQL Server sees what the OS is presented which is the three drive arrays.

You are trying to created multiple file tempdb data files four to be exact on the O drive array.

Correct?

Seems like you are trying to improve performance in your tempDB.
Take a look at this:

http://support.microsoft.com/kb/328551

http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html

http://msdn2.microsoft.com/en-us/library/ms175527.aspx

http://sqljunkies.com/WebLog/odds_and_ends/archive/2006/06/21/21985.aspx

Kristen
Test

22859 Posts

Posted - 2007-09-17 : 12:40:46

"Three Letters (But 4 drives in each)...All 12 disks"

OK. The number of drives is irrelevant to SQL Server. As far as SQL Server is concerned you have just three devices, and thus I don't see that you will gain anything by splitting TEMPDB into 4 separate files.

The best you can do is:

M : Data
N : Logs
O : TempDB

and that assumes that the O/S is on a different drive (presumably C:)

Have you got any other drives left locally? That might be a suitable place for TEMPDB. It doesn't need backing up or any of the other possible benefits of a SAN that I can think of.

No doubt someone will correct me if I'm wrong in any of those opinions.

Kristen

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-09-17 : 13:57:02

Yes that it
You have 3 drive arrays, each array is made up of 4 disk drives setup in a raid 1+0 config and the arrays are presented to the OS as M (Log), N (Data), O (TempDB).

SQL Server sees what the OS is presented which is the three drive arrays.

os Yes is on C:

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-09-17 : 14:46:31

The flag -T1118 can you add this to SQL Startup for SQL 2000
Just go to properties and add -T1118 in parameter ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-17 : 15:03:59

What Kristen is mentioning about drives and multiple files for tempdb is not true. You do not need to split these tempdb files onto different drives to see performance gains.

We had Microsoft out here about 1-2 years back to help us out with performance problems. They had us split our tempdb into multiple files on the same drive. We saw big performance gains.

It is very important that the file sizes be the exact same size.

Here is the thread where I discuss it a little and plus give some links.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-09-17 : 15:16:03

"They had us split our tempdb into multiple files on the same drive. We saw big performance gains."

Thanks for clarifying that Tara. I'm struggling to think how the retrieve-from-disk can benefit from multiple files on the same drive, but I'm happy to hear that it works.

"It is very important that the file sizes be the exact same size"

Is it OK if TEMPDB auto-extends, or do you have to be careful to pre-size all the TEMPDB files so that TEMPDB never gets extended?

"Here is the thread where I discuss it a little and plus give some links."

The link is missing

This one perhaps?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65459

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-17 : 15:27:16

Thanks for posting the link. I had copied into my clipboard, but then neglected to put it in my post.

You have to turn off autogrow in order for the algorithm to work, so no it is not okay if tempdb auto-extends.

We only had this type of tempdb architecture setup on one SQL Server 2000 instance that was experiencing a ton of performance problems due to tempdb (according to in-depth analysis performed by MS).

This same system was converted to 2005 a while back and we did not put this architecture in place. We also are not experiencing performance problems on this system since moving to 2005, but that may be because we are slowly converting the customers to an iSeries platform rather than SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-09-19 : 19:36:31

Ok just so i understand
TEMPDB 4 Processors so 4 files...but put these in the 3 (4 disks) i have.

M (Log), N (Data), O (TempDB).
Put the tempdb files in M and in N and say two in O

I have all four in O .

Thanks

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources