January 15, 2014 at 4:18 am
Hi
I have sized my 2 tempdb files at 20Gb each because I saw sql monitor returning a lot of sort warning and hash warning alerts - so I figured tempdb needed to be bigger to handle these sorting operations (it was originally 1 file sized @ approx 5Gb ).
I am still getting sort warning and hash alerts and when I run a query to see how much of the tempdb data file is being used, I see that the file has more or less 20gb free.
Could it be that when these sort operations are taking place (usually at 3am in the morning) that my Tempdb files are using a much more significant chunk of the allocated 20Gb and that when I run my query to see the size of the files in the morning because tempdb is not being used, I am seeing that the file looks nearly empty (ie 20Gb file only using 55Mb of space).
I want to know what is the max size these files grow to so I can size the file correctly and not waste disc space. As an extra bit of information I have ran a query to see if the tempdb has ever had to auto grow bigger than what I set it and I can verify that it has not - so I know 20Gb for each file is at least ok - I just want to maybe reduce it if 20Gb is too big.
By the way, when I use sql monitor to use the analysis tab, it just shows the 2 tempdb files at 40Gb - it doesn't show what proportion of the file has been used over time - Not sure if this is something that the tool can do??
January 15, 2014 at 4:21 am
Sort and hash warnings are in-memory operations that don't fit into memory and hence spill to TempDB, they're not an indication that TempDB is too small (that would be autogrow operations or out of space errors)
If your TempDB is not growing, then it's as large or larger than it needs to be. If it was too small, the files would have autogrown (or there would be out of space errors if the files couldn't grow)
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
January 15, 2014 at 4:38 am
So do I have to kind of just guess what size I need to allocate to the files then ? As I say the 2 20Gb files have never had to auto grow so I know I'm ok with that size. Maybe reduce them down to 15Gb, then 10Gb and see if any auto grow events occur.
When I originally made them bigger because of the hash and sort warnings I was under the impression these type of erros put a strain on tempdb, but I know now that I would have seen the autogrow events had tempdb been stuggling.
Thanks Gail.
🙂
January 15, 2014 at 4:43 am
Or, unless you're pressed for space, leave them at 20GB?
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
January 15, 2014 at 4:54 am
PearlJammer1 (1/15/2014)
HiI have sized my 2 tempdb files at 20Gb each because I saw sql monitor returning a lot of sort warning and hash warning alerts - so I figured tempdb needed to be bigger to handle these sorting operations (it was originally 1 file sized @ approx 5Gb ).
Find out what is generating the hash\sort warnings and fix it. Just extending TempDB is merely putting a sticky plaster over the real problem.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 15, 2014 at 6:43 am
Find out what is generating the hash\sort warnings and fix it. Just extending TempDB is merely putting a sticky plaster over the real problem.
This may warrant a seperate discussion....but have you any tips on fixing these kind of errors. Would I be best setting up a server side trace to capture these events ?
January 15, 2014 at 6:59 am
PearlJammer1 (1/15/2014)
Find out what is generating the hash\sort warnings and fix it. Just extending TempDB is merely putting a sticky plaster over the real problem.
Would I be best setting up a server side trace to capture these events ?
Yes!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply