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

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 particu-
larly 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. prei x 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 i nd that a
spinlock could not be acquired immediately on such an operation, but you may well see this occur-
ring on a system under signii cant 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.
SUMMARY
Latch contention is not something that can be controlled by hints in the same way that locks can.
Latches are designed to protect the very internal structures within SQL Server that hold data, and
they are absolutely necessary.
As the demands on your data increase, with more and more processor threads needing access, even
latches can start to contend for resources. Good design decisions, both schema design and query
design, can typically prevent these problems, however, and you should be able to avoid most latch
contention issues through appropriate planning and awareness.