Introduction:
Overview: In the competitive world of IT, where efficiency and performance are paramount, database management often poses significant challenges. One of our esteemed customers, a leading IT firm with over 3,000 SQL Server licenses and more than 400 applications in their portfolio, encountered a critical issue impacting their operational efficacy. As their daily processing jobs, application performance, and quarter-end activities began to suffer, a deeper investigation revealed the root cause: severe tempdb contention.
Tempdb contention is a critical bottleneck in our customer environments, particularly under high-demand scenarios. This system database, essential for temporary storage and intermediate results, became a critical pain point for our customer, significantly hindering their performance and operational stability.
In this blog, we delve into the specifics of tempdb contention, examining its impact and exploring strategies to mitigate such challenges. By leveraging both Temporary Tables and Common Table Expressions (CTEs), we aim to provide a comprehensive guide to optimize database performance and reduce contention in high-stakes environments. Join us as we navigate through practical solutions and best practices, empowering you to enhance your SQL Server management and ensure seamless operations.
What is a CTE (Common Table Expression)?
A Common Table Expression (CTE) is a temporary result set in SQL Server that you can use to simplify complex queries. It helps make your SQL queries more readable and easier to manage. Think of it as a way to create a temporary table that you can use just for the duration of a single query.
How Does a CTE Work?
What is a Temporary Table?
A temporary table in SQL Server is a special type of table that is created and used during the execution of a single session or query. Temporary tables allow you to store and manipulate intermediate results temporarily. They are automatically deleted when they are no longer in use or when the session ends.
How Does a Temporary Table Work?
Types of Temporary Tables
How Temp Tables Cause Contention:
Here is a small demonstration how cte and temp table works:
Step 1: create a table with 50 columns
CREATE TABLE [dbo].[Orders](
[OrderID] [int] IDENTITY(1,1) NOT NULL, [CustomerName] [varchar](100) NULL, [OrderDate] [datetime] NULL, [ProductID] [int] NULL, [Quantity] [int] NULL, [Price] [decimal](18, 2) NULL, [Status] [varchar](50) NULL, [ShippingAddress] [varchar](200) NULL, [Country] [varchar](50) NULL, [Comments] [varchar](1000) NULL, [Column11] [varchar](50) NULL, [Column12] [varchar](50) NULL, [Column13] [varchar](50) NULL, [Column14] [varchar](50) NULL, [Column15] [varchar](50) NULL, [Column16] [varchar](50) NULL, [Column17] [varchar](50) NULL, [Column18] [varchar](50) NULL, [Column19] [varchar](50) NULL, [Column20] [varchar](50) NULL, [Column21] [varchar](50) NULL, [Column22] [varchar](50) NULL, [Column23] [varchar](50) NULL, [Column24] [varchar](50) NULL, [Column25] [varchar](50) NULL, [Column26] [varchar](50) NULL, [Column27] [varchar](50) NULL, [Column28] [varchar](50) NULL, [Column29] [varchar](50) NULL, [Column30] [varchar](50) NULL, [Column31] [varchar](50) NULL, [Column32] [varchar](50) NULL, [Column33] [varchar](50) NULL, [Column34] [varchar](50) NULL, [Column35] [varchar](50) NULL, [Column36] [varchar](50) NULL, [Column37] [varchar](50) NULL, [Column38] [varchar](50) NULL, [Column39] [varchar](50) NULL, [Column40] [varchar](50) NULL, [Column41] [varchar](50) NULL, [Column42] [varchar](50) NULL, [Column43] [varchar](50) NULL, [Column44] [varchar](50) NULL, [Column45] [varchar](50) NULL, [Column46] [varchar](50) NULL, [Column47] [varchar](50) NULL, [Column48] [varchar](50) NULL, [Column49] [varchar](50) NULL, [Column50] [varchar](50) NULL)Step 2: Insert one crore records using SQL Script that generates imaginary values.
DECLARE @counter INT = 1;
DECLARE @maxRecords INT = 10000000; — 1 crore records
WHILE @counter <= @maxRecords
BEGIN
INSERT INTO Orders (CustomerName, OrderDate, ProductID, Quantity, Price, Status, ShippingAddress, Country, Comments,
Column11, Column12, Column13, Column14, Column15, Column16, Column17, Column18, Column19, Column20,
Column21, Column22, Column23, Column24, Column25, Column26, Column27, Column28, Column29, Column30,
Column31, Column32, Column33, Column34, Column35, Column36, Column37, Column38, Column39, Column40,
Column41, Column42, Column43, Column44, Column45, Column46, Column47, Column48, Column49, Column50)
VALUES
(
‘Customer’ + CAST(@counter AS NVARCHAR(50)),
DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 546, ‘2023-01-01’), — Random date between 2023-01-01 and 2024-07-01
ABS(CHECKSUM(NEWID())) % 1000 + 1, — Random ProductID between 1 and 1000
ABS(CHECKSUM(NEWID())) % 10 + 1, — Random Quantity between 1 and 10
CAST(ABS(CHECKSUM(NEWID())) % 50000 AS FLOAT) / 100, — Random Price between 0.00 and 500.00
CASE ABS(CHECKSUM(NEWID())) % 3
WHEN 0 THEN ‘Pending’
WHEN 1 THEN ‘Shipped’
ELSE ‘Canceled’
END, — Random Status
CAST(ABS(CHECKSUM(NEWID())) % 900 + 100 AS NVARCHAR(10)) + ‘ Main St, City’ + CAST(@counter AS NVARCHAR(50)),
CASE ABS(CHECKSUM(NEWID())) % 10
WHEN 0 THEN ‘USA’
WHEN 1 THEN ‘Canada’
WHEN 2 THEN ‘UK’
WHEN 3 THEN ‘Germany’
WHEN 4 THEN ‘France’
WHEN 5 THEN ‘Italy’
WHEN 6 THEN ‘Spain’
WHEN 7 THEN ‘Australia’
WHEN 8 THEN ‘India’
ELSE ‘Brazil’
END, — Random Country
‘No comments’,
‘Column11’, ‘Column12’, ‘Column13’, ‘Column14’, ‘Column15’, ‘Column16’, ‘Column17’, ‘Column18’, ‘Column19’, ‘Column20’,
‘Column21’, ‘Column22’, ‘Column23’, ‘Column24’, ‘Column25’, ‘Column26’, ‘Column27’, ‘Column28’, ‘Column29’, ‘Column30’,
‘Column31’, ‘Column32’, ‘Column33’, ‘Column34’, ‘Column35’, ‘Column36’, ‘Column37’, ‘Column38’, ‘Column39’, ‘Column40’,
‘Column41’, ‘Column42’, ‘Column43’, ‘Column44’, ‘Column45’, ‘Column46’, ‘Column47’, ‘Column48’, ‘Column49’, ‘Column50’
);
SET @counter = @counter + 1;
END;
PRINT ‘Finished inserting records.’;
Step 3: Once after inserting the record, check the performance of the CTE like time taken, and memory,cpu utilization
SET STATISTICS TIME ON;
WITH CTE AS (
SELECT Country, COUNT(*) AS OrderCount
FROM Orders
GROUP BY Country
)
SELECT *
FROM CTE
WHERE OrderCount > 5000;
SET STATISTICS TIME OFF;
The elapased time and the cpu time taken for the above the code is :
The memory allocated by the above query in KB is:
The allocation of the cte in tempdb
The alloacation of cte in tempdb is zero kb
Step 4: , check the performance of the Temp tables like time taken, and memory,cpu utilization and their allocation space in tempdb
SET STATISTICS TIME ON;
CREATE TABLE #TempOrders (
Country NVARCHAR(50),
OrderCount INT
);
INSERT INTO #TempOrders (Country, OrderCount)
SELECT Country, COUNT(*) AS OrderCount
FROM Orders
GROUP BY Country;
SELECT *
FROM #TempOrders
WHERE OrderCount > 5000;
DROP TABLE #TempOrders;
SET STATISTICS TIME OFF;
The elapsed time and cpu time for the above code is :
The memory allocated for the above code in KB is:
The allocation of temporary table in tempdb:
The allocation size of user created objects in tempdb is 192 KB
Differences Observed Between Temporary Tables and CTEs
CTEs:
Temporary Tables:
Conclusion
According to the above example, both the temporary table and the CTE provide similar output and execution time, using the same amount of resources. However, the overhead caused by the temporary table includes allocation space in tempdb, which is not deallocated until the session is closed.
If this temporary table code runs in 100 sessions, the objects remain allocated until each session ends. If this scenario scales to 1000 sessions, it can lead to tempdb contention.
Therefore, it is clear that for applications requiring data for simple calculations and transformations, it is more efficient to use CTEs. This approach avoids potential tempdb contention that can occur on a larger scale with temporary tables.
Happy Reading !!
Thejas K