In this article, we will discuss the use of hash partitions to prevent slowdowns caused by large numbers of insert operations in SQL Server and to improve performance.
When there is a “PAGELATCH_EX” wait type in SQL server, hash partition prevents this and produces a faster solution.
Hash partition is a method generally used to prevent waiting while writing to the same page, and we can speed up our inserts with this method.
First, we create our partition function.
|
1 2 3 |
CREATE PARTITION FUNCTION [p_hash_funct](tinyint) AS RANGE LEFT FOR VALUES (0,1,2,3,4,5,6,7,8,9,10,11) GO |
After creating the function, we create a partition schema.
|
1 2 |
CREATE PARTITION SCHEME [ps_hash_schema] AS PARTITION [p_hash_funct] TO ([TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP], [TABLESGROUP]) GO |
We created a function and schema. We are creating our new table so that we can use it as a Hash Partition.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE dbo.frk_d1 ( pkDashboard bigint NOT NULL IDENTITY (1, 1), hastane varchar(20) NULL, tarih date NULL, tanim varchar(20) NULL, deger int NULL, Aktarim_Zamani datetime NOT NULL, HashID AS (CONVERT([tinyint],abs(binary_checksum([pkDashboard]%(30))))) PERSISTED NOT NULL) ON [ps_hash_schema](HashID) GO |
If we pay attention to our “HasHId” column above, we see that it is written as
“AS (CONVERT([tinyint],abs(binary_checksum([pkDashboard]%(30))))) PERSISTED NOT NULL) ON [ps_hash_schema](HashID)”,
Here we add a parameter to create our table named “dbo.frk_d1” on the partition schema we created, and ensure that it is created with a partition.
We have created our table, but we need to add our indexes as follows while creating them.
|
1 2 3 4 5 6 |
CREATE UNIQUE CLUSTERED INDEX CIX_Hash ON dbo.frk_d1 ( pkDashboard, HashID ) WITH( PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ps_hash_schema(HashID) GO |
As seen above, we create the index on the partition scheme named “ps_hash_schema(HashID)” and using HashID.
