PAGELATCH_EX and PAGELATCH_SH Wait Types and Hash Partition On SQL Server

 

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.

 

2)The partition scheme is created.

Here we use the filegroup named TABLESGROUP of all partitions.

If you want you can use different filegroups.

 

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.

 

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.

 

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

 

Example:

 

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

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

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *