January 13, 2014 at 9:50 pm
Comments posted to this topic are about the item TEMPDB Configuration per vCPU
January 14, 2014 at 7:40 am
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" 😉
January 14, 2014 at 8:14 am
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
January 14, 2014 at 8:30 am
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.
January 14, 2014 at 12:07 pm
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" 😉
January 15, 2014 at 10:44 am
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.
January 15, 2014 at 11:41 am
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" 😉
January 15, 2014 at 11:42 am
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" 😉
January 17, 2014 at 9:03 am
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.
January 17, 2014 at 10:06 am
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" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply