Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



The request limit for the database is 400 and has been reached
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-01-22









Msg 10928, Level 20, State 1, Line 1
Resource ID : 1. The request limit for the database is 400 and has been reached. See 'http://go.microsoft.com/fwlink/?LinkId=267637' for assistance.



Issue:

- Our Production Azure SQL Database, scaled to P2, had reached the limit for "Premium service tier - P2" with "Max concurrent workers (requests)" = 400 and the database started to slowdown with the majority of the SPIDs being blocked.


Root Cause:

- A 350 million record table with a the Primary Key Column defined as a uniqueidentifier and Clustered.

As the table reached a large size, the insert speed was slowing down tremendously. Over 350 SPIDs were blocked, had a Wait_Type of LCK_M_IX and a Wait_Resource of OBJECT: 3:1043538914:0 - which was the table in question.

With a Clustered Primary Key as a GUID, every insert is put in random location, causing long insert times, causing blocking on Inserts. With SQL Server, when a new row/record is inserted into a table, SQL Server first reads the Page where the row belongs (by key value if the table has a CI) and then either adds the row to the existing data page or allocates a new Data Page if there’s not enough space available in the existing page. With a random primary key value, the new key value is unlikely to be adjacent to the last row inserted (which is likely still in Buffer Cache) so the needed page often must be read from storage. More Physical IO. Long Insert times. Blocking.


Solution:

First, scale Azure SQL Database from a P2 to P4 to avoid the resource error. Second, change the Primary Key to be an Identity Field on INT data type. (Archive the table first - if possible)

Now, with incremental primary key values, the next key value is placed adjacent to the last one inserted and is likely still in memory. An incremental primary key typically provides excellent insert performance. Less physical I/O. Faster Inserts. Greatly reduced Blocking.



Resources:

Azure SQL Database resource limits