0

Is there a separate strategy for inserting / loading data into a table that is partitioned? We are using TVPs inside a single proc to load multiple tables into a SQL Server DB and I did not change the ingest strategy when we switched to a partitioned table. Of course the queries were changed, but nothing for the actual ingest. By strategy, I am simply referring to the actual insert statements. I can't imagine that there is any deviation, but wanted to double check.

I realize that the partition strategy itself can cause contention. For example, if partitioned by date and all of the data being loaded is for the current time period you will be loading into the same partition.

Any suggestions / comments are welcome. I have searched and ready Brent Ozar's articles, but haven't seen anything that would lead me to believe I need to do anything differently.

Below are examples of our scheme and function that we use to partition the data based on a date field that exists within the table. The structures below allow for us to dynamically update the function and schemes to add more date ranges. As you can see from the function, all current rights will contend with each other because they are all hitting the same partition. However, it really helps with our queries.

Partition Scheme

CREATE PARTITION SCHEME [Datetime2_Scheme] 
AS PARTITION [Datetime2_Function] TO (
    [Partition1]
  , [Partition1]
);

Partition Function

CREATE PARTITION FUNCTION [Datetime2_Function](datetime2(7)) 
AS RANGE RIGHT FOR VALUES (
    N'2015-10-01T00:00:00.000'
  , N'2015-11-01T00:00:00.000'
);

As always greatly appreciate the feedback from Stack Overflow folks.

  • What problem are you facing? Also, without a little more detail about the partitioned tables (definitions of the tables, and partitioning functions) along with the code you are using to load the table, we have no idea if you are doing a good or bad thing. – Max Vernon Nov 10 '15 at 16:29
  • Thanks max. I added examples of our functions and schemes – scarpacci Nov 10 '15 at 16:45
  • What's the point of having all partitions on the same filegroup? Also, are you using a single insert statement to insert rows into the partitioned table? Are they date-sorted? Where are the source rows coming from. etc., etc. – Max Vernon Nov 10 '15 at 16:54
  • Max, I will update the question with additional details about the files / filegroup layout – scarpacci Nov 10 '15 at 18:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.