What is tempdb?
The tempdb system database is a global resource that has been used since the inception of SQL Server. It is available to all SQL Server users connected to the SQL Server instance. It holds many types of data and can often be the source of a performance bottleneck.
For example, tempdb can hold the following:
- Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
- Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting
- Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions
- Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers
So everytime that you do a
SELECT col1, col2 INTO #temptable FROM table
you are going to be using
tempdb. As you write more complex queries, your need for tempdb will
grow. When setup correctly, tempdb performance can be greatly improved
without touching your queries.
How do I know if tempdb has performance issues?
One way that we can see if tempdb is suffering, is by checking the wait
stats of the server and looking for high levels of
PAGELATCH
. A latch
is a short-term synchronisation lock that is used by SQL Server to
maintain the integrity of the physical pages of data structures in
memory. Unlike locks, you are not able to influence the behaviour of
latches as SQL Server manages this for us.
Glenn Berry has created a wonderful diagnostic script which is available from here .sql). I have taken his wait stat query and modified it slightly so that the column names and general formatting are more readable.
WITH Waits AS
(
SELECT wait_type AS 'Wait_type'
, wait_time_ms / 1000.0 AS 'Wait_time_seconds'
, 100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS 'Percent_of_results'
, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS 'Row_number'
FROM sys.dm_os_wait_stats WITH (NOLOCK)
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','ONDEMAND_TASK_QUEUE', 'BROKER_EVENTHANDLER', 'SLEEP_BPOOL_FLUSH')
)
SELECT W1.wait_type AS 'Wait_Type'
, CAST(W1.Wait_time_seconds AS DECIMAL(12, 2)) AS 'Wait_time_seconds'
, CAST(W1.Percent_of_results AS DECIMAL(12, 2)) AS 'Percent_of_results'
, CAST(SUM(W2.Percent_of_results) AS DECIMAL(12, 2)) AS 'Running_percentage'
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.[Row_number] <= W1.[Row_number]
GROUP BY W1.[Row_number], W1.wait_type, W1.wait_time_seconds, W1.Percent_of_results
HAVING SUM(W2.percent_of_results) - W1.Percent_of_results < 99
OPTION (RECOMPILE);
The modified version of this query is available from here: Wait Stats Diagnostic Query Inlcuding BOL explainations
When we run this query in SSMS, we would expect to see rising levels of
PAGELATCH_UP
,
PAGELATCH_EX
and
PAGELATCH_SH
. Note for this query,
I have not choosen to filter just by latches as this gives us a greater
oversight as what is going on in our servers. As a general rule of
thumb, if you do not see pagelatches in the top 3 of the results window,
then I would not worry yet. Instead, watch for a trend.
Another way we can tell whether or not tempdb is becoming a problem, is by looking at the virtual file statistics through this query:
SELECT DB_NAME(database_id) AS 'Database_Name'
, CASE WHEN file_id = 2 THEN 'Log' ELSE 'Data' END AS 'File_Type'
, ((size_on_disk_bytes/1024)/1024.0) AS 'Size_On_Disk_in_MB'
, io_stall_read_ms / num_of_reads AS 'Avg_Read_Transfer_in_Ms'
, CASE WHEN file_id = 2 THEN
CASE
WHEN io_stall_read_ms / num_of_reads < 5 THEN
'Good'
WHEN io_stall_read_ms / num_of_reads < 15 THEN
'Acceptable'
ELSE
'Unacceptable'
END
ELSE
CASE
WHEN io_stall_read_ms / num_of_reads < 10 THEN
'Good'
WHEN io_stall_read_ms / num_of_reads < 20 THEN
'Acceptable'
ELSE
'Unacceptable'
END
END AS 'Average_Read_Performance'
, io_stall_write_ms / num_of_writes AS 'Avg_Write_Transfer_in_Ms'
, CASE WHEN file_id = 2 THEN
CASE
WHEN io_stall_write_ms / num_of_writes < 5 THEN
'Good'
WHEN io_stall_write_ms / num_of_writes < 15 THEN
'Acceptable'
ELSE
'Unacceptable'
END
ELSE
CASE
WHEN io_stall_write_ms / num_of_writes < 10 THEN
'Good'
WHEN io_stall_write_ms / num_of_writes < 20 THEN
'Acceptable'
ELSE
'Unacceptable'
END
END AS 'Average_Write_Performance'
FROM sys.dm_io_virtual_file_stats(null,null)
WHERE num_of_reads > 0 AND num_of_writes > 0
This query inspects the
sys.dm_io_virtual_file_stats
DMV and gives you
a quickview of IO subsystem performance. I have offset this against
Microsoft's recommendations for IO performance. Obviously, this all
depends on the subsystem you are running. If your running on SATA disks,
your performance isn't going to be as good as if you were running on
RAID'd SAS disks. It's all about monitoring and data analysis. I have
included Microsoft's recommendations in the downloadable script located
here
.
How many files should I create for temp db?
First of all, I would say that unless you have identified a performance problem then you can actually cause more harm than good (with disk contention etc). Assuming that you have a valid reason for creating more than one tempdb file, Microsoft recommend that you have 1/4 or 1/2 files to number of cores on your system, upto a maximum of 8. Microsoft's internal research has showed that there is no performance gain from having more than 8 tempdb files.
For example, if you have an 8 core machine, you should have either 2 or 4 tempdb files.
How do I add an extra file to tempdb?
ALTER DATABASE tempdb
ADD FILE
(
name = tempdb_secondary,
filename = 'C:\databases\tempdb_secondary.ndf',
size = 512 MB
)
You will notice that I have set the size parameter in all of the commands that I have shown above and consistantly set them to the same size. The reasoning behind this is that by default, the main tempdb file will get created at roughly 8mb (on my system) with an autogrowth of 10%. Setting the size prevents alot of unnecssary autogrowth from happening.
Admittidley, I don't know the best way of setting the autogrowth property for tempdb. For this reason, I have asked the question on Stackexchange - Database Administrators, located here .
How do I move tempdb?
Another common mis-configuration, is to leave tempdb in it's default place. Luckily, this situation can be rectified by running the following code and amending the size and file location properties to suit your environment:
ALTER DATABASE tempdb
MODIFY FILE
(
name = tempdev,
filename = 'C:\databases\tempdb.mdf',
size = 512 MB
)
GO
2
ALTER DATABASE tempdb
MODIFY FILE
(
name = templog,
filename = 'C:\databases\tempdblog.ldf',
size = 512 MB
)
Note: Running this code will require a restart of the SQL Server service. Please bare this in mind before running on any production servers. Aim to run in a maintainence Window.
Always be careful before modifying the tempdb files if you think you have a problem. Use tools such as ostress to record and repeat your workload in a test environment to see if performance can be improved via configuration changes.
Hopefully this will help someone else out as it did me, cheers.