In today’s blog, we will be talking about Tempdb Space Usage.
Tempdb is a system database inside SQL Server, used primarily for internal and temporary operations. In the case of troubleshooting performance, DBAs focus mostly on the size of Tempdb, what is being stored here and why?
Tempdb is comprised of two files, a data file (could be more than one) and a log file. Upon executing workloads, there is a significant increase in the size of these two files, while the Available Space will continue to decrease, causing SQL Server to not function at its optimum.
The DMV, sys.dm_db_file_space_usage, and a system catalog – sys.database_files, provide the required output for purposes of troubleshooting Tempdb.
Note: The following test must not be performed in a production environment.
USE [tempdb];
GO
SELECT GETDATE() AS runtime
,SUM(user_object_reserved_page_count) * 8 AS usr_obj_kb
,SUM(internal_object_reserved_page_count) * 8 AS internal_obj_kb
,SUM(version_store_reserved_page_count) * 8 AS version_store_kb
,SUM(unallocated_extent_page_count) * 8 AS freespace_kb
,SUM(mixed_extent_page_count) * 8 AS mixedextent_kb
FROM sys.dm_db_file_space_usage
USE [tempdb]
GO
SELECT [name] AS [Logical Name]
,size / 128.0 AS [Total Size in MB]
,size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS [Available Space In MB]
FROM sys.database_files
Tempdb objects are classified as user objects, internal objects, and version store.
- User objects are created by stored procedures, queries, and workloads including both local and global temporary tables.
- Internal objects are created by SQL Server for running queries – that may involve a sort or hash operation, which requires temporary space to complete the execution successfully.
- Version store object comprises SNAPSHOT isolation, which is responsible for maintaining multiple versions of records.
Let’s create a stored procedure to simulate workloads.
--Create a Stored Procedure to populate data on the temp table
USE [tempdb];
GO
IF OBJECT_ID('[dbo].[sp_populate_data]','P') IS NOT NULL
DROP PROCEDURE [dbo].[sp_populate_data]
GO
CREATE PROCEDURE [dbo].[sp_populate_data]
@TAB AS NVARCHAR(50), @ROW AS INT
AS
SET NOCOUNT ON
DECLARE @RowCount INT
DECLARE @RowMax INT
DECLARE @RowString INT
DECLARE @SQL1 NVARCHAR(200)
SET @SQL1 = N'SELECT @RowCount1=COUNT(1) FROM '+@TAB
EXEC sp_executesql @SQL1, N'@RowCount1 INT OUTPUT', @RowCount1 = @RowCount OUTPUT
SET @RowMax = @RowCount + @ROW
WHILE @RowCount < @RowMax
BEGIN
SET @RowString = CAST(@RowCount AS INT)
DECLARE @FNAME VARCHAR(30)
SET @FNAME = (SELECT CAST(CAST(newid() as binary(16)) as varchar(8)))
DECLARE @num1 VARCHAR(20)
SET @num1 = (SELECT CONVERT(INT, (2000+1)*RAND()))
DECLARE @name1 VARCHAR(50)
SET @name1 = @FNAME +'_'+ @num1;
--SELECT @name1;
DECLARE @LNAME VARCHAR(30)
SET @LNAME = (SELECT CAST(CAST(newid() as binary(16)) as varchar(8)))
DECLARE @num2 VARCHAR(20)
SET @num2 = (SELECT CONVERT(INT, (2000+1)*RAND()))
DECLARE @name2 VARCHAR(50)
SET @name2 = @LNAME +'_'+ @num2;
--SELECT @name2;
DECLARE @ADDRESS VARCHAR(200)
SET @ADDRESS = (SELECT CAST(CAST(newid() as binary(16)) as varchar(50)))
--SELECT @ADDRESS
DECLARE @SID INT
SET @SID = (REPLICATE('0', 10 - DATALENGTH(@RowString)) + @RowString)
--SELECT @SID;
DECLARE @PID INT
SET @PID = (SELECT CONVERT(INT, (200000+1)*RAND()))
--SELECT @PID;
DECLARE @SALARY INT
SET @SALARY = (SELECT CONVERT(INT, (200000+1)*RAND()))
DECLARE @SQL2 NVARCHAR(MAX)
SET @SQL2 = N'INSERT INTO '+@TAB+ N' VALUES(
@SID11,@PID11,@name11,@name22,@ADDRESS11,@SALARY11);'
DECLARE @PARAM NVARCHAR(MAX)
SET @PARAM = N'@SID11 INT,@PID11 INT,@name11 VARCHAR(50),@name22 VARCHAR(50),@ADDRESS11 VARCHAR(50),@SALARY11 INT'
EXEC sp_executesql @SQL2, @PARAM, @SID11 = @SID, @PID11 = @PID,
@name11 = @name1, @name22 = @name2, @ADDRESS11 = @ADDRESS, @SALARY11 = @SALARY
--EXEC sp_executesql @SQL2
SET @RowCount = @RowCount + 1
END
GO
Let’s create a global temporary table.
--Create a Global temp table
USE [AdventureWorks2016]
GO
IF OBJECT_ID('Tempdb..##NewGlobalTempTable','U') IS NOT NULL
DROP TABLE ##NewGlobalTempTable
GO
CREATE TABLE ##NewGlobalTempTable(
[SID] [bigint] NOT NULL,
[PID] [int] NOT NULL,
[FNAME] [varchar](50) NOT NULL,
[LNAME] [varchar](50) NOT NULL,
[ADDRESS] [varchar](max) NOT NULL,
[SALARY] [int] NOT NULL)
GO
Let’s populate the global temporary table.
--Execute the below-stored procedure to populate data in ##NewGlobalTempTable table
USE tempdb
GO
EXEC sp_populate_data ##NewGlobalTempTable,100000
Now let’s check the tempdb usage.
USE [tempdb];
GO
SELECT GETDATE() AS runtime
,SUM(user_object_reserved_page_count) * 8 AS usr_obj_kb
,SUM(internal_object_reserved_page_count) * 8 AS internal_obj_kb
,SUM(version_store_reserved_page_count) * 8 AS version_store_kb
,SUM(unallocated_extent_page_count) * 8 AS freespace_kb
,SUM(mixed_extent_page_count) * 8 AS mixedextent_kb
FROM sys.dm_db_file_space_usage
USE [tempdb]
GO
SELECT [name] AS [Logical Name]
,size / 128.0 AS [Total Size in MB]
,size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS [Available Space In MB]
FROM sys.database_files
It is observed that the size of the user object increases to about 28MB, Available Space drops below 50MB, while the size of the data file remains unchanged. This is because there was already enough space available in the data file to be consumed.
Performing this operation multiple times will lead to SQL Server running out of Available Space completely, which will require the data file to be grown.
Next, the space usage of internal objects is analyzed using a few CROSS JOIN operations running in a loop. For this sort operation to execute successfully, a work table needs to be created internally, to store the intermediate results of the sort operation. This will cause the size of the data file to grow.
USE AdventureWorks2014;
GO
WHILE (1 = 1)
BEGIN
DBCC DROPCLEANBUFFERS
SELECT TOP (100000) a.*
FROM master..spt_values a
,master..spt_values b
,master..spt_values C
ORDER BY a.number DESC
,b.number DESC
,C.number DESC
END;
GO
Let’s check the tempdb usage again (execute the script again).
This leads to the internal object occupying more space and causing the data file to grow further. Keep executing the script to see the increase in space.
Once the workload has stopped executing, there is no longer a requirement for the work table that was created. As a result, the size of the internal object will be greatly reduced, while the amount of space available will increase.
It is important to note that the size of the data file will not reduce automatically. It needs to be shrunk manually to recover space on the disk.
The final part of this blog deals with the version store. To watch this in action, first READ COMMITED SNAPSHOTS need to be enabled in AdventureWorks2014. This will allow SQL Server to store the last committed values/records for a given transaction in the version store object.
--Enable READ COMMITED SNAPSHOT on database PWI_TEMPDBFULL
USE [master]
GO
ALTER DATABASE [AdventureWorks2016] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO
Currently, our version store is empty, holding no data. This can be verified using the DMV- sys.dm_tran_version_store.
SELECT * FROM sys.dm_tran_version_store
Let’s create a table for this demo.
USE [AdventureWorks2014]
GO
CREATE TABLE OpenTran (NAME VARCHAR(50));
GO
INSERT INTO OpenTran VALUES('SQLMaestros');
GO
SELECT * FROM OpenTran
GO
Now, let’s update the table in an in-flight transaction with no commits or rollbacks.
USE [AdventureWorks2014] GO BEGIN TRAN UPDATE [OpenTran] SET [NAME] = 'TEST'
Having done so, running sys.dm_tran_version_store, there is one record in the version store which is the last committed image of the record consuming 64KB of space on the disk. In the case of a mass update, this value will be much higher.
SELECT * FROM sys.dm_tran_version_store
Now let’s issue the ROLLBACK.
ROLLBACK
Keep executing the query a few times and observe that the record gets cleared in some time.
SELECT * FROM sys.dm_tran_version_store
