not really, as temp space always depends on the queries themselves and the number of concurrent queries performed. for example, a sort typically needs a little more space than the source data. a good starting point is sum up the sizes of the databases on the server and set the TempDB to 10-15% of the sum, but you may need more depending on your queries. note: in a production environment, typically you would dedicate a disk to the TempDB.
Is there a way to estimate how large tempdb data (and/or log) files will need in size or growth to accommodate prior to query run?
Hello, trying to run a query but getting the following error message: “[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Could not allocate space for object 'dbo.SORT temporary run storage: 142562294562816' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. Unable to create extract” Is there a way to estimate how large tempdb data (and/or log) files will need in size or growth to accommodate prior to query run...(or while it's running)? Thanks in advance.
4 answers
Sort by: Most helpful
-
-
Erland Sommarskog 116.5K Reputation points MVP
2024-01-10T22:27:21.57+00:00 It's certainly not a trivial task. There may some possibilities if it is only a matter of filling up temp tables. But here you have appear to have a sort operator that spills to disk, badly. Since I don't know the query, I don't know what alternatives the optimizer has. It may think that the number rows is small or that the row size is small, and it can make the sort with a reasonable amount of memory, but it is mistaken on the estimates. If the optmizer can't get it right, why would we mere humans. I would advice that you take a closer look at your query and the query plan.
-
RahulRandive 10,221 Reputation points
2024-01-10T22:38:24.1633333+00:00 You can possibly monitor the tempdb usages to avoid such issue and take corrective action. You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that's used in the
tempdb
files. For example, the following four sample scripts find the amount of free space intempdb
, the amount of space used by the version store, the amount of space used by internal objects, and amount of space used by user objects:-- Determining the amount of free space in tempdb SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB] FROM tempdb.sys.dm_db_file_space_usage; -- Determining the amount of space used by the version store SELECT SUM(version_store_reserved_page_count) AS [version store pages used], (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB] FROM tempdb.sys.dm_db_file_space_usage; -- Determining the amount of space used by internal objects SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used], (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB] FROM tempdb.sys.dm_db_file_space_usage; -- Determining the amount of space used by user objects SELECT SUM(user_object_reserved_page_count) AS [user object pages used], (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB] FROM tempdb.sys.dm_db_file_space_usage;
-
LiHongMSFT-4306 30,591 Reputation points
2024-01-11T03:44:16.18+00:00 Is there a way to estimate how large tempdb data (and/or log) files will need in size or growth to accommodate prior to query run...(or while it's running)?
As far as I know, it seems not.
However, you can try to relieve the pressure on tempdb by following below suggestions:
Make sure tempdb file can autogrow. Also make sure you are dropping temporary tables at the end of stored procedures that create them.
Use batching if possible.
Monitor and optimize queries that are creating large hash tables.
Take care of sorting operations on queries that are spilling data to tempdb.
Best regards,
Cosmog Hong
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.