Concurrency blocking for tempdb

Concurrency blocking for 9.3 tempdb

Before describing the concurrency problem with tempdb, let's introduce a few more special data pages.

PFS (page free space), which identifies the usage of the data page space, is identified in bytes and can represent data page usage percentages, such as using percent 50, percent 80, percent 95, and fully used, as well as a byte bit representing the type of data page, such as an IAM page. A PFS page that identifies 64MB of data page space usage.

GAM (Global Allocation Map), used to identify whether the data extents (Extent) have been assigned, in bits, when bit is 0 o'clock, indicating that the extents are not allocated, when the bit is 1 o'clock, that the extents have been allocated. A GAM page can identify approximately 4GB of file space identification.

SGAM (Shared Global Allocation Map) that identifies whether the mixed data extents are assigned, in bits. The data may be stored in a mixed space, or the index may be stored. A Sgam page can identify approximately 4GB of data file mixed-space identities.

In tempdb, the data characteristics of the staging table are described in the previous section, when a temporary table is created in the session and initialized, the data space is allocated, the temporary table is deleted in the session, or the session ends, the space is reclaimed. Data is added and deleted, all need to use to the data page, when the data page storage space changes, PFS's byte identity value needs to change. When such an operation occurs frequently within the same piece of data page space area. As you know in the previous latch, the intrinsic page is updated with a latch to achieve mutual exclusion, and when concurrent operations accumulate to a specific value, latch waits occur frequently, which can cause server CPU usage to rise and even CPU load to occur.

For this reason, it is generally recommended to increase the number of data files in tempdb to the same number of logical CPUs. This allows the data to be diverted to avoid frequent erasure of data within the same data region.

The same situation may occur on the GAM and SGAM pages, except that the GAM and SGAM require a large amount of data for the temporary table.

In addition to the concurrent blocking problems of Pfs,gam and several root pages of Sgam, tempdb may also have problems with system table blocking due to frequent creation of temporary tables. For example, in a stored procedure with high concurrent access, a temporary table is created using SELECT INTO, which can cause the system's metadata table when concurrent access is high enough, or the query statement takes longer and has a certain amount of concurrency. For example, the sys.objects view corresponds to the system metadata table SYSSCHOBJS or sys.columns view corresponding to these system metadata tables Syscolpars, these system metadata may also be due to high concurrency and long transactions resulting in a large number of system congestion. The way to solve this problem is that when you create a temporary table, you should not include statements that create temporary tables in longer transactions. Avoid the system tables in tempdb from being locked, causing other processes to not create or manipulate temporary tables properly.

Also, in the case of higher concurrency, to achieve better data throughput for tempdb, it is recommended that data files be placed on separate disks to improve disk throughput.

Next, we'll use a test tool named Ostress. is a Microsoft-provided SQL Server Test Tools component RML Utilities for SQL Server, you can download the tool on the Microsoft website, this tool component is free.

tempdb concurrency sample

The following links are 64-bit tools:

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

The following are the 32-bit tools:

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

After downloading the test software and installing, we need to create the corresponding stored procedure in the database, stored procedure Code as shown in Listing 9-5, create two stored procedures, usp_temp_table_test as a sub-stored procedure, Usp_loop_test_table_ The test loop calls it.

CREATE PROC Dbo.usp_temp_table_test

As

BEGIN

???? CREATE TABLE #table (C1 int,c2 CHAR (5000));

???? DECLARE @i int=1;

???? while (@i<=10)

???? BEGIN

???????? INSERT into #table (C1,C2) VALUES (@i, ' test ');

???????? SET @i+=1;

???? END

END

GO

?

CREATE PROC Dbo.usp_loop_temp_table_test

As

BEGIN

???? SET NOCOUNT on;

???? DECLARE @i int=1;

???? while (@i<100)

???? BEGIN

???????? EXEC dbo.usp_temp_table_test;

???????? SET @i+=1;

???? END

END

GO

Code Listing 9-5 tempdb concurrency blocking test

After creating the corresponding code, using ostress to simulate user concurrency, execute the following Windows batch instructions, as shown in Listing 9-6, using ostress to simulate 300 concurrent executions of the stored procedures we created.

"C:\Program Files\Microsoft Corporation\rmlutils\ostress.exe"-S (local) \SQL2012-E-Q "exec [ADVANTUREWORKS2008R2]. dbo.usp_loop_temp_table_test; "-O" D:\output.txt "-n300

Code Listing 9-6 executing the ostress tool

When executing ostress, use the dynamic management view to view the latch waits for the tempdb memory page in the current server memory, the following statement in the county, query the dynamic management view sys.dm_os_waiting_tasks to get the appropriate blocking information, as shown in code listing 9-7.

; With Waiting_tasks

As (SELECT session_id,

???????????????????? Wait_type,

???????????????????? Wait_duration_ms,

???????????????????? BLOCKING_SESSION_ID,

???????????????????? Resource_description,

PageID = CONVERT (Int,right (Resource_description,len (resource_description)-charindex (': ', resource_description,3)) )

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 ' PFS Page '

When pageid=2 OR pageid%511232=0 then ' GAM Page '

When Pageid=3 OR (PageID-1)%511232=0 then ' SGAM Page '

ELSE ' not PFS, GAM, or SGAM page '

END

From Waiting_tasks;

Code Listing 9-7 Viewing the current latch situation in tempdb

With the query statement, you can see that the server is mostly a latch wait for the PFS data page, as shown in 9-5.

Figure 9-5 Latch waits for the server

As seen from the execution results, 297 of the 300 concurrent tasks are waiting for the data page 2:1:1, where the page is a PFS data page with a wait type of pagelatch_up type. This is caused by the fact that tempdb has only one data file in the instance, which in high concurrency causes the congestion caused by the concurrent update of the data page.

9.3.1 Configuring tempdb

How to configure tempdb avoids the problem in figure 9-5, as we've already mentioned in the previous section, the way to increase concurrency is to add data files, separate the concurrency, and avoid manipulating the same file. So what do you need to be aware of when configuring multiple files?

SQL Server when writing data to a database with multiple files, the database engine allocates data according to the size of each data file, so when you configure multiple files for tempdb, keep the data file size of tempdb consistent. This can be achieved by achieving near average usage and allocation of data files when storing tempdb data in order to achieve an average use of the Pfs,gam in each file and the purpose of sgam these root data pages.

Concurrency blocking for tempdb