How to Get a PAGELATCH_SH in sql SERVER tempdb

Question

I am learning memory optimization in SQL server . On the way I learned about PAGELATCH WAIT .But I don't know how it happens and I don't know how to Test it with optimisation .

Can someone give me an example data that will cause a pagelatch so that I can understand it and test it With and without tempdb memory optimization.

Answer 1

Shared (_SH) latches won't really be a cause of contention in tempdb. You'd more likely see contention around _EX or _UP latches.

To see _UP latches, you can use a tool like SQL Query Stress to spawn many sessions doing something like this:

SELECT
    m.*
INTO #m
FROM sys.messages AS m
WHERE m.language_id = 1033;

To see _EX latches, all you have to do is create a temp table across many sessions:

CREATE TABLE 
    #t
(
    a bigint NOT NULL 
        PRIMARY KEY CLUSTERED,
    b integer NULL,
    c varchar(20) NULL,
        INDEX n (b) 
        WHERE b IS NOT NULL
);

It's up to you to figure out how many sessions to spawn to see contention, and will depend highly on the number of CPUs and the number of tempdb files you have created.