如何检查SQL Server tempdb瓶颈

---确认分配瓶颈
SELECT session_id,wait_duration_ms,resource_description
from sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGE%LATCH_%' AND
 resource_description LIKE '2:%'
 
 --查询tempdb中当前引发最多分配和回收操作
 
 SELECT TOP 10
 t1.session_id
 ,t1.request_id
 ,t1.task_alloc
 ,t1.task_dealloc
 ,t2.plan_handle,
 (SELECT SUBSTRING(text,statement_start_offset/2+1,
        (CASE WHEN statement_end_offset=-1
        THEN LEN(CONVERT(NVARCHAR(max),text))*2
        ELSE statement_end_offset
        END -statement_start_offset)/2)
    from sys.dm_exec_sql_text(sql_handle)
    ) AS query_text
from
 (SELECT session_id,request_id
    ,SUM(internal_objects_alloc_page_count+user_objects_alloc_page_count) AS task_alloc
    ,SUM(internal_objects_dealloc_page_count+user_objects_dealloc_page_count) AS task_dealloc
    from sys.dm_db_task_space_usage GROUP BY session_id,request_id) AS t1,
    sys.dm_exec_requests AS t2
 WHERE t1.session_id=t2.session_id AND
 (t1.request_id=t2.request_id) AND
 t1.session_id>50
 ORDER BY t1.task_alloc DESC
 
 CREATE TABLE t1(c1 INT PRIMARY KEY,c2 INT ,c3 CHAR(8000))
 Go
 CREATE TABLE t2(c4 INT ,c5 CHAR(8000))
 
 DECLARE @i INT
 SELECT @i=0
 WHILE(@i<6000)
 BEGIN
 INSERT INTO t1 VALUES(@i,@i+1000,'hello')
 INSERT INTO t2 VALUES(@i,'there')
 SET @i=@i+1
 END
 
 SELECT c1,c5 from t1 INNER HASH JOIN t2 ON t1.c1=t2.c4
 ORDER BY c2
 
 --查看该查询计划
 SELECT * from sys.dm_exec_cached_plans
 WHERE plan_handle=0x06000700B764A91BB8610911000000000000000000000000
 
 --确认是否有临时表正缓存一个存储过程