Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

TempDB best practices - Doubt Expand / Collapse
Author
Message
Posted Friday, September 20, 2013 12:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:22 AM
Points: 61, Visits: 409
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:4 Hyperthread Ratio:1 Physical CPU Count: 4 Physical 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?
Post #1496996
Posted Sunday, September 22, 2013 6:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:22 AM
Points: 61, Visits: 409
someone to volunteer?
Post #1497202
Posted Sunday, September 22, 2013 12:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 36,983, Visits: 31,508
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497217
Posted Sunday, September 22, 2013 2:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:22 AM
Points: 61, Visits: 409
and about the size of them? how can i calculate that?
Post #1497230
Posted Sunday, September 22, 2013 2:38 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 42,818, Visits: 35,943
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 2008, MVP
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

Post #1497234
Posted Sunday, September 22, 2013 5:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 36,983, Visits: 31,508
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497246
Posted Sunday, September 22, 2013 6:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:22 AM
Points: 61, Visits: 409
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...)
Post #1497247
Posted Sunday, September 22, 2013 6:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:22 AM
Points: 61, Visits: 409
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?
Post #1497248
Posted Sunday, September 22, 2013 11:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 36,983, Visits: 31,508
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497263
Posted Monday, September 23, 2013 5:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:22 AM
Points: 61, Visits: 409
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?
Post #1497331
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse