TempDB best practices - Doubt

Question

Post reply

TempDB best practices - Doubt

LOVER OF SQL

SSCommitted

Points: 1900
More actions
September 20, 2013 at 12:28 pm

#99854

Hello guys,
I have two doubt:
-- 1 script:
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], physical_memory_in_bytes/1024 AS [Physical Memory (MB)], sqlserver_start_time
FROM sys.dm_os_sys_info;
return: Logical CPU Count:4Hyperthread Ratio:1Physical CPU Count: 4Physical Memory (MB): 8388088
what is my doubt?
1. I need put more 3 datafile for my tempdb? because i have one (mdf and ldf) - default.
2. About the Initial Size and autogrow?? how can i define this options?

Viewing 15 posts - 1 through 15 (of 18 total)

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

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 1

LOVER OF SQL

SSCommitted

Points: 1900

September 22, 2013 at 6:52 am

#1652189

someone to volunteer?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

LOVER OF SQL (9/22/2013)
someone to volunteer?

Sure. Please see the following links.

https://www.google.com/#q=paul+randal+number+of+tempdb+files

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

--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)

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 3

and about the size of them? how can i calculate that?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

How big does TempDB get? Take that size, divide by the number of files, add a bit extra for safety.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

LOVER OF SQL (9/22/2013)
and about the size of them? how can i calculate that?

To be honest, I thought my post might inspire you to hit Google so that you can see many different recommendations. 😉

Start off with what Gail suggested. Make sure that growth is in MB and not % and that it's not set too low. Here's a couple of the better links for what that's all about.

https://www.google.com/#q=kimberly+tripp+vlf

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

--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)

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 6

Yes... but lets suppose that I am installing at the moment and tempdb is small.. (2,3,4mb), in other words, is not a big... how can i calculate this (for each datafile then I add...)

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 7

yes, but the problem at the moment is because my tempdb is thin.... small.. (that is new project - SQL Server)... Hardware, OS, Database... and my tempdb is small.. you understand?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

LOVER OF SQL (9/22/2013)
yes, but the problem at the moment is because my tempdb is thin.... small.. (that is new project - SQL Server)... Hardware, OS, Database... and my tempdb is small.. you understand?

It's a bit of a swag, then. If it were me, I'd set it to 1,000MB with 500MB growth for the MDF file and 500MB with 500MB growth for the LDF file and then monitor it as the system grows.

--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)

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 9

1,000MB equals = 1GB? or 1MB?

and if I have 4 processor (physical) I need put more 3 datafile? because exist mdf (default).

You sad: "500MB with 500MB growth for the LDF file and then monitor it as the system grows. " but, is not necessary I am add ldf files all right? only mdf for processor..

Jeff Moden,

How can i allow/endrose your answer in this topic?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

LOVER OF SQL (9/23/2013)
1,000MB equals = 1GB? or 1MB?
and if I have 4 processor (physical) I need put more 3 datafile? because exist mdf (default).
You sad: "500MB with 500MB growth for the LDF file and then monitor it as the system grows. " but, is not necessary I am add ldf files all right? only mdf for processor..
Jeff Moden,
How can i allow/endrose your answer in this topic?

Do not add more LDF files. LDF files operate in a serial cyclic manner and there's no benefit.

And obviously 1,000MB <> 1MB. 1,000MB = 1GB. If you have 4 processors, you could start 4 MDF files at 500MB with 500MB growth. Since you have no history on TempDB usage on this system, that's also a swag.

--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)

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 11

" If you have 4 processors, you could start 4 MDF" (in the case, i'll have 5 datafiles all right? (4 for ndf) and (1 mdf-default), ok?

and about: "files at 500MB with 500MB growth."

size 500MB or 1GB for ndfs?

500MB growth - all right!

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

LOVER OF SQL (9/23/2013)
" If you have 4 processors, you could start 4 MDF" (in the case, i'll have 5 datafiles all right? (4 for ndf) and (1 mdf-default), ok?
and about: "files at 500MB with 500MB growth."
size 500MB or 1GB for ndfs?
500MB growth - all right!

Like I said, it's a total swag because you have no history on the system. If you can easily tolerate 4GB of MDF/LDF, the 1GB each for the MDF and the 3 NDFs would be fine. It will certainly delay any autogrowth and preallocating TempDB to that size will certainly not hurt anything unless you're extremely squeezed for disk space.

--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)

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 13

I agree with Jeff. It's a guess, perhaps WAG, if you have no history.

2GB (4 x 500MB) or 4GB (4 x 1GB) is a decent guess if you can spare the space. I'd also do a 500MB or 1GB log and monitor.

5GB is not a lot of space these days and even if it's overkill, it prevents issues. I would monitor usage and watch it over weeks/months.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

LOVER OF SQL (9/23/2013)
" If you have 4 processors, you could start 4 MDF" (in the case, i'll have 5 datafiles all right? (4 for ndf) and (1 mdf-default), ok?

No. 4 data files.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass