SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TEMPDB Configuration per vCPU


TEMPDB Configuration per vCPU

Author
Message
Paulo A. Nascimento
Paulo A. Nascimento
SSC Eights!
SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)

Group: General Forum Members
Points: 846 Visits: 207
Comments posted to this topic are about the item TEMPDB Configuration per vCPU
Perry Whittle
Perry Whittle
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35941 Visits: 17540
growing data files in 64MB chunks is a really bad idea. Ideally create them to the size required including a little for contingency measures. If you must autogrow files and you have followed the best practice for enabling "Perform volume maintenance tasks" local policy for the sql service account, grow the files in at least 512MB or 1GB increments.

Personally, don't touch TEMPDB unless you can confirm you're hitting allocation issues.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Paulo A. Nascimento
Paulo A. Nascimento
SSC Eights!
SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)

Group: General Forum Members
Points: 846 Visits: 207
Perry Whittle (1/14/2014)
growing data files in 64MB chunks is a really bad idea. Ideally create them to the size required including a little for contingency measures. If you must autogrow files and you have followed the best practice for enabling "Perform volume maintenance tasks" local policy for the sql service account, grow the files in at least 512MB or 1GB increments.

Personally, don't touch TEMPDB unless you can confirm you're hitting allocation issues.


Hi Perry

Shown numeric values, along with file paths, are just "illustrative". Anyway I'm glad you brought this to our attention. Regarding "Perform volume maintenace tasks" please see article http://tinyurl.com/osyg7au

Required size, contigency, safety factors, as you probably know already are always kind of subjective.

Regards,

--
Paulo
Nadrek
Nadrek
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3138 Visits: 2735
Good job referencing http://support.microsoft.com/kb/2154845/en-us in the script - I would have added some information about it in the article as well. "You observe severe blocking when the SQL Server is experiencing heavy load. When you examine the Dynamic Management Views [sys.dm_exec_request or sys.dm_os_waiting_tasks], you observe that these requests or tasks are waiting for tempdb resources. You will notice that the wait type and wait resource point to LATCH waits on pages in tempdb. These pages might be of the format 2:1:1, 2:1:3, etc."

Perry's right - even for an illustration, 64MB autogrowth is horrifically small for any implementation I can see that would actually be experiencing the contention.

For adding multiple tempdb files, if you're not going to support spreading them out over multiple spindles, I'd at least get the current tempdb location out of sys.master_files or similar and put the new files in the same place.

Note that in your code, the script points to E: and the "After" portion shows D:, which seems inconsistent.

Again, regarding the MS support article, I'd like to see more scripting to determining this is actually the case, and whether or not you might need to keep adding files in sets of 4 if the latch waits on pages in tempdb continues. Those who need help with adding files to tempdb have a good chance of needing help with this as well.
Perry Whittle
Perry Whittle
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35941 Visits: 17540
Nadrek (1/14/2014)
For adding multiple tempdb files, if you're not going to support spreading them out over multiple spindles, I'd at least get the current tempdb location out of sys.master_files or similar and put the new files in the same place.

This is how the article gets misunderstood. Its not recommending to put the files on separate disks they're all in the same filegroup. You're simply creating multiple files on the same disk to alleviate the meta page contention.

Spreading files across multiple disks is designed to relieve storage I\O contention, different things altogether.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Paulo A. Nascimento
Paulo A. Nascimento
SSC Eights!
SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)

Group: General Forum Members
Points: 846 Visits: 207
Nadrek (1/14/2014)
...
Perry's right - even for an illustration, 64MB autogrowth is horrifically small for any implementation I can see that would actually be experiencing the contention.

Well if tempdb database was, say, 640 MB in size, then that would not seem strange to me :-). 64 MB is exactly exactly 10% of the size of tempdb.

Nadrek (1/14/2014)
...
For adding multiple tempdb files, if you're not going to support spreading them out over multiple spindles, I'd at least get the current tempdb location out of sys.master_files or similar and put the new files in the same place.


Spindles are only important when dealing with traditional mechanical disks, imho. Working with modern storage with built-in automatic data tiering (SATA <--> SAS <--> SSD disks), physical reallocation of secondary data files of tempdb is less important, again, just my opinion. A typical SAS 15k rpm disk will give around 200 IOPS (dedicated spindle), but modern storage SSD doesn't care about spindles and even provides IOPS around 15.000 IOPS or more, depending on the hardware.

Nadrek (1/14/2014)
...
Note that in your code, the script points to E: and the "After" portion shows D:, which seems inconsistent.

True, it is inconsistent, because, if I recall well, I started this testing on my laptop and then collected final results from other system.

Thanks for your feedback.
Perry Whittle
Perry Whittle
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35941 Visits: 17540
Paulo A. Nascimento (1/15/2014)
Well if tempdb database was, say, 640 MB in size, then that would not seem strange to me :-). 64 MB is exactly exactly 10% of the size of tempdb.

Ah the old 10% theory I wondered when this little scallywag would rear its head. Bad idea, period.

Growing in small increments not only stalls writes due to the continual file growth operations but the fragmentation at the physical file level becomes an issue too.

Ideally you don't want tempdb to grow at all but if it does then certainly not in small increments.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Perry Whittle
Perry Whittle
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35941 Visits: 17540
Paulo A. Nascimento (1/15/2014)
Well if tempdb database was, say, 640 MB in size, then that would not seem strange to me :-). 64 MB is exactly exactly 10% of the size of tempdb.

Ah the old 10% theory I wondered when this little scallywag would rear its head. Bad idea, period.

Growing in small increments not only stalls writes due to the continual file growth operations but the fragmentation at the physical file level becomes an issue too.

Ideally you don't want tempdb to grow if it does then certainly not in small increments.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Paulo A. Nascimento
Paulo A. Nascimento
SSC Eights!
SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)

Group: General Forum Members
Points: 846 Visits: 207
Perry Whittle (1/15/2014)
Paulo A. Nascimento (1/15/2014)
Well if tempdb database was, say, 640 MB in size, then that would not seem strange to me :-). 64 MB is exactly exactly 10% of the size of tempdb.

Ah the old 10% theory I wondered when this little scallywag would rear its head. Bad idea, period.

Growing in small increments not only stalls writes due to the continual file growth operations but the fragmentation at the physical file level becomes an issue too.

Ideally you don't want tempdb to grow if it does then certainly not in small increments.


The 10% theory is an old and good one, as long one doesn't take it literally.
As already said, data file growth is not an issue if Instant File Initialization is used (available since W2K3) as the growth operations is instantaneous with no performance penalties. The concern should always be put in not using percentual file growth.
Perry Whittle
Perry Whittle
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35941 Visits: 17540
Paulo
The 10% theory is an old and good one, as long one doesn't take it literally.
As already said, data file growth is not an issue if Instant File Initialization is used (available since W2K3)

The 10% theory is lame. Instant file initialisation is even more reason to grow in sensible size chunks, anything 512mb or over.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search