TempDB CPU

Question

TempDB CPU

Viewing 15 posts - 31 through 45 (of 46 total)

You must be logged in to reply to this topic. Login to reply

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 1

Hey there everyone been really busy. Got a brand new SQL Server and brand new SAN just configured done all the IOSTRESS Tools we had

200 IOPS now got 10,000 IOPS ....which is really good.

So now i run the same application on the database and i see the CPU go from 2% to 30% with one single Update (ERP system does some financial costing burden equations).

All on RAID 10 now. 14 Disks all for this.

When i run the Process all i see is about 50852 locks and they all sit in TEMPDB with EXT X ..

So bit nervous in doing the TEMPDB .

If i right click on TEMPDB i see tempdev and then templog

Do i just add another filename here call it tempdev2 and then templog2

I have 4 processes so do tempdev3 and tempdev4.

Im a gui person get bit scray running sql scripts unless i seen how to do it in GUI

Then i was going to re run process and see if the CPU comes down.

Can you help on the creation of TEMPDB and confirm before i do it.

Cheers

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 2

Ok i got brave thats what a test system all about.

Added

Alter database tempdb add file (name = tempdev2, filename = 'J:\MSSQL\DATA\MSSQL\Data\tempdb_db2.mdf',size = 60Mb,filegrowth = 20Mb),

( name = tempdev3, Filename = 'J:\MSSQL\DATA\MSSQL\Data\tempdb_db3.mdf',size = 60Mb,filegrowth = 20Mb),

( name = tempdev4, Filename = 'J:\MSSQL\DATA\MSSQL\Data\tempdb_db4.mdf',size = 60Mb,filegrowth = 20Mb)

I see it create the files when i right click on tempdb and just the one

log

Still CPU remains the same 25% for same process nothing gained.

How do i know if it using the other TempDB files....

Bob Fazio SSChampion Points: 10794 More actions · Answer 3

Few things. 25%? Seems that you are NOT parallelizing the query. Not sure why, but I would actually expect it to spike HIGHER! Has the time at least shortened? You got a MUCH faster box, and if you aren't getting any improvement the issue is somewhere else.

J: - This is on the RAID 10 drives Right?

Now specifically to the article and recommendation. The TEMPDB, multiple files issue reduced CONTENTION on the tempdb. If this is the only process running, it won't have contention. If you are pegging at 25% CPU, I think this might be something in the query that is causing your issue, not tempdb.

??? Is this a 4CPU system by the way? Could you have a function that is pegging the CPU? I need to go back and look at the SQL (did you post it yet? I don't recall seeing it)

Lastly! The recommendation was to create identical files WITHOUT auto-grow. If they grow and get to be different sizes, it changes the way the algorithm works and then everything gets sent to the biggest file KILLING the whole reason for the multiple files.

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 4

Well i suppose its all down to the code now that being generated from our ERP system.... as i done all above.

What is profiler CPU measured in milliseconds and what is determined high.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Dunno that, Tracey, but I'm still looking for the URL to the original article you sited...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bob Fazio SSChampion Points: 10794 More actions · Answer 6

Tracey,

You didn't setup the TEMPDB as specified(WITHOUT AUTOGROW). Did you enable the 1118 trace?

I agree with Jeff, you still have some options, but at least in this case, not doing it EXACTLY as instructed has no benefit at all. You might as well have not done anything.

$ CTomaszek SSCommitted Points: 1758 More actions · Answer 7

I would like to see the original article, too, feel free to post the URL any time, Tracey.

This is what I look like now because I'm holding my breath waiting for Tracey to post the URL -->

noeld SSC Guru Points: 96590 More actions · Answer 8

Is this the White Paper we are talking about ?

Working with tempdb in SQL Server 2005

* Noel

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 9

Here the post i was following:

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

Done all the tempdb with 4 files for 4 processors.

Guess what i now said to SQL run with 2 processors then with 4 processors the total CPU is at 25% and runs for 8 minutes regardless of 2 or 4 processors so the ERP system app must have something in its code .

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 10

Not sure how to add the T1118 flag .... i see start up parameters but not sure how to put it in

Bob Fazio SSChampion Points: 10794 More actions · Answer 11

First you need to make sure that all temp files are the EXACT same size. If any one is not the same size, then it will cause issues. That is why autogrow is supposed to be off. (I Realize the risk, but make it big enough)

Next.

To enable it without it enabled as a startup parameter

DBCC TRACEON(1118,-1)

That will enable it globally for the server.

Now to ensure it gets set on startup... You mentioned you found startup Parameters. Under SQL Server Manager Configuration, SQL Server, Advanced

Add at the end of the line you will see something like the following.

-dD:\Program Files\Microsoft SQL Server\SQL2005\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lE:\Program Files\Microsoft SQL Server\SQL2005\LOG\mastlog.ldf;-T1118;-T1204

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

Thanks...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 13

I will try the flag.

Thanks everyone.....for your guidance and help.

AlanBarber Ten Centuries Points: 1118 More actions · Answer 14

watch that parallelism. We were having issues with performance and found that turning it down, not up actually helped a ton. The defaults if I remember is 5 for the cost threshold and the max degree of parallelism is the number of cpus.

We would have issues where the server was constantly at 40-50% and pegging out at 100% and would shut down everything with timeouts.

did some tracing and found out that the issue was that just about every process was using parallelism and chocking the server.

We ended up bumping up the cost to 10 and max dop to 2 and cpu usage went down to to 10-20% with pegs in the 60-70% range.

MudLuck SSCrazy Points: 2301 More actions · Answer 15

I have also read the article and it makes since that at the OS level only one thread can look a file at a time.

There for if you have multiple CPU'S that can run multiple threads simultaneously that you create an I/O bottle neck if you only have one MDF file. I was however interested in the Log file.

Does SQL Server Use parallelism in the checkpoint command. In other words would I benefit from adding one or two more logs to the tempdb?

My inclination is no but couldn't find anything one way of the other what do you all think?