“The Only Thing That Is Constant Is Change”

Blocking caused by Tempdb contention.

Posted by database-wiki on April 26, 2011

ISSUE: Customer is experiencing slow performance issues with a custom developed ASP application running on a SharePoint infrastructure.

Data collected:

PSSDIAG. ( I will talk about how to configure and analyze PSSDIAG data in one blog post shortly. )

PSSDIAG Analysis using SQLNEXUS:

Server info:

====================Processor =======================

       Processor Architecture: x64

                    Page Size: 4096

               Processor Type: AMD64

               Processor Type: Family 6, Model 15, Stepping 7

                    CPU Speed: 2660Mhz  (2660000 ticks per ms)

              Cache Line Size: 64

                   Cache Size: 4096KB

        Active Processor Mask: 255 (0x00000000000000FF)

              Hyper-Threading: Not capable

                   Multi-core: Yes

Physical Package/Socket Count: 2

         Processor Core Count: 8 (4 per package)

      Logical Processor Count: 8 (1 per core)

 ====================Memory ==========================

        Total Physical Memory: 16382 MB

        Avail Physical Memory: 270 MB

              % Memory In Use: 98

              Total Page File: 33168 MB

              Avail Page File: 17344 MB

     NUMA Node 0 Avail Memory: 270 MB

 — SQL Server information”

Microsoft SQL Server 2005 – 9.00.4273.00 (X64) Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Server name is ‘SQLSQ’.

The NETBIOS name of the local node that is running the server is ‘DBWSQL’.

— Additional information from Errorlog:

Cannot use Large Page Extensions:  lock memory privilege was not granted.

Detected 8 CPUs.

— Memory usage by SQL Server:

Memory Manager                  KB                

—————————— ——————–

VM Reserved                                16959280

VM Committed                               14343624

AWE Allocated                                     0

Reserved Memory                                1024

Reserved Memory In Use                            0

— sp_configure:

name                                minimum     maximum     config_value run_value 

———————————– ———– ———– ———— ———–

max degree of parallelism                     0          64            0           0

max server memory (MB)                       16  2147483647     10582912    10582912

SQL resource is waiting on Page Latch ( Non I/O )

We also see heavy blocking.

And the headblocker SPID is 152.

Below are the details of head blocker SPID 152

— Top Waits indicate “Page Latch” wait type which is non-I/O wait. This wait means that SQL Server waited for a page which is not retrieved from IO.

— Clearly the problem is waits on non I/O Latches.

— Drilling down into the data shows that this is waits on Update latch and shared latch against 2:1:1 and 2:1:3 – this is the classic tempdb contention problem:

WaitType                                      WaitTime             WaitTimePerSec(ms)

——————————————— ——————– ——————–

PAGELATCH_UP                                  5378500              16103

PAGELATCH_SH                                  3533219              10578

SOS_SCHEDULER_YIELD                           364547               1091

SQLTRACE_BUFFER_FLUSH                         332078               994

LATCH_EX                                      86281                258

SLEEP_TASK                                    31093                93

PAGELATCH_EX                                  16828                50

ASYNC_NETWORK_IO                              11985                35

OLEDB                                         7047                 21

WRITELOG                                      4907                 14

MSQL_XP                                       3782                 11

PAGEIOLATCH_SH                                2922                 8

SQLTRACE_LOCK                                 1328                 3

IO_COMPLETION                                 563                  1

LCK_M_U                                       454                  1

SOS_RESERVEDMEMBLOCKLIST                      250                  0

MSSEARCH                                      203                  0

LATCH_SH                                      140                  0

LCK_M_SCH_S                                   78                   0

CMEMTHREAD                                    46                   0

PAGEIOLATCH_UP                                32                   0

SLEEP_BPOOL_FLUSH                             31                   0

RESOURCE_SEMAPHORE_MUTEX                      16                   0

Action Plan:

1. From the SQL Server Errorlog:

2011-03-20 03:50:56.120 Server Microsoft SQL Server 2005 – 9.00.4273.00 (X64)

Dec  3 2009 10:20:38

Copyright (c) 1988-2005 Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

2011-03-20 03:50:56.170 Server Cannot use Large Page Extensions:  lock memory privilege was not granted.

2011-03-20 03:50:58.920 spid5s Server name is ‘PRDBWSQLSQ’. This is an informational message only. No user action is required.

Give SQL Server’s starup account the right to lock pages in memory so that SQL Server Working Set will not be a victim of Trimming:


2. Increase the number of tempdb data files to be at least equal to the number of processors. Also, create the files with equal sizing.

As of now we only have one TempDB file

E:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf

E:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf

For more info, refer http://msdn.microsoft.com/en-us/library/ms175527.aspx

3. Add trace flag -T1118 which reduces contention. This will in turn fix the blocking cause by the tempdb contention.


4. Set MAXDOP setting in SQL Server to 8 to limit the number of threads spawned by each operator within a plan to the number of Physical processors

sp_configure ‘max degree of parallelism’,8

5. Change Max Memory Settings in SQL Server to a value less than the Physical Memory on the box. Here is an example to set Max. Server Memory of SQL Server to 12 GB:

sp_configure ‘max server memory (MB)’,12288

More Info:

What is TempDB Contention:

Description about three types of pages that store allocation structures as follows:

•         PFS (Page Free Space): it tracks the following key information about each page. It uses 1 byte/page and each PFS page stores status for 8088 pages.

–         Free space available

–         If it is allocated or not

–         Has ghost records (when a row is deleted, it is marked as ghost)

•         GAM (Global Allocation Map): Tracks if a uniform extent is free or not. A bit ‘1’ indicates that extent is available for allocation. Each GAM page tracks 64000    extents or 4GB

•         SGAM (Shared Allocation Map): Tracks if an extent is mixed extent or not. A bit ‘1’ indicates that it is a mixed extent and has one or more free pages. Mixed extents are used to allocate first 7 pages of an object. If number of pages in an object is 8 or larges, its pages are allocated from uniform extents. Each SGAM covers 64000 extents or 4 GB

 So for example, if you have a database file of size 16GB, it will have 4 GAM pages (each GAM covers 4GB), 4 SGAM pages and 256 PFS pages. It is worth mentioning that each file has its own PFS, GAM and SGAM pages.

Now, let us look at a high level what happens during allocation. A page allocation is done when an object is first created or as part of inserting the row that requires a new page to be allocated. Please note, my intent in the following description is not to describe the allocation algorithm of SQL Server (in fact, the allocation mechanism is much more complex and possibly widely different) but to give you an idea how you can get contention in allocation structures.

•         SQL Server looks at the target extent if there are any pages available for allocation by examining the PFS page under SH latch. If a page is found, it is allocated and the PFS information is updated under UPDATE latch. For HEAPs, but not for Btree, the SQL Server will look into other allocated pages with enough free space to store the new row and then updates the new free space information by taking the UPDATE latch on PFS page.

•         If no such page is found, the SQL Server looks at GAM page under SH latch to find a free uniform extent (assuming the object already has > 8 pages). If no such extent is found, then it looks at the next GAM page and so on. When a free extent is found, the SQL Server takes an UPDATE latch on the GAM page and updates the information.

•         If the object has <= 8 pages, a page from mixed extent needs to be allocated. The SQL Server looks at SGAM page under SH latch and if a mixed extent with

 one or more free page is found, the page is allocated and the SGAM page is updated by taking the UPDATE latch

Similarly, when page is deallocated or an object is dropped or when a row is deleted (for HEAPs), the allocation structures need to be updated. As you can now imagine, if there is significant allocation/deallocation activity in a SQL Server database, multiple threads may need to wait to acquire X or SH latch in non-conflicting mode which can lead to allocation bottleneck.

Concurrent thread(s) will need to wait to acquire the page latch on these allocation structures in TempDB thereby slowing the operation. The pages containing allocation structures (GAM, SGAM, PFS) are well known and have fixed page numbers in each file.

•         PFS – 1

•         GAM – 2

•         SGAM – 3

The allocation bottleneck is caused when allocation structures are accessed by concurrent threads in conflicting modes.

More details about tempdb : http://technet.microsoft.com/en-us/library/cc966545.aspx    

=> Doing the above changes did fix the Customer issue.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: