Question: What is Stored in TempDB?
Answer: In my recent conversation with my client during they asked me if I know what is currently stored in their TempDb. If you run the following query you would get the detailed list.
SELECT tb.name AS [Temporary Object Name], ps.row_count AS [# rows], ps.used_page_count * 8 AS [Used space (KB)], ps.reserved_page_count * 8 AS [Reserved space (KB)] FROM tempdb.sys.partitions AS prt INNER JOIN tempdb.sys.dm_db_partition_stats AS ps ON prt.partition_id = ps.partition_id AND prt.partition_number = ps.partition_number INNER JOIN tempdb.sys.tables AS tb ON ps.object_id = tb.object_id ORDER BY tb.name
The query above will give us a result of something in the following line:
From the result, you can clearly figure out what actually is stored in your TempDB and based on that you can make a decision to either keep it or to remove it.
Please note that when you restart your SQL Server, your TempDB is anyway automatically reset to the original state. You should not store anything in your TempDB which you may need persistently needed in the future.
Let us see a very simple example of the same with the help of the latest sample database.
- SQL SERVER – Parameter Sniffing Simplest Example
In this blog post, we discuss what actually is parameter sniffing and how we can overcome it with the help of recompiling the stored procedure.
- SQL SERVER – Parameter Sniffing and Local Variable in SP
It is easy to overcome the parameter sniffing for the stored procedure by declaring the local variable inside the stored procedure.
- SQL SERVER – Parameter Sniffing and OPTIMIZE FOR UNKNOWN
You can take advantage of the new query hint of Optimize For Unknown to simulate the local variable in the stored procedure. A very underutilized technique indeed.
- SQL SERVER – DATABASE SCOPED CONFIGURATION – PARAMETER SNIFFING
This new database level enhancement was introduced recently which can help you overcome any issue with the parameter sniffing.
- SQL SERVER – Parameter Sniffing and OPTION (RECOMPILE)
The oldest and most traditional technique to not cache the query plans and compile your stored procedure or queries every single time to get optimal performance.
- Performance and Recompiling Query – Summary
This post summarizes the entire series of parameter sniffing, performance and recompiling query.
Reference: Pinal Dave (http://blog.SQLAuthority.com)