December 8, 2015 at 12:31 am
Hi All,
Does insert operation is costly task when a table is partitioned and a simultaneous operation is done to this table on other partition.
I will try to explain this below.
I created a Partitioned Table below
Create table Staging.POC
(
PartitionKey int,
Id varchar(10)
) ON PS_Staging(PartitionKey)
alter table Staging.POC set (LOCK_ESCALATION = AUtO)
Inserted value to this table
Declare @min-2 int = 1
while(@min < 11001)
begin
insert into Staging.POC(PartitionKey,Id)
select 1,'abc'
end
I opened a session (126)
and started the below operation:
begin transaction _Struct2_
update Staging.POC
set Id = 'b' where partitionKey = 1
Then in other session (129), started below operation:
begin transaction _Structure_
Declare @min-2 int = 1
while(@min < 1000001)
begin
insert into Staging.POC(PartitionKey,Id)
select 20,'abc'
end
On checking the locks with below query
select DISTINCT resource_type,request_session_id,request_mode,request_status,
resource_associated_entity_id,prt.partition_number,prt.rows,count(1) as NumberOfLocks From sys.dm_tran_locks lck
JOIN sys.partitions prt on prt.partition_id = lck.resource_associated_entity_id
where resource_type <> 'Database'
and request_session_id in (126,129) and resource_associated_entity_id in (72057597171138560,72057597169893376)
group by resource_type,request_session_id,request_mode,request_status,
resource_associated_entity_id,prt.partition_number,prt.rows
I see that the Insert operation has acquired 1000000 RID X locks and 1 HOBT IX lock
however UPdated operation has acquired HOBT X locks.
Does locking these many RID's affect performance and what happens if two sessions try to insert data to this table.
Any thoughts or reference
December 8, 2015 at 1:01 am
Each locks uses memory, so the server sometime is doing lock escalation in order to use less locks and by that use less memory for locking. The lock escalation might save you memory (1 table lock uses much less memory then 1000000 row level locks) but it prevents other users from working with this table. In short getting more locks on lower level (for example many row level lock instead of table lock) will cost in terms of memory usage, but there is a good chance that it will improve concurrency and won't block other users from working with the same table on rows that are not affected by the operation. By the way usually modifying 1000000 records in a single transaction is not recommended, but this is a different story.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 8, 2015 at 1:59 am
Yes, but in this scenario (Partitioning) I see the locks are not escalated even after crossing the threshold limit of escalation.
Below is the excerpt from MSDN:
Lock Escalation Thresholds
Lock escalation is triggered when lock escalation is not disabled on the table by using the ALTER TABLE SET LOCK_ESCALATION option, and when either of the following conditions exists:
A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.
A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.
The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.
So, is it that in Partitioned tables the insert operations do not escalate locks and keep it to RID to enable concurrency
December 8, 2015 at 2:06 am
er.mayankshukla (12/8/2015)
So, is it that in Partitioned tables the insert operations do not escalate locks and keep it to RID to enable concurrency
No, it is not.
Escalation isn't guaranteed to succeed. If SQL tries to escalate to a table (or HoBT in the case of a partitioned table) lock and there's an incompatible row or page lock on the table/partition already, the escalation will not complete and SQL will carry on taking the more granular locks and will try again to escalate again if lots more locks are taken.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2015 at 2:07 am
locks escalate in three steps, row, page and then table. Its possible that you're not able to escalate to page for one of a number of reasons and therefore its not escalating.
December 8, 2015 at 2:16 am
Dave Morrison (12/8/2015)
locks escalate in three steps, row, page and then table.
No they don't.
Escalation is straight to table, no matter whether the locks start as row or page. The only time that locks don't escalate to table is with a partitioned table when the lock escalation is set to AUTO, in which case the locks will escalate straight to partition (and no further), no matter whether they start as row or page.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2015 at 2:26 am
GilaMonster (12/8/2015)
Dave Morrison (12/8/2015)
locks escalate in three steps, row, page and then table.No they don't.
Escalation is straight to table, no matter whether the locks start as row or page. The only time that locks don't escalate to table is with a partitioned table when the lock escalation is set to AUTO, in which case the locks will escalate straight to partition (and no further), no matter whether they start as row or page.
Yes you're right, I didn't explain myself properly, I mean they start as either row or page
December 8, 2015 at 2:31 am
Yup, they can start as row or page, but if they start as row locks (KEY/RID), they will either stay as row locks or escalate to table/partition. Row locks can never escalate to page.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2015 at 2:37 am
Indeed, just read what I initially wrote again. Shouldn't be allowed to use my brain before my first coffee 🙂
December 8, 2015 at 4:07 am
In this case there was only a HOBT Lock by a session with update query and RID by a session with Insert query. Both with difference partition key values
So easily the RID could have escalated to HOBT.
This leads me to a different reason correct me if I am wrong :
During insert operation the table is not aware about which partition will be filled as there might be values of more than one partition
Normally in updates/select/delete Sql does partition elimination
Due to this reason, the insert operation doesn't escalates to HOBT instead it keep lock to Row level.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply