March 12, 2014 at 5:13 pm
I am testing doing partitions based on a string value, which will separate out clients (yes, I already know what you're going to say). Each client has a data pull that pulls data into a single table and we're getting lots of locking contention. I was hoping that partitioning would fix that.
Begin setup dump:
DROP DATABASE PartitionThis
GO
CREATE DATABASE PartitionThis
GO
USE PartitionThis
GO
CREATE SCHEMA ph AUTHORIZATION dbo
GO
;WITH Pass0 AS ( SELECT 1 AS C UNION ALL SELECT 1),
Pass1 AS ( SELECT 1 AS C FROM Pass0 AS A , Pass0 AS B),
Pass2 AS ( SELECT 1 AS C FROM Pass1 AS A , Pass1 AS B),
Pass3 AS ( SELECT 1 AS C FROM Pass2 AS A , Pass2 AS B),
Pass4 AS ( SELECT 1 AS C FROM Pass3 AS A , Pass3 AS B),
Pass5 AS ( SELECT 1 AS C FROM Pass4 AS A , Pass4 AS B),
tally AS ( SELECT row_number() OVER ( Order BY C ) AS N FROM Pass5 )
SELECT N
INTO ph.tally
FROM tally
WHERE N <= 100000;
GO
CREATE TABLE dbo.test_without (
ClientCode VARCHAR(10),
Data1 INT,
Data2 VARCHAR(200),
Data3 DATETIME
)
GO
INSERT INTO dbo.test_without
SELECT CASE WHEN N % 3 = 0 THEN 'CLIENT1' WHEN N % 3 = 1 THEN 'CLIENT2' WHEN N % 3 = 2 THEN 'CLIENT3' END, N, CONVERT(VARCHAR(200), NEWID()), DATEADD(dd, 0 - CHECKSUM(NEWID()) % 20, CONVERT(DATE, GETDATE())) FROM ph.[tally]
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG1
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG2
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG3
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG4
GO
CREATE PARTITION FUNCTION pfClientCode (VARCHAR(10))
AS RANGE LEFT FOR VALUES ('CLIENT1', 'CLIENT2', 'CLIENT3')
GO
DECLARE @path NVARCHAR(256), @i TINYINT=1, @sql NVARCHAR(4000);
SELECT TOP 1 @path=LEFT(physical_name,LEN(physical_name)-4)
FROM sys.database_files WHERE name='PartitionThis';
WHILE @i <= 4
BEGIN
SET @sql=N'ALTER DATABASE PartitionThis ADD FILE (name=DailyF' + CAST(@i AS NCHAR(1))+',
filename=''' + @path + N'F'+ CAST(@i AS NCHAR(1))+'.ndf' + ''',
size=128MB, filegrowth=256MB) TO FILEGROUP DailyFG'+CAST(@i AS NCHAR(1))
--show the command we're running
RAISERROR (@sql,0,0)
--run it
EXEC sp_executesql @sql;
SET @i+=1;
END
GO
CREATE PARTITION SCHEME psClientCode
AS PARTITION pfClientCode
TO (DailyFG1, DailyFG2, DailyFG3, DailyFG4)
GO
CREATE TABLE test_with (
ClientCode VARCHAR(10),
Data1 INT,
Data2 VARCHAR(200),
Data3 DATETIME
) ON psClientCode(ClientCode)
GO
ALTER TABLE [dbo].[test_with] SET (LOCK_ESCALATION = AUTO);
GO
CREATE CLUSTERED INDEX C_test_with
ON test_with (ClientCode) ON psClientCode(ClientCode)
GO
INSERT INTO test_with
SELECT * FROM test_without WHERE ClientCode = 'CLIENT1'
GO
After setup, I simulate an insert/delete from two sessions:
BEGIN TRAN
DELETE FROM test_with WHERE ClientCode = 'CLIENT2'
INSERT INTO test_with
SELECT * FROM test_without WHERE ClientCode = 'CLIENT2'
...
BEGIN TRAN
DELETE FROM test_with WHERE ClientCode = 'CLIENT3'
INSERT INTO test_with
SELECT * FROM test_without WHERE ClientCode = 'CLIENT3'
The second session is completely blocked by the first session. Sp_lock shows that there is an IX lock on every partition, an X lock on the partition being updated, and both an IX and X lock on the table.
sp_lock:
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
60 5 0 0 DB S GRANT
60 5 1122103038 0 TAB IX GRANT
60 5 1122103038 0 TAB [UPDSTATS] X GRANT
60 5 1122103038 1 HBT IX GRANT
60 5 1122103038 1 HBT IX GRANT
60 5 1122103038 1 HBT IX GRANT
60 5 1122103038 1 HBT X GRANT
60 5 1122103038 1 HBT IX GRANT
partition locks:
resource_type resource_associated_entity_id which_partition request_mode request_type request_status
------------- ----------------------------- ------------------------------ ------------ ------------ --------------
HOBT 72057594039631872 test_with DailyFG4 IX LOCK GRANT
HOBT 72057594039500800 test_with DailyFG2 X LOCK GRANT
HOBT 72057594039566336 test_with DailyFG3 IX LOCK GRANT
HOBT 72057594039435264 test_with DailyFG1 IX LOCK GRANT
Can anyone explain this behavior? I have tried without Auto Update Stats, I've tried without the Clustered index, I've tried every way I know how to get it to only lock a single partition but it seems to not want to do that.
Thanks in advance,
Jack
[edit]I had forgotten to update the code with the actual LOCK_ESCALATION = AUTO code. Oops 🙂
March 12, 2014 at 8:49 pm
Partitioning helps on something like this but not when you change 50% of the table, which is exactly what you're doing with the Client2 insert. Change that to the following and then run the Client3 insert and see.
BEGIN TRAN
DELETE FROM test_with WHERE ClientCode = 'CLIENT2'
INSERT INTO test_with
SELECT [font="Arial Black"]TOP 2 [/font]* FROM test_without WHERE ClientCode = 'CLIENT2'
If you do the same thing on the normal table, you still get locked out on the Client3 update.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2014 at 11:01 am
Thanks for the reply Jeff. I've updated the data set to more accurately simulate the data I'm actually using, and you are correct that I am not getting an exclusive lock on the table anymore. I'm still getting IX locks on the other partitions which are preventing locks on those tables. Any way I can get around this? When I look at other examples online they do not show their queries being locked on other partitions. Is this because I'm using a varchar?
request_session_id resource_type resource_associated_entity_id which_partition request_mode request_type request_status
------------------ ------------- ----------------------------- ------------------------------ ------------ ------------ --------------
51 HOBT 72057594039959552 test_with DailyFG1 IX LOCK GRANT
51 HOBT 72057594040025088 test_with DailyFG2 X LOCK GRANT
51 HOBT 72057594040090624 test_with DailyFG3 IX LOCK GRANT
51 HOBT 72057594040156160 test_with DailyFG4 IX LOCK GRANT
51 OBJECT 277576027 test_with IX LOCK GRANT
54 HOBT 72057594039959552 test_with DailyFG1 IX LOCK GRANT
54 HOBT 72057594040025088 test_with DailyFG2 Sch-S LOCK GRANT
54 HOBT 72057594040025088 test_with DailyFG2 IX LOCK CONVERT
54 OBJECT 277576027 test_with IX LOCK GRANT
March 13, 2014 at 11:14 am
jack.odom 35193 (3/13/2014)
Thanks for the reply Jeff. I've updated the data set to more accurately simulate the data I'm actually using, and you are correct that I am not getting an exclusive lock on the table anymore. I'm still getting IX locks on the other partitions which are preventing locks on those tables. Any way I can get around this? When I look at other examples online they do not show their queries being locked on other partitions. Is this because I'm using a varchar?
request_session_id resource_type resource_associated_entity_id which_partition request_mode request_type request_status
------------------ ------------- ----------------------------- ------------------------------ ------------ ------------ --------------
51 HOBT 72057594039959552 test_with DailyFG1 IX LOCK GRANT
51 HOBT 72057594040025088 test_with DailyFG2 X LOCK GRANT
51 HOBT 72057594040090624 test_with DailyFG3 IX LOCK GRANT
51 HOBT 72057594040156160 test_with DailyFG4 IX LOCK GRANT
51 OBJECT 277576027 test_with IX LOCK GRANT
54 HOBT 72057594039959552 test_with DailyFG1 IX LOCK GRANT
54 HOBT 72057594040025088 test_with DailyFG2 Sch-S LOCK GRANT
54 HOBT 72057594040025088 test_with DailyFG2 IX LOCK CONVERT
54 OBJECT 277576027 test_with IX LOCK GRANT
I didn't check the locks when I did my test using your code. All I know is that after the small 2 row insert for Client2, I was still able to do the insert for Client3.
Shifting gears a bit, I think partitioning might be like putting a bandaid on stabwound and using it as a "fix" for contention isn't what I'd personally do. Rather, I'd find the contending code and fix it to produce faster and more efficient transactions that don't block nearly as much as they do now. Will it take longer to do? Absolutely? Is it truly the fix you need to make? Absolutely.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2014 at 2:42 pm
I wonder whether this might have something to do with a read-ahead or prefetch operation. I'm testing some theories and will post results later.
Jason Wolfkill
March 19, 2014 at 12:26 pm
Thanks for your replies guys. I appreciate the sentiment of finding a better way but I have tried everything I know of to fix this issue. I get deadlocks and timeouts on SSIS packages, and the current scripts I have take excessive amounts of time to download data, locking various pages which have multiple client data on it. If you have a better solution, I'm all ears.
On the weird locking I am getting, it does appear to be an issue with partitioning based on varchar. If I checksum the strings and set up the partition based on the integer key, I get the exact behavior I was expecting:
-- Create a partition function
CREATE PARTITION FUNCTION ClientPF (int)
AS RANGE RIGHT FOR VALUES (CHECKSUM('CLIENT1'), CHECKSUM('CLIENT2'), CHECKSUM('CLIENT3'))
GO
-- Create partition scheme
CREATE PARTITION SCHEME ClientPS
AS PARTITION ClientPF ALL TO ([PRIMARY])
GO
-- Create partitioned table
CREATE TABLE dbo.test_with
(ClientCodeCS int,
Data1 INT,
Data2 VARCHAR(200),
Data3 DATETIME)
ON ClientPS (ClientCodeCS)
CREATE CLUSTERED INDEX CIX_test_with ON [dbo].[test_with]
( [ClientCodeCS] ) ON ClientPS([ClientCodeCS])
GO
INSERT INTO [dbo].[test_with] SELECT CHECKSUM([ClientCode])
,[Data1]
,[Data2]
,[Data3] FROM [PartitionThis].dbo.[test_without]
GO
-- verify parition details
select count(*) from dbo.[test_with] where $Partition.ClientPF(ClientCodeCS)=1
select count(*) from dbo.[test_with] where $Partition.ClientPF(ClientCodeCS)=2
select count(*) from dbo.[test_with] where $Partition.ClientPF(ClientCodeCS)=3
select count(*) from dbo.[test_with] where $Partition.ClientPF(ClientCodeCS)=4
select count(*) from dbo.[test_with] where $Partition.ClientPF(ClientCodeCS)=5
select MIN([Data1]) from dbo.[test_with] where $Partition.ClientPF(ClientCodeCS)=3
GO
-- enable partition level locking
ALTER TABLE dbo.[test_with]
SET (LOCK_ESCALATION = AUTO);
GO
-- in connection 1 (note: we are not closing the transaction)
BEGIN TRAN
DELETE FROM [dbo].[test_with] WHERE [ClientCodeCS] = CHECKSUM('CLIENT2')
INSERT INTO [dbo].[test_with] SELECT CHECKSUM([ClientCode]), [Data1], [Data2], [Data3] FROM [PartitionThis].dbo.[test_without] WHERE [ClientCode] = 'CLIENT2'
SELECT request_session_id, resource_type = CONVERT(VARCHAR(10), [resource_type]), [resource_associated_entity_id], which_partition = CONVERT(VARCHAR(30), CASE WHEN p.[partition_id] IS NOT NULL THEN OBJECT_NAME(p.[object_id]) + ' ' + CONVERT(VARCHAR(100), rv.value) ELSE so.name END), request_mode = CONVERT(VARCHAR(10), [request_mode]), request_type = CONVERT(VARCHAR(10), [request_type]), request_status = CONVERT(VARCHAR(10), [request_status])
FROM sys.dm_tran_locks l
LEFT JOIN sys.[partitions] p ON l.[resource_associated_entity_id] = p.[hobt_id] AND l.[resource_type] = 'HOBT'
LEFT JOIN sys.indexes si
ON p.object_id = si.object_id
AND p.index_id = si.index_id
LEFT JOIN sys.[objects] so
ON l.[resource_associated_entity_id] = so.[object_id]
LEFT JOIN sys.destination_data_spaces dds
ON si.data_space_id = dds.partition_scheme_id
AND p.partition_number = dds.destination_id
LEFT JOIN sys.data_spaces partitionds
ON dds.data_space_id = partitionds.data_space_id
LEFT JOIN sys.partition_schemes AS ps
ON dds.partition_scheme_id = ps.data_space_id
LEFT JOIN sys.partition_functions AS pf
ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values AS rv
ON pf.function_id = rv.function_id
AND dds.destination_id = CASE pf.boundary_value_on_right
WHEN 0 THEN rv.boundary_id
ELSE rv.boundary_id + 1
END
WHERE [resource_type] <> 'DATABASE' AND (so.[name] = 'test_with' OR p.[object_id] = OBJECT_ID('test_with'))
ORDER BY 1, 2, 4
Using two sessions, this produces the following locks:
request_session_id resource_type resource_associated_entity_id which_partition request_mode request_type request_status
------------------ ------------- ----------------------------- ------------------------------ ------------ ------------ --------------
54 HOBT 72057594040287232 test_with -1727024746 X LOCK GRANT
54 OBJECT 613577224 test_with IX LOCK GRANT
56 HOBT 72057594040221696 test_with -1727024747 X LOCK GRANT
56 OBJECT 613577224 test_with IX LOCK GRANT
No more IX locks on other partitions, meaning other client data can be inserted. Adding an identity to the Client may solve the problem here, so that's where I'm headed next.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply