What should you do?

You have an application that queries a database. Users report that the application is slower than expected.

You discover that several server process identifiers (SPIDs) have PAGELATCH_UP and PAGELATCH_EX waits. The resource descriptions of the SPIDs contains 2:1:1.

You need to resolve the issue.

What should you do?
A . Allocate additional processor cores to the server.
B . Add files to the file group of the application database.
C . Reduce the fill factor of all clustered indexes.
D . Add data files to tempdb.

View Answer

Answer: D

Explanation:

PAGELATCH contention in tempdb is typically on allocation bitmaps and occurs with workloads with many concurrent connections creating and dropping small temporary tables (which are stored in tempdb).

Assuming that the temporary tables are needed for performance, the trick is to have multiple data files for tempdb so that the allocations are done round-robin among the files, the contention is split over multiple PFS pages, and so the overall contention goes down.

References: https://sqlperformance.com/2015/10/sql-performance/knee-jerk-wait-statistics­pagelatch