Tempdb latch竞争的优化

Tempdb latch竞争的优化

一、前言

Tempdb自SQLserver 2005以来，已经成为SQL SERVER Instance的共享（其实一直是共享资源，这里强调下，哈哈）且关键资源，对此资源的优化，有时候对整个Instance的performance的改善有至关重要的作用。

A，Tempdb中包含有：

1， User objects，如local temp table,global temp table,table variable

2， Internal objects,如用于存储group by,order by,union等操作的中间结果的worktable；

3， Version store，下面这些情况会使用version store:

The following features make use of the version store:

➤ Triggers — These have used row versions since SQL Server 2005, rather than scan the transaction log as they did in SQL Server 2000.

➤ Snapshot Isolation and Read-Committed Snapshot Isolation — Two new isolation levels based on versioning of rows, rather than locking. ➤ Online Index Operations — Row versioning to support index updates during an index rebuild.

➤ MARS (Multiple Active Result Sets) — Row versioning to support interleaving multiple batch requests across a single connection.

                 B，监控Tempdb空间的DMV

                          Sys.dm_db_file_space_usage

                       Sys.dm_db_session_space_usage

                       Sys.dm_db_task_space_usage

                  

二、实验：高并发情况下，大量使用temp table造成对tempdb pfs，gam,sgam得latch争用

 

I,实验代码：

USE test_r;

GO

CREATE PROCEDURE [dbo].[usp_temp_table]

AS

CREATE TABLE #tmpTable

(

c1 INT,

c2 INT,

c3 CHAR(5000)

) ;

 

CREATE UNIQUE CLUSTERED INDEX ix_c1 ON #tmptable ( c1 ) ;

 

DECLARE @i INT = 0 ;

WHILE ( @i < 10 )

BEGIN

INSERT INTO #tmpTable ( c1, c2, c3 )

VALUES ( @i, @i + 100, 'hahaha' ) ;

SET @i += 1 ;

END ;

GO

-- Create stored procedure that runs usp_temp_table 50 times

CREATE PROCEDURE [dbo].[usp_loop_temp_table]

AS

SET nocount ON ;

DECLARE @i INT = 0 ;

WHILE ( @i < 100 )

BEGIN

EXEC tempdbdemo.dbo.usp_temp_table ;

SET @i += 1 ;

END ;

 

II,使用ostress做压力测试工具

可以从：http://www.microsoft.com/en-us/download/details.aspx?id=4511

去download，由于ostress需要Microsoft Report Viewer 2008 Redistributable (SP1 or later)，请一并到

http://www.microsoft.com/en-us/download/details.aspx?id=3841下载

Microsoft Report Viewer 2008 Redistributable.

 

III，在ostress所在机器新建一个ODBC数据源，我这里ODBC数据源名是OST

 

 

单块硬盘：

 

开始实验：

A，首先看tempdb只有一个数据文件的情况

D:\Program Files\Microsoft Corporation\RMLUtils>ostress -S172.16.10.222 -DOST -dtest_r -T146 -E -Q"EXEC test_r.dbo.usp_loop_temp_table;" -ooutput.txt -n300

 

参数说明：

-S172.16.10.222 sql server instance名字，这里是默认实例

-DOST ODBC名，这里是OST

-dtest_r,test_r，数据库名

-E 使用windows集成验证

-Q，要执行的SQL命令

-o，日志存放目录

-n300， 并发数，这里是300

更多参数说明，可以看ostress –help

 

                 实验结果：

                    使用

WITH TASKS

AS (

SELECT session_id,

    wait_type,

    wait_duration_ms,

    blocking_session_id,

    resource_description,

    PageID = Cast(Right(resource_description, Len(resource_description)-

    Charindex(':', resource_description, 3)) As Int)

From sys.dm_os_waiting_tasks

Where wait_type Like 'PAGE%LATCH_%' And resource_description Like '2:%')

 

SELECT

    session_id,

    wait_type,

    wait_duration_ms,

    blocking_session_id,

    resource_description,

    ResourceType = Case

    When PageID = 1 Or PageID % 8088 = 0 Then 'Is PFS Page'

    When PageID = 2 Or PageID % 511232 = 0 Then 'Is GAM Page'

    When PageID = 3 Or (PageID - 1) % 511232 = 0 Then 'Is SGAM Page'

    Else 'Is Not PFS, GAM, or SGAM page'

    End

From Tasks ;

查询得到下面的输出,d大量PAGELATCH_SH、PAGELATCH_EX及PAGELATCH_UP的latch争用：

图1

查看下2:1:1是啥页面：

Select  db_name(database_id),page_type from sys.dm_os_buffer_descriptors where database_id=2 and file_id=1 and page_id =1

Output：

   DBNAME  page_type

tempdb  PFS_PAGE

 

A， 将tempdb的datafile增加到CPU数量（我这里8个，每个100MB）

 

USE [master]

GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 102400KB , FILEGROWTH = 102400KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev01', FILENAME = N'E:\SQLDATA\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdev01.ndf' , SIZE = 102400KB , FILEGROWTH = 102400KB )

GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILEGROWTH = 102400KB )

GO

 

 

 

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev02', FILENAME = N'E:\SQLDATA\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdev02.ndf' , SIZE = 102400KB , FILEGROWTH = 102400KB )

 

 

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev03', FILENAME = N'E:\SQLDATA\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdev03.ndf' , SIZE = 102400KB , FILEGROWTH = 102400KB )

 

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev04', FILENAME = N'E:\SQLDATA\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdev04.ndf' , SIZE = 102400KB , FILEGROWTH = 102400KB )

GO

 

 

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev05', FILENAME = N'E:\SQLDATA\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdev05.ndf' , SIZE = 102400KB , FILEGROWTH = 102400KB )

 

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev06', FILENAME = N'E:\SQLDATA\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdev06.ndf' , SIZE = 102400KB , FILEGROWTH = 102400KB )

 

 

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev07', FILENAME = N'E:\SQLDATA\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdev07.ndf' , SIZE = 102400KB , FILEGROWTH = 102400KB )

 

 

再跑：

D:\Program Files\Microsoft Corporation\RMLUtils>ostress -S172.16.10.222 -DOST -dtest_r -T146 -E -Q"EXEC test_r.dbo.usp_loop_temp_table;" -ooutput.txt -n300

 

 

输出：

    WITH TASKS

AS (

SELECT session_id,

    wait_type,

    wait_duration_ms,

    blocking_session_id,

    resource_description,

    PageID = Cast(Right(resource_description, Len(resource_description)-

    Charindex(':', resource_description, 3)) As Int)

From sys.dm_os_waiting_tasks

Where wait_type Like 'PAGE%LATCH_%' And resource_description Like '2:%')

 

SELECT

    session_id,

    wait_type,

    wait_duration_ms,

    blocking_session_id,

    resource_description,

    ResourceType = Case

    When PageID = 1 Or PageID % 8088 = 0 Then 'Is PFS Page'

    When PageID = 2 Or PageID % 511232 = 0 Then 'Is GAM Page'

    When PageID = 3 Or (PageID - 1) % 511232 = 0 Then 'Is SGAM Page'

    Else 'Is Not PFS, GAM, or SGAM page'

    End

From Tasks ;

图2

 

Select  db_name(database_id)  DBNAME,page_type from sys.dm_os_buffer_descriptors where database_id=2 and file_id in (8,9) and page_id =1

 

DBNAME page_type

tempdb   PFS_PAGE

tempdb   PFS_PAGE

 

结果：依然存在大量的PFS争用，但是观察下来的确比单个datafile有所缓解

 

 

修改usp usp_temp_table的代码，如下

 

USE test_r;

GO

alter PROCEDURE [dbo].[usp_temp_table]

AS

CREATE TABLE #tmpTable

(

c1 INT unique clustered,

c2 INT,

c3 CHAR(5000)

) ;

 

--CREATE UNIQUE CLUSTERED INDEX ix_c1 ON #tmptable ( c1 ) ;

 

 

DECLARE @i INT = 0 ;

WHILE ( @i < 10 )

BEGIN

INSERT INTO #tmpTable ( c1, c2, c3 )

VALUES ( @i, @i + 100, 'hahaha' ) ;

SET @i += 1 ;

END ;

GO

 

 

结果：

  依然存在大量的PFS争用

 

 

开启 T1118依然存在大量PFS争用

 

 

 

 

 

 

 

多磁盘，单个tempdb datafile:

 图三

1.jpg

2.jpg

3.jpg

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/101629/viewspace-762429/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/101629/viewspace-762429/