Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
Windows Server

SQL Server 2012 : Latch Contention Examples - UP Latches in tempdb, Spinlock Contention in Name Resolution

8/24/2014 9:19:07 PM

UP Latches in tempdb

It is possible that the resource your request is waiting on might be in tempdb, rather than the database you have designed. You can see this by looking at the wait_resource field in sys.dm_exec_requests and, in particular, the first number, which indicates the database. The number 2 means that tempdb has the problem.

If PAGELATCH_UP waits are seen on the first page in any of the files in tempdb — that is, page 2:1:1 or 2:4:1 (essentially, 2:N:1 for any N) — then this indicates that the PFS (Page Free Space) page is exhibiting latch contention. This can be confirmed by looking at sys.dm_os_buffer_descriptors:

SELECT page_type
FROM sys.dm_os_buffer_descriptors
WHERE database_id= 2 AND page_id= 1;

A common reaction to any kind of contention in tempdb is to increase the number of data files it uses. It is good practice to have more than one tempdb data file in a multi-threaded environment, but continually adding new files isn’t necessarily the best approach to resolve this problem.

The PFS_PAGE resource must be updated whenever data is inserted into a table without a clustered index — that is, a heap. This doesn’t imply that a heap is necessarily bad; there are many positive things about storing data outside b-trees. However, the PFS_PAGE must be consulted whenever an insert is done, to locate a page with enough free space for the insert.

At this point you’re probably thinking, “But this is tempdb.” However, you haven’t designed your database for tempdb; it’s being used to service your application, and you’ve already made sure that you don’t have latch contention in your own database.

One common cause of this type of contention is the use of multi-statement table-valued functions.

A multi-statement table-valued function declares a table variable, which is populated within the code of the function definition. Finally, the RETURN command is issued, which returns the populated table variable to the user.

This is in contrast to an inline table-valued function, which is handled very differently.

Like a scalar function, a multi-statement table-valued function is executed in a separate context. It is no coincidence that both methods use BEGIN and END and in many ways are more similar to a stored procedure. An inline function does not use BEGIN and END, and is more similar to a view in that the subquery within is extracted into the outer query, not simply the results. The tempdb database is used to store the results of multi-statement table-valued functions, and it is here that contention could occur.

Imagine a scenario in which a multi-statement table-valued function is used in a correlated subquery, such as an EXISTS clause, or in the SELECT clause. Without the ability to perform simplification on the function, the Query Optimizer may well need to call the function many times. This is commonly seen in scalar functions used in the WHERE clause, but it can also be seen when a multi-statement table-valued function is used outside the FROM clause.

The storage used by tempdb for the results of a multi-statement table-valued function must be managed, which involves the PFS_PAGE resource (using UP latches, because the information being updated is not table data, which would require an EX latch), as it determines where new records can be placed, and it marks them as free once the results have been consumed by the outer query. Even a single statement can end up having such a function called many times, causing contention even within a single query.

I’m sure you can imagine some of the ways to avoid this contention. Inline equivalents can be useful; and restructuring the query to avoid using the function in an EXISTS or SELECT clause can also be effective. That’s because latch contention is not just about the database design, but also about the way in which queries are written.

Spinlock Contention in Name Resolution

Unfortunately, developers do not always qualify their object names in their queries. This is particularly common in older applications, originally written in SQL Server 2000 or earlier, before schemas were introduced, but it also occurs in many other systems. It’s very easy to assume that dbo is the only schema used, and to omit the dbo. prefix in table names — using, for example

SELECT * FROM Customers;

instead of

SELECT * FROM dbo.Customers;

This is a simple error to make, and you may not notice any discernible effect on your system until it needs to scale. However, if you don’t specify the schema, the system needs to do a couple of quick checks. It has to determine your default schema, and it has to check whether there is a table with that name in your default schema. If not, it has to check the dbo schema to see if that’s what you meant.

All this can happen very quickly — so quickly that a spinlock is used. It would be rare to find that a spinlock could not be acquired immediately on such an operation, but you may well see this occurring on a system under significant load. The contention appears on the SOS_CACHESTORE spinlock type. Fortunately, it’s simple to resolve: Just ensure that you always fully qualify your table names.

Other -----------------
- SQL Server 2012 : Latch Contention Examples - Queuing
- SQL Server 2012 : Latch Contention Examples - Inserts When the Clustered Index Key Is an Identity Field
- SQL Server 2012 : Latches and Spinlocks - Monitoring Latches and Spinlocks
- SQL Server 2012 : Latches and Spinlocks - SuperLatches/Sublatches
- SQL Server 2012 : Latches and Spinlocks - Latch Types, Latch Modes
- Sharepoint 2013 : Overview of The Client-Side Object Model and Rest APIs - Client-Side Object Model API Coverage
- Sharepoint 2013 : Overview of The Client-Side Object Model and Rest APIs - REST and OData (part 3) - Creating, Updating, and Deleting
- Sharepoint 2013 : Overview of The Client-Side Object Model and Rest APIs - REST and OData (part 2) - Filtering and Selecting
- Sharepoint 2013 : Overview of The Client-Side Object Model and Rest APIs - REST and OData (part 1) - Getting Started with REST and OData
- Sharepoint 2013 : Integrating Apps for Office with SharePoint (part 2) - Apps for Office Integrated with an App for SharePoint
 
 
Top 10 video Game
-   Skyforge [PC] Open Beta Gameplay Trailer
-   Armored Warfare [PC] PvE Trailer
-   F1 2015 [PS4/XOne/PC] Features Trailer
-   Act of Aggression [PC] Pre-Order Trailer
-   Sword Coast Legends [PC] Campaign Creation E3 2015 Trailer
-   Sword Coast Legends [PC] Campaign Creation E3 2015 Dungeon Run Trailer
-   Naruto Shippuden: Ultimate Ninja Storm 4 Trailer
-   Danganronpa Another Episode: Ultra Despair Girls Trailer 2
-   Project X Zone 2 Trailer
-   Poly Bridge Early Access Trailer
-   Rodea The Sky Soldier Trailer
-   CABAL 2 Launch Trailer
-   The Smurfs Trailer
-   Act of Aggression Pre-Order Trailer
-   Project X Zone 2 [3DS] Trailer
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
programming4us programming4us
PS4 game trailer XBox One game trailer
WiiU game trailer 3ds game trailer
Trailer game
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer