Phát hiện Tempdb bottleneck

Đoạn script phát hiện tranh chấp Tempdb

Select session_id,
wait_type,
wait_duration_ms,
blocking_session_id,
resource_description,
      ResourceType = Case
When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 1 % 8088 = 0 Then 'Is PFS Page'
            When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 2 % 511232 = 0 Then 'Is GAM Page'
            When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 3 % 511232 = 0 Then 'Is SGAM Page'
            Else 'Is Not PFS, GAM, or SGAM page'
            End
From sys.dm_os_waiting_tasks
Where wait_type Like 'PAGE%LATCH_%'
And resource_description Like '2:%'

Tham khảo: http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-TempDB-Contention/

Ở 1 bài viết khác tác giả cũng đưa ra đoạn script tương tự và có câu giải thích rõ ràng hơn như sau:

This query is getting all the PAGE LATCH wait types where the database id is 2 (tempdb). The order for this is dbid:page:type (in the resource_description). If this type is GAM (ex- 2:1:2), SGAM (ex- 2:1:3), or PFS (ex- 2:1:1) it can represent a bottleneck.

http://www.zero1design.com/2010/07/11/one-easy-way-to-optimize-page-allocation-in-tempdb/

1 tài liệu chính thức của Microsoft nói về Tempdb bottleneck

http://support.microsoft.com/kb/2154845

CÁCH KHẮC PHỤC

Khi ta đã phát hiện có tempdb bottleneck, ta cần tìm nhưng câu truy vấn gây ra điều này, có thể dùng tool WhoIsActive. Ta có thể chứng minh điều này bằng cách dùng tool để gây ra Tempdb bottleneck và dùng WhoisActive tool để tìm ra

http://sqlblog.com/blogs/adam_machanic/archive/2011/04/21/analyzing-tempdb-contention-a-month-of-activity-monitoring-part-21-of-30.aspx

cách khắc phục thì ta nên tạo ra nhiều file Tempdb data file, tương ứng với số CPU server. Ta có thể dùng đoạn script sau (lấy từ ebook SQL internal)

ALTER DATABASE tempdb
MODIFY FILE (name=tempdev,size=512MB) ;
GO
ALTER DATABASE tempdb
ADD FILE (name=tempdev2,size=512MB,filename='D:\data\tempdev2.ndf') ;

GO
ALTER DATABASE tempdb
ADD FILE (name=tempdev3,size=512MB,filename='D:\data\tempdev3.ndf') ;

GO
ALTER DATABASE tempdb
ADD FILE (name=tempdev4,size=512MB,filename='D:\data\tempdev4.ndf') ;

It’s not important for them to be on separate drives because you’re not doing it to improve
I/O performance but simply to have more allocation pages.