SQL Server Fragmentation- Defragment
-
The table consist of around 2 billion record on an average there are 1000K inserts happening daily , we see some slowness in recent days , so while checking the table we saw some fragmentation information.
Above is the query i used against my dataware database and observed some fragmentation in the index for some tables.
There were around 20k records returned when i run the above query for a particular table (here it is Mytable).
Should we do defragment them all, if so how can i defragment them based on partition number?
The below image is the property of one of index from the table, here we dont find any fragmentation information , since for partition id 1 there were no fragmentation.
-
The screenshots make me wonder if it is worth doing fragmentation work at all. The index size is pretty small with the largest being 15 MB. So rebuilding or reorganizing those indexes isn't going to be that high of I/O, but you will also not see much in terms of space changing and performance I would expect to be very similar before and after. Especially if the table has 2 billion records with 1,000K inserts per day. (Experts - correct me if I am mistaken here).
In your second screenshot, you are showing information on an empty index. There are 0 pages in the index, therefore you have 0 rows in the index. Not a very useful index in my opinion or possibly something is wrong with the partitioning?
Looking at the above, my first thought is how many partitions do you have and how populated are these partitions? For example, if you are showing us the first 7 partitions that have 15 MB of data or less, but you have some partition with 100 GB or more in it, you will likely get a better benefit by adjusting the partition scheme.
That being said, I run SQL 2016 standard so have not had the chance to play with partitions, so someone with more experience in partitions can probably help you more.
BUT, if you do want to defragment the index, you can add on "PARTITION = PARTITION NUMBER" after REORGANIZE or REBUILD and it will only do so on the one partition. See - https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15
TL;DR - With 2 billion rows in the table, I wouldn't bother reorganizing or rebuilding "small" indexes even with high fragmentation as I find they just get fragmented again quickly. I would focus on large indexes with high fragmentation. Since some partitions are empty, it might not hurt to look at your partitioning scheme as well.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
First I would thanks for spending time my problem and valuable suggestion.
There are around ~10K partitions in the table with average of 15MB size and highest begin 97 MB with 77.78% fragmentation.
SSIS packages were used to populate data to the table , were we see time increase in more for insert in recent days when compare to last year,Yes probably we see increase in data in of the reason. Usually entire process would take 20 mins , now it is taking average of 33 mins is what the issue currently. What could be other factors to look into?
- Mr. Brian Gale wrote:
That being said, I run SQL 2016 standard so have not had the chance to play with partitions, so someone with more experience in partitions can probably help you more.
Just so you know, there were a lot of Enterprise Edition features of 2016 added to the Standard Edition as of SP1. One of them is partitioned tables.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
the time the SSIS package is taking to load the data is most likely related to how the processing is implemented than to how the partitions are setup - and for a pure load the fragmentation may not matter much unless the process is updating/inserting into multiple partitions (on which case it is likely that the partitioning was not setup in an optimal way).
would you be able to share the table DDL (including all indexes) and a detailed description of the SSIS package process with regards to identifying, processing and loading the data onto the table?
Screen shot may help here and if the names of the tasks in the package are "confidential" create a copy of it and change the names so you can share it.
if you are using lookups on the package that is most likely the cause for it to be slow.
-
Lordy... looking at this again, I see partition numbers that exceed 5500 partitions. If you divide 5500 by 365, that's 15 YEARs of days if you've partitioned by day. I'm sure that not all of your code enjoys "Partition Elimination" and, so, anything that doesn't has a hell of a problem going through the B-Tree of rough equivalent of 5500 separate super small tables to find things.
With that observation, what in the world have you decided to partition the table on?
That's not the only problem I see here. If you're doing any kind of reporting (which uses more than 1 page at a time), you're reports are really going to suffer in the "Read Ahead" area (which is very important to performance) because 99% fragmentation also usually means and index segment size of "1".
Please modify your code to include the segment size and the "Page Density" (average percent of page fullness) and post the results, please.
Also, stop posting code as graphics. I'd post code with that change but I'm not going to retype it from a bloody graphic. 😉
Also, when was the last time you rebuilt these indexes? And, no... I'm not talking about the last time they were REORGANIZEd, which may be a large part of your fragmentation problem.
It would also be helpful to see the DDL for the table in question and the related indexes.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
its 10k partitions according to the OP
- frederico_fonseca wrote:
its 10k partitions according to the OP
Lordy... I wonder if the partitions are "hourly".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply