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 of the RiskFabric relational database:

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

Microsoft SQL Server uses tempdb as 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 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 the use of 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 Installing Symantec ICA section of the Symantec 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.

Microsoft provides the procedure for moving tempdb to a new drive in the document Move System Databases. Microsoft also provides an overview of tempdb along with details of its operations and performance optimization recommendations in the document tempdb database.

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 Microsoft SQL Server database administrator (DBA).