PAGELATCH_EX and PAGELATCH_SH Wait Types and Hash Partition On SQL Server

dbtut

6 years ago

The PAGELATCH_EX and PAGELATCH_SH wait types are errors that occur when writing to the same page in memory at the same time.

These errors usually occur when too many inserts occur at the same time.

To overcome this problem, the table being inserted must be partitioned with hash partition.

Depending on my experience, you may see up to 30 times performance improvement as a result of switching the table to a hash partition structure.

To perform a hash partitioning on the table, you must perform the following operations, respectively.

1)The Partition Function is created.

CREATE PARTITION FUNCTION [pf_hash](tinyint) AS RANGE LEFT FOR VALUES (0,1,2,3,4,5,6,7,8,9,10,11)
GO

1 2	CREATE PARTITION FUNCTION [pf_hash](tinyint) AS RANGE LEFT FOR VALUES (0,1,2,3,4,5,6,7,8,9,10,11) GO

2)The partition scheme is created.

Here we use the filegroup named TABLESGROUP of all partitions.

If you want you can use different filegroups.

CREATE PARTITION SCHEME [ps_hash] AS PARTITION [pf_hash] TO 
([TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP], 
[TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP], 
[TABLESGROUP], [TABLESGROUP], 
[TABLESGROUP], [TABLESGROUP])

GO

CREATE PARTITION SCHEME [ps_hash] AS PARTITION [pf_hash] TO

([TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP],

[TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP],

[TABLESGROUP], [TABLESGROUP],

[TABLESGROUP], [TABLESGROUP])

3)An empty table with the HashID value is created in the structure of the table to be partitioned.

Let’s create a sample table. You should use the script of your own table.

CREATE TABLE dbo.Tmp_TableName
(
pkXX bigint NOT NULL IDENTITY (1, 1),
Time datetime NOT NULL,
HashID  AS (CONVERT([tinyint],abs([pkXX]%(12)))) PERSISTED
)  ON ps_hash(HashID)
GO

CREATE TABLE dbo.Tmp_TableName

(

pkXX bigint NOT NULL IDENTITY (1, 1),

Time datetime NOT NULL,

HashID AS (CONVERT([tinyint],abs([pkXX]%(12)))) PERSISTED

) ON ps_hash(HashID)

With the expression “ON ps_hash (HashID)”, the table is created on the partition.

The column we will use for the partition is written in parentheses.

4)The data in the old table is transferred to the empty table.

The name of the temp table must be changed after the old table is dropped.

BEGIN TRANSACTION
GO
ALTER TABLE dbo.Tmp_TableName SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_TableName ON
GO
IF EXISTS(SELECT * FROM dbo.TableName)
 EXEC('INSERT INTO dbo.Tmp_TableName(pkXX, Time)
SELECT pkXX,Time FROM dbo.TableName WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_TableName OFF
GO
DROP TABLE dbo.TableName
GO
EXECUTE sp_rename N'dbo.Tmp_TableName', TableName', 'OBJECT'
GO
COMMIT

BEGIN TRANSACTION

ALTER TABLE dbo.Tmp_TableName SET (LOCK_ESCALATION = TABLE)

SET IDENTITY_INSERT dbo.Tmp_TableName ON

IF EXISTS(SELECT * FROM dbo.TableName)

EXEC('INSERT INTO dbo.Tmp_TableName(pkXX, Time)

SELECT pkXX,Time FROM dbo.TableName WITH (HOLDLOCK TABLOCKX)')

SET IDENTITY_INSERT dbo.Tmp_TableName OFF

DROP TABLE dbo.TableName

EXECUTE sp_rename N'dbo.Tmp_TableName', TableName', 'OBJECT'

COMMIT

NonClustered Indexes and other objects are also created on the partition in the new table.

Example:

CREATE NONCLUSTERED INDEX IX_Example ON dbo.TableName
(
Time
)
WITH( PAD_INDEX = ON, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON ps_hash(HashID)
GO

CREATE NONCLUSTERED INDEX IX_Example ON dbo.TableName

(

Time

)

WITH( PAD_INDEX = ON, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON ps_hash(HashID)

You should absolutely test it before applying it in production environment.

Also remember that you must close the application until the transfer is complete.