We are conducting parallel, multithreading inserts in SQL server tables, and want to reduce latching.
What are the disadvantages of utilizing hash partitioning to reduce latching? Is it reduced query slowness from essentially querying all these split up partition tables?
We have around 120 Table Inserts Per Second, financial system.
Other Notes: SQL 2016 System will use around 50 GB of SSD Hard drive space a year. Currently, have 50 Core processors, and 150 GB of RAM.
Platform is not built, so no baseline to test; but I need to develop testing plan and strategies.
Hash Partitioning Example: http://www.madeiradata.com/how-to-solve-the-tail-insert-problem-2/
CREATE PARTITION FUNCTION pf_hash (TINYINT) AS RANGE LEFT FOR VALUES (0,1,2,3,4,5,6,7,8); CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY]); CREATE TABLE dbo.UserEntries_RegularWithHash ( Id BIGINT IDENTITY NOT NULL, UserId INT NOT NULL , CreatedDate DATETIME2 NOT NULL, HashId AS CAST(Id % 9 AS TINYINT) PERSISTED NOT NULL, CONSTRAINT PK_UserEntries_RegularWithHash PRIMARY KEY CLUSTERED (Id,HashId) ) ON ps_hash(HashId);