Transcript

SQL Diagnostic Manager for SQL Server

SQL Diagnostic Manager currently leads the marketplace in the monitoring of tempdb. The views available in SQL Diagnostic Manager give a DBA everything he or she might need to troubleshoot a tempdb problem.
To navigate in SQL Diagnostic Manager to tempdb, all you need to do is select the server you want to troubleshoot, pick the Databases tab, and then go to tempdb.

TempDB

Tempdb is a workspace used to store temporary user objects, results created through queries and sorts, as well as other objects created through the SQL Server. Because of the significant amounts of data stored in tempdb, issues can affect the entire SQL Server and its databases.

Often DBAs are faced with the scenario where tempdb fills up in the middle of the night, but no one knows what was running at that time. The History Browser in SQL Diagnostic Manager allows us to go back and forensically view problems that happened in the past. So if you are trying to see what happened in tempdb, maybe last night or yesterday or last week, simply open History Browser, select the day that you would like to go to or view, select the time period you would like to look at, and view.

From there you will be able to see the exact time that the problem occurred, tempdb space used by file and the breakdown, how the space is being used by type of object (whether it is user object, internal object, mixed extents, tempdb contention, as well as version store cleanup rate versus version store generation rate.

SQL Server TempDB

Because tempdb is a shared database for all applications and all users on a SQL Server, its health is integral to the performance of any server. Each time you start SQL Server, it recreates tempdb. Having a monitoring tool in place allows us to get the usage of tempdb, even after a reboot, so we can compare and see problems that have occurred, even in the past.

Identifying the sessions that are affecting tempdb can be done at the bottom of the screen. Here you can see every session that is listed, up and down is active in tempdb, and you can see specifics around those sessions, how much CPU, physical I/O [input and output], and memory consumption each session is taking.

SQL Server TempDB Full

The tempdb version store collects the data necessary to support row versioning. Each time a data value changes, a new version of the row is created and stored for as long as the oldest active transaction needs to access it. Once the row version is no longer needed, it is removed from tempdb by a cleanup job, which runs once per minute. As a result, long-running transactions prevent cleanup of older entries into tempdb version store, causing growth which can affect performance and cause tempdb to run out of space.

Using the version store cleanup chart, we can compare and contract version cleanup rate versus cleanup generation rate. And usually, if the chart shows peaks and dips that are mostly aligned, then you know everything is running fine. If the version store cleanup rate is lower than the version store generation rate, then that signifies we might have a problem in tempdb that might cause a space issue.

SQL Server TempDB Contention

Tempdb contention is also an issue that can affect performance. Tempdb resource contention or waits are usually the result of heavy use on too few tempdb files and occur when the system attempts to allocate pages.

You can see if there is a tempdb issue by dropping down this chart and selecting Tempdb Contention. The Tempdb Contention chart displays latch wait times in milliseconds for the allocation pages in tempdb. The three tracked allocation pages are Page Free Space, Global Allocation Map, and Shared Global Allocation Map.

IDERA SQL Diagnostic Manager

You can also remediate issues in tempdb directly from SQL Diagnostic Manager. To do that, first identify the session you want to interact with, using the area at the bottom of the screen, then you can select that session, right-click it, view its locks if you would like, view session details, or you can trace it, or kill the session directly from SQL Diagnostic Manager.

Knowing what is happening in your tempdb can greatly improve the performance of your SQL Server and monitoring is a great first start.