April 9, 2018 at 12:01 pm
Hi,
I'm seeing high waits for pagelatch_up, pagelatch_ex, pagelatch_ex waits and seeing 40 to 50 blocking sessions in SQL server 2016. ETL jobs are running INSERT statements
I have applied CU8 for SQL Server 2016 SP1 but still, I'm seeing blocking with excessive waits for pagelatch_up, pagelatch_ex, pagelatch_ex . Please advise
https://support.microsoft.com/en-us/help/4013999
Thanks
April 9, 2018 at 12:24 pm
Sounds like high volume on transactions that are affecting latching and locking. Options 1) identify the queries taking part, tune them. Option 2) throw lots of money at the server to buy more and faster disks, more and faster memory 3) Lots and lots of page latches, assuming you've already done #1 & #2, can be helped using in-memory tables and stored procedures.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 9, 2018 at 9:49 pm
Hi,
If you have many tables with identity primary key that may cause pagelatch_up, pagelatch_ex, pagelatch_ex going up. You may consider using GUID primary keys if the concurrent insert rate is a priority for your database. But that is a design change.
May 30, 2018 at 12:54 pm
Was this issue resolved?
Prakash B
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply