June 25, 2017 at 9:10 pm
Why I could not able shrink Tempdb even free space available in Tempdb.
Please find attached screenshot .
June 25, 2017 at 9:48 pm
Adding to the above
I have added additional .ndf(Size 4MB) file to tempdb to release contention on .mdf (22GB Size)file of tempdb few days back.
It looks like SQL server not using .ndf file for sorting operations the size .ndf file still 4MB only.
Am I need enable any additional options on SQL server to use .ndf file for sorting operations?
June 26, 2017 at 12:14 am
Hi,
did you configure the Traceflag 1117?
https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/
Kind regards,
Andreas
June 26, 2017 at 9:12 am
Keep in mind that SQL Server uses a proportional fill algorithm for allocation.
Allocations to files in a filegroup will be roughly proportional to the amount of free space in each of the files.
This means that if you have two data files in tempdb, one of which is 22 GB (and probably has many GB of free space), and the other of which is 4 MB (and thus can't have more than a few MB of free space, by definition), an overwhelming proportion of allocations will occur in the first file.
You will need to size your files so that they have an equal amount of free space to really see any benefit from the additional file with respect to allocation contention (latch contention on PFS, GAM, and SGAM pages). On that note, I'd make sure you really have that problem before you put a lot of effort into addressing it.
On the shrink issue, it is quite common for shrinking tempdb on a live system to be incredibly difficult.There are number of things you can try to get the shrink to work, like clearing the various system caches, but doing that has some obvious downsides. What's more, there's still no guarantee the shrink will work afterwards.
Provided you have regular maintenance windows, or can get a brief maintenance window for this purpose, the simplest is just to use ALTER DATABASE...MODIFY FILE to set the files to an appropriate equal size, and then restart the service in your next maintenance window.
Cheers!
June 26, 2017 at 10:40 am
Tempdb file(s) should be EXACTLY the same size with EXACTLY the same growth increments. It is all but mandatory to have trace flags 1117 and 1118 enabled globally on every SQL Server too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 26, 2017 at 10:54 am
TheSQLGuru - Monday, June 26, 2017 10:40 AMTempdb file(s) should be EXACTLY the same size with EXACTLY the same growth increments. It is all but mandatory to have trace flags 1117 and 1118 enabled globally on every SQL Server too.
We have some data warehouse quires which are spending most execution time for sorting operations on tempdb.
I need some valuable inputs which will improve performance of tempdb.
June 26, 2017 at 10:57 am
krishna83 - Monday, June 26, 2017 10:54 AMTheSQLGuru - Monday, June 26, 2017 10:40 AMTempdb file(s) should be EXACTLY the same size with EXACTLY the same growth increments. It is all but mandatory to have trace flags 1117 and 1118 enabled globally on every SQL Server too.We have some data warehouse quires which are spending most execution time for sorting operations on tempdb.
I need some valuable inputs which will improve performance of tempdb.
How to maintain all tempdb files same. At least tempdb not allowing to shrink mdf filrs
June 26, 2017 at 11:18 am
You can find lots of resources on the internet about tempdb best practices.
Here's one: https://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/
June 27, 2017 at 5:15 am
Or - you can look into those queries and eliminate the factors causing a lot of sorting in tempdb.
There must be a big area of possible improvements in table design, indexing strategy and/or quality of T-SQL itself.
_____________
Code for TallyGenerator
June 27, 2017 at 6:16 am
Sergiy - Tuesday, June 27, 2017 5:15 AMAND - you can look into those queries and eliminate the factors causing a lot of sorting in tempdb.There must be a big area of possible improvements in table design, indexing strategy and/or quality of T-SQL itself.
There, fixed that for you. 😉
June 27, 2017 at 8:26 pm
TheSQLGuru - Monday, June 26, 2017 10:40 AMTempdb file(s) should be EXACTLY the same size with EXACTLY the same growth increments. It is all but mandatory to have trace flags 1117 and 1118 enabled globally on every SQL Server too.
Gosh, no. TF 1118 is certainly a god-send and should always be enabled (and that's the default in 2016, IIRC). But, (haven't tried it... going only by what others have published) if you have multiple files in normal databases that SHOULD be different sizes (partitioned tables, for example), then until 2016, you have to remember that TF 1117 is a GLOBAL setting and it can cause a huge amount of unnecessary wasted space. If you size your TempDB files correctly ( I've had 8 at 2GB each on my big prod box for about 4 years now) and they've always stayed the same size. If I were to turn on TF 1117, then the 89 different files that are in a fairly large partitioned table would all be the same size when the smallest only needs to be 500MB and the largest needs to be 13GB with everything possible in between.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2017 at 8:37 pm
krishna83 - Monday, June 26, 2017 10:54 AMTheSQLGuru - Monday, June 26, 2017 10:40 AMTempdb file(s) should be EXACTLY the same size with EXACTLY the same growth increments. It is all but mandatory to have trace flags 1117 and 1118 enabled globally on every SQL Server too.We have some data warehouse quires which are spending most execution time for sorting operations on tempdb.
I need some valuable inputs which will improve performance of tempdb.
The most valuable input I can give you is that it's not likely a TempDB problem. It's most likely a code problem where the code needs to be rewritten to be more efficient and to avoid unnecessary sorting. Yes, proper configuration of TempDB and all the tricks with hardware, RAID type, multiple files, etc, etc will all help some but they will all pale in comparison to fixing code to run better.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply