SQL insert paused due to pagelatch_ex
I have an MS SQL 2008 database with several thousand records. Suddenly, one of the tables cannot accept a new row insert. Edit and remove is still good. Activity Monitor shows this as:
Task status: suspended Command: Paste Wait Type: Pagelatch_ex
Nothing happens after a few minutes of waiting. If I delete one of the last (bottom) 200 (scores) rows in this table, I can only insert one new row. The insertion of the second line is paused again. If I remove 3 lines from the last 200 lines, I can only insert 3 new lines. But if I delete a line not in the last 200, I cannot insert the line.
After entering a new line, the new ID goes to the top in the SSMS results view, not the bottom (no sorting). For example: 100, 101, 102, 95, 96, 97, 98, 99, where 100 and above are newly inserted data.
The table now contains only 2 columns, an ID (primary column, seed ID) and an InvoiceID column. All indexed views, tables and constraints are dropped with the same problem.
I just found out that removing the primary key for the ID column and re-setting it solves the problem. However, the sorting for the ID is still confusing. The new line is positioned at the top, and the old lines are shifted down.
What is causing this problem and how can I prevent it? Thank.
source to share
Pagelatch_ex is raised when there is a request for a page that needs to be modified. Generally, the latch is short-lived and does not affect performance. However, you may run into problems if you have a slow I / O subsystem or other resource bottlenecks. You need to first determine which database / table is involved and from there.
Start by troubleshooting technical issues . It provides steps to troubleshoot various performance issues - how to use DMVs, performance counters, and more.
source to share