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?
Best Answer
Is it an issue with table-valued parameters, and this is the reason for pagelatch_up as this is a select statement in SP?
Table-valued parameters create an activity on the tempdb database for this reason, your stored procedure might be caused to tempdb allocation contention. The main symptoms of this issue is to see the following wait types in the tempdb database;
In general, you can solve this problem to create multiple tempdb data files because of the your SQL Server version you might be used to -T1117 and -T1118 flags.
Any other replacement for Table Value Parameters?
On the other hand, using the Memory-Optimized Table Variables can solve this type of problems using the Memory-Optimized Table Types and Table Variables
Would you suggest increasing the number of tempDb files from 8 to 12, 16, ...32?
This is the best practice:
The number of the TempDB data files should match the number of logical processors, up to eight files, on the machine where the SQL Server instance is installed.
However, this value can change according to your requirements.