Latches and Spinlocks - SQL Server 2012 Internals and Troubleshooting - page 208

much quicker to set bits than to squeeze them in. Incrementing a counter that is already in place is a
signii cantly better option than trying to preserve space until it is needed, if the speed of recording
the data is to be maintained.
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 i eld in sys.dm_
exec_requests and, in particular, the i rst number, which indicates the database. The number 2
means that tempdb has the problem.
If PAGELATCH _ UP waits are seen on the i rst page in any of the i les 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 coni rmed by looking at s ys.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 i les it
uses. It is good practice to have more than one tempdb data i le in a multi-threaded environment,
but continually adding new i les 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 dei nition. Finally, the RETURN command is issued, which returns the populated
table variable to the user. Examples of this can be seen in SQL Server Books Online.
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 simplii cation 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.