TempDB performance

Products

Information Centric Analytics

Issue/Introduction

The RiskFabric Processing job or RiskFabric Intraday Processing job performs poorly or appears to be hung. No errors are logged in the SQL Server Agent job history, but an error similar to the following is logged in the Log_DataTransformation table:

Error Msg: Could not allocate space for object '<temporary system object: <ID>>' 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.

Environment

Release : 6.x

Component : Microsoft SQL Server

Cause

SQL Server uses TempDB as a working space during processing. As TempDB runs out of space, processing performance decreases because SQL Server has less working space on disk for temporary objects and their operations. If TempDB uses all the space allocated to it and can't grow, it will impede any further activity by SQL Server that requires TempDB.

Resolution

Because the space needed by TempDB is a function of the data ingested into Information Centric Analytics (ICA) from disparate data sources, and because the amount of data is, itself, a function of the number of entities and event velocities in source systems, Broadcom cannot prescribe the amount of space to allocate for TempDB. Per the ICA Administrator Guide, TempDB space should be very large and implemented using multi-file architectures on fast spindle pools or solid state drives (SSD), and should ideally be hosted on a drive separate from the drive hosting the RiskFabric database:

https://techdocs.broadcom.com/us/en/symantec-security-software/information-security/information-centric-analytics/6-6/Administrator-Guide/installation.html

Microsoft provides an overview of the procedure for moving TempDB to a new drive in the document Move System Databases.

There are a number of resources available online that provide methods for monitoring and assessing TempDB usage to assist with sizing calculations. Broadcom strongly recommends database architecture and design decisions be made in consultation with a SQL Server database administrator (DBA).

Additional Information

Microsoft offers an overview of TempDB and its operations here:

https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver16

NOTE: URL last validated November 1, 2023