Monitoring SQL Server Resources with System Monitor - Professional SQL Server 2005 Performance Tuning - page 52

Tempdb is shared by all databases within an instance. At times it can itself become a resource bottleneck.
It's therefore important that the configuration is optimized to make best use of the resources available.
One of the ways to achieve this is to create a separate data file per CPU. This allows CPUs round-robin
access to the tempdb files, often achieving better performance than with a single data file.

Best Practice
Create one tempdb data file per CPU or core. (Hyperthreaded processors aren't included.)
This will help achieve better Tempdb performance, particularly where user activity is
resource-intensive on Tempdb.

ContentionWithinTempdb
Tempdb is transient — it only exists while the SQL Server service is running. Tempdb is a shared global
resource. This means if one database or application is heavily dependent on tempdb, other
databases within the same instance may suffer performance problems which are outside their control.

There are a number of features new to SQL Server 2005, such as online index rebuilds, where a version
store can be held within tempdb to maintain data availability during index rebuild operations. System
monitor counters are available to report free space in tempdb and the version store size, and the following
counters can be useful to get an idea of the tempdb workload:

SQLServer:General Statistics — Temp Tables Creation Rate: Reveals the number of
temporary tables or variables created per second.

❑

SQLServer:General Statistics — Temp Tables For Destruction: Shows the number of
temporary tables that are no longer required and are awaiting destruction by the system thread
that carries out cleanup.

❑

Additionally, there are a number of DMVs that may help you understand the nature of use of tempdb.
One common concern is to understand the top consumers of space within tempdb. This can be ascer-
tained by joining the sys.dm_db_task_ space_usage and sys.dm_exec_requests. Here's a sample query
that maybe useful in determining tempdb space used by each server process:

select tsu.session_id
, tsu.request_id
, tsu.alloc_count
, tsu.dealloc_count
, er.sql_handle
, er.statement_start_offset
, er.statement_end_offset
, er.plan_handle
from (
select session_id
, request_id
,sum (internal_objects_alloc_page_count) as alloc_count
,sum (internal_objects_dealloc_page_count) as dealloc_count
from sys.dm_db_task_space_usage
group by session_id, request_id
) as tsu
, sys.dm_exec_requests as er