Wikis - Page

Support Tip: Setting the SQL Server Temp DB size for Content Manager

2 Likes

Content Manager makes use of Temp Tables in SQL Server and DBA's need to know what size they should set for the Temp_DB.

There isn't a one-size-fits-all formula for determining the required size of tempdb in SQL Server, as it depends on various factors such as the workload, the number of users, the complexity of queries, etc. However, you can estimate the size based on certain guidelines and observations:

  1. Number of User Connections: Generally, each user connection will require some space in tempdb for storing temporary objects, sorting, etc. So, the more connections you have, the larger tempdb might need to be.
  2. Size of Temporary Objects: If your queries involve large temporary tables, sorting, or hashing operations, you might need more space in tempdb to accommodate them.
  3. Query Complexity: Complex queries or those involving large data sets might require more space in tempdb for intermediate results.
  4. Version Store: If your database is using features like snapshot isolation or triggers, it might require additional space in tempdb for version store usage.
  5. Maintenance Operations: Some maintenance operations like rebuilding indexes or certain DBCC checks can also require space in tempdb.
  6. Monitoring and Adjusting: Monitor tempdb usage regularly and adjust its size accordingly. You can use SQL Server Management Studio (SSMS) or dynamic management views (DMVs) to monitor tempdb usage.
  7. Available Disk Space: Ensure that you have enough disk space available for tempdb to grow as needed.

Given these factors, it's often recommended to start with a reasonable initial size for tempdb and then monitor its usage over time. You can then adjust its size accordingly based on your observations. It's also a good practice to pre-size tempdb to avoid frequent autogrowth events, which can impact performance.

Labels:

Support Tips/Knowledge Docs
Comment List
Related
Recommended