Managing TempDB in SQL Server:TempDB Basics (cont..)
In the previous blog https://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/21/tempdb-basics.aspx I started the discussion on the TempDB basics and this blog continues it.
TempDB Basics:
· As I indicated, the TempDB is created every time the SQL Server is started, it is tempting let it grow starting with the size initially set in the Model Database. While this works, it will fragment your TempDB database because by default the size of the TempDB will increase in the increments of 10% of its original size which starts in low MB range. If your workload needs the size of the TempDB to be 500GB in steady state, then you can imagine the number of time the TempDB needs to grow. On top of this, each time the TempDB grows, all transactional activity may come to an halt because TempDB is used by most operations and these activities will get blocked until more disk space gets allocated to the TempDB. So the key recommendation is to pre-allocate the size of the TempDB that matches with the needs of your workload. For example, if your workload needs 500GB TempDB, then it makes sense to pre-allocated say 600GB disk space it does not need to grow further. The auto-grow functionality should only be used more for exception rather than a strategy. One interesting aspect is that once the size of the database is explicitly set to say 600GB, the size will be retained when you start the SQL Server next time. You can use instant file initialization feature available starting with SQL Server 2005. To use instant file initialization, you must run the SQL Server (MSSQLSERVER) service account under a Windows account and assign the Windows SE_MANAGE_VOLUME_NAME special permission to that Windows account. Similarly, the log file(s) should be appropriately sized.
· There is one Filegroup for data files and one Filegroup for log files. It is recommended that you create at least as many data files as the number of Cores used by SQL Server process to minimize allocation bottleneck. I will explain this in more details in my troubleshooting section.
· Types of Objects in TempDB: There are following three kinds of objects created in TempDB
o User Objects: These objects correspond to ## and # tables and table variables created explicitly by the application. BOL describes these objects well. For specific discussion for the difference in # table a table variable, please refer to the blog https://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx. Some key points about changes for User Object in SQL Server 2005
§ REDO information is not logged
§ Improved caching for temporary objects. SQL Server caches 1 IAM page and 1 data page with the associated query plan. The benefit of this is significant. First, if the same plan is executed multiple times, the temporary table needs to be created once there by reducing DDL contention. Imagine if a temporary table with say 5 columns gets created/destroyed every time, SQL Server will need to insert one row system table for ‘tables’ and 5 rows for columns in ‘columns’ and then remove these entries when table is removed. Second, it reduces allocation overhead. Let me illustrate this with the following example
use general
go
set nocount on
go
-- create the stored procedure
if object_id('test_temptable_caching') IS NOT NULL
drop procedure test_temptable_caching;
go
create procedure test_temptable_caching
as
create table #t1 (c1 int, c2 int, c3 char(5000))
--create index foo on #t1(c1);
declare @i int
select @i = 0
while (@i < 10)
begin
insert into #t1 values (@i, @i + 1000, 'hello')
select @i = @i+1
end
print 'done with the stored proc'
go
Now if I execute this stored procedure in a loop, the #table is only created on the first execution. You can use the following script to know how many times the #table was created.
declare @table_counter_before_test bigint;
select @table_counter_before_test=cntr_value
from sys.dm_os_performance_counters
where counter_name = 'Temp Tables Creation Rate';
declare @i int
select @i = 0
while (@i < 10)
begin
exec test_temptable_caching
select @i = @i+1
end
declare @table_counter_after_test bigint;
select @table_counter_after_test=cntr_value
from sys.dm_os_performance_counters
where counter_name = 'Temp Tables Creation Rate';
print 'Temp tables created during the test: ' +
convert( varchar(100), @table_counter_after_test-@table_counter_before_test);
Temp Objects are only cached when none of the following conditions is violated.
a. Named constraints are not created.
b. Data Definition Language (DDL) statements that affect the table are not run after the temp table has been created, such as the CREATE INDEX or CREATE STATISTICS statements.
c. Temp object is not created by using dynamic SQL, such as: sp_executesql N'create table #t(a int)'
o Internal Objects: These objects are created internally by SQL Server to execute user commands. For example, when processing an Order By clause, the SQL Server uses TempDB for sorting. Similarly, when doing a hash join, the hash table uses TempDB for its persistence. Some examples of internal objects are Work tables, Work Files, Spool Operator and so on. Like user objects, the caching behavior of internal objects has improved in SQL Server 2005. Now, SQL Server caches 1 IAM page and 1 full extent (i.e 8 pages). For example, you can identify the work table caching using the permon counter 'Worktables Created/sec' in similar way described with #table in earlier. Another key point here is that operations on internal objects are not logged because internal objects are created/destroyed in the scope of a single statement.
o Version Store: This is a new construct in SQL Server 2005 and it needs a separate blog of its own.
Thanks
Sunil Agarwal
Comments
Anonymous
December 31, 2008
SunilA has a great set of posts out on the SQL Server Storage Engine blog about managing TEMPDB. It'sAnonymous
January 11, 2009
This blog continues the discussion on the common issues in TempDB that you may need to troubleshoot.