I have performance issue on MS SQL 2014 db. Store procedure that does SELECT has input parameters as custom Table Value Parameter type. There are 50-100 sessions at the time and executing store proc can take 1s to 14s. I can execute SP with simple input parameter and it can take 1s if I repeat the same it can take up to 14s, it almost if I am feeling lucky or not.
Running diagnostic queries I can see PAGELATCH_UP on many calls to this store procedure and there is a chain of one session id waiting on another session id. PAGELATCH_UP is in the format of resource 2:1:2 2:6:2 2:9:2 depends from the time of capture. So 2:: is tempDb :1: or :6: are tempDb files, there is 8 of them and this is 128GB 32core machine. For the last one ::2 I could found out it is Global Allocation Map (GAM).
In all blogs people have usually have issue with resource ::1 or ::3 (PFS and SGAM), but haven't seen anything regarding GAM.
There is a recommendation from Microsoft how to resolve contention in tempDB which focuses on PFS and SGAM: https://support.microsoft.com/en-gb/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-d
Would you suggest increasing number of tempDb files from 8 to 12, 16, ...32?
Is it issue with table-valued parameters, and this is the reason for pagelatch_up as this is select statement in SP?
Any other replacement for Table Value Parameters?