MSSQL Hash Partition

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.

After creating the function, we create a partition schema.

We created a function and schema. We are creating our new table so that we can use it as a Hash Partition.

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.

As seen above, we create the index on the partition scheme named “ps_hash_schema(HashID)” and using HashID.