TempDB Analysis: Gain Insight into TempDB Performance and Utilization 

Tempdb is arguably one of the most critical databases in SQL Server. Developers and SQL Server itself use tempdb for a wide array of things—versioning, temporary tables, table variables, query plan operations, cursors, triggers, LOB variables—that you must take into consideration when designing and developing SQL Server applications. If misconfigured, tempdb can cause cascading performance issues across your SQL Server instance.

Insight into the things that are using tempdb, and how effectively they are using it, is crucial to troubleshooting and preventing tempdb contention. The TempDB view in SQL Sentry Portal, the installed SQL Sentry web interface, provides you with a dedicated interface to view important tempdb storage and utilization metrics.

You can also see tempdb objects by type (e.g., user tables, user temp tables, global temp tables, internal tables, system tables, query objects). The TempDB Activity chart provides a high-level view of the type and level of tempdb activity.

With the TempDB Session Usage chart, you can identify if a few sessions are consuming a large amount of space in tempdb or if many sessions are each consuming a small amount. This data is grouped by Host, Application, and Login. You can highlight a segment on the TempDB Activity chart and zoom in to view details for that specific time range.

SolarWinds SQL Sentry provides additional tempdb monitoring information via the Top SQL view, integrated Plan Explorer, the Disk Space and Disk Activity tabs, Advisory Conditions, the Performance Analysis Dashboard, and the Performance Counter History report.