September 8, 2017 at 11:10 am
Hello ,
I was able to come up with Scripts that Swtich out Data based on boundary values. Everything works, no big deal, but I couldn't get Merge Range script at the end faster, it takes about 5-6 mins to merge each boundary.
Got many massive tables to archive and hence want to find out if there is any solution I am missing.
Tables are Partitioned by timeID, Partition Function created is Range Left. File-groups in Partition schema is Primary.
Is there anything I could help myself to make this merging boundary values faster ?
FYI...This is my very first experience working with Partitioning Thank you.. appreciate your help
September 11, 2017 at 9:05 am
No way to know exactly what's taking place in your environment, but some things that "might" play a role:
1.) disk fragmentation for the spindles where the partitions are located
2.) I/O speed and controller busy % for the spindles involved.
3.) RAM constraints in the server that may not otherwise be having any impact because until this activity began, your RAM usage was just sitting on the edge, so to speak.
You may be able to start seeing what's taking place using perfmon, looking at I/O wait and I/O queue type information.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 11, 2017 at 5:30 pm
Most likely the merge is doing data movement.
I recall a document that highlighted that issue very well but could not find it - but as a general rule range right is easier to deal with.
see https://www.brentozar.com/archive/2013/01/best-practices-table-partitioning-merging-boundary-points/
and http://www.dbdelta.com/table-partitioning-best-practices/
for some examples and explanations.
September 11, 2017 at 9:14 pm
frederico_fonseca - Monday, September 11, 2017 5:30 PMMost likely the merge is doing data movement.I recall a document that highlighted that issue very well but could not find it - but as a general rule range right is easier to deal with.
see https://www.brentozar.com/archive/2013/01/best-practices-table-partitioning-merging-boundary-points/
and http://www.dbdelta.com/table-partitioning-best-practices/
for some examples and explanations.
I agree that some form of data movement is the cause and, oddly enough, it seems to be most prevalent when folks use "LEFT" portioning rather than "RIGHT" probably due to the nature that "RIGHT" seems to be more the way people think (I think it's easier) where "LEFT" isn't. As a result, they end up inadvertently moving data between partitions and, if each partition has its own file/filegroup, you could be waiting a bit for the file to grow as the page splits to happen.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply