deleted 210 characters in body

Source Link

edited Sep 13, 2017 at 13:39

user129291

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,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);

msdn

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);

msdn

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);

Modify title to reflect that the issue at hand is related to (a) the physical functioning of the DBMS named SQL Server, not to (b) the Structured Querey Language [SQL].

Source Link

edited Sep 13, 2017 at 2:56

MDCCL

8.5k
3
31
62

SQL Server Latching from Multithreading/Parallel Inserts and Hash Partitioning

We are conducting parallel, multithreading inserts in SQL server tables, andand 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);

msdn

SQL Latching from Multithreading/Parallel Inserts and Hash Partitioning

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);

msdn

SQL Server Latching from Multithreading/Parallel Inserts and Hash Partitioning

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);

msdn

added 216 characters in body

Source Link

edited Sep 13, 2017 at 2:46

user129291

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);

msdn

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);

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);

msdn

added 218 characters in body

Source Link

edited Sep 12, 2017 at 16:53

user129291

Loading

added 218 characters in body

Source Link

edited Sep 12, 2017 at 16:48

user129291

Loading

added 4 characters in body

Source Link

edited Sep 12, 2017 at 12:45

user129291

Loading

deleted 11 characters in body

Source Link

edited Sep 12, 2017 at 12:36

user129291

Loading

added more notes for answer response

Source Link

edited Sep 12, 2017 at 12:29

user129291

Loading

code formatting

Source Link

edited Sep 12, 2017 at 11:30

Dan Guzman

28.5k
2
46
70

Loading

added 21 characters in body

Source Link

edited Sep 12, 2017 at 3:45

user129291

Loading

Source Link

asked Sep 12, 2017 at 3:33

user129291

Loading

Stack Exchange Network

Return to Question

SQL Server Latching from Multithreading/Parallel Inserts and Hash Partitioning

SQL Latching from Multithreading/Parallel Inserts and Hash Partitioning

SQL Server Latching from Multithreading/Parallel Inserts and Hash Partitioning