July 26, 2021 at 1:16 pm
Good day,
What would cause fragmentation on the Unique Clustered index on a table (its one and only index) that is only ever read from, and never written to?
Thanks,
P
July 26, 2021 at 3:45 pm
What is the unique clustered index on that table? If the table is never written to - how did the data get in the table in the first place? A table with no data won't have any fragmentation.
If you are assuming that an identity column set as the clustered index somehow prevents a table from experiencing fragmentation - that is not correct. If you are assuming an identity column prevents fragmentation when the data is loaded, that too is incorrect.
A good example is a load process that goes parallel - that parallel process can and will load data out of order. The only way to ensure that a clustered identity index does not fragment when the table is loaded is to load the table one row at a time.
If the clustered unique index is not an identity column - the only way to ensure no fragmentation when loading data is to again, insert a single row at a time in the clustered index order.
Once loaded, an offline rebuild of the table should remove all fragmentation - unless mixed extents are used for that index.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 26, 2021 at 3:47 pm
My understanding - that shouldn't ever happen.
If the data isn't changing, the data shouldn't be getting fragmented. INSERT, UPDATE, and DELETE operations would cause fragmentation, SELECTs shouldn't.
My guess - something is changing the data even if you are not aware of it. I would probably put an INSERT, UPDATE, DELETE trigger on the table and have it update an audit log and you can watch for data changes.
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.
July 26, 2021 at 3:52 pm
Ah, I should have been clearer. It is dropped and re-created monthly, populated with 1.8m rows, and then create the index. Within 2 days, the dm tables are showing fragmentation.
Could backups be doing it? I realize I don't need to back it up, just easier to backup everything without hard-coding exceptions.
July 26, 2021 at 4:08 pm
Are the indexes created before or after populating the data? I would encourage you to create them AFTER populating the data OR rebuild them after populating the data.
Backups don't modify data, so they will not cause fragmentation. If backups could modify data, you wouldn't be able to trust the backup and thus wouldn't have any way to restore without potential for data changing. The exception to this is the backup history tables which obviously need to be changed after a backup otherwise it is not a very good history table.
Indexes get fragmented when data changes. So any process that changes your data can result in fragmentation. If the data is getting fragmented, it means something is changing it. Based on my understanding of indexes, SOMETHING is running an INSERT, UPDATE, or DELETE after you have created your indexes on the table.
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.
July 26, 2021 at 4:59 pm
As mentioned, the index is created post-load.
Thanks for taking the time to respond. Trigger, here I come!
I'll let you know what I find.
Thanks again
P
July 26, 2021 at 5:12 pm
Before doing a trigger, may not hurt to look at other options. Extended events for example.
Triggers can be painful if you forget to drop them when you are done.
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.
July 26, 2021 at 5:16 pm
Won't be around for long, methinks.
Also, still on 2008, and XE was not friendly in the slightest back ...er... now 🙂 That said, can't wait to start playing with it in our brand spankin' new 2016 servers. And yes, I know what last Monday was...
July 26, 2021 at 5:39 pm
Ah, I should have been clearer. It is dropped and re-created monthly, populated with 1.8m rows, and then create the index. Within 2 days, the dm tables are showing fragmentation.
Could backups be doing it? I realize I don't need to back it up, just easier to backup everything without hard-coding exceptions.
Are you checking fragmentation after the index creation? Or is this just something you check a couple days later and find?
I would not create a clustered index after the load - I would have the clustered index already created and perform a rebuild after the load. I haven't found there to be a significant difference in the load times with or without the clustered index.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 26, 2021 at 5:54 pm
Couple of days later. Wish I had the query handy, but it's on my other workstation, the one with the fried HD from a power blip last Friday. Good thing we RAIDed our programmers' machines...
Process is Drop, Create, Populate, Index.
I found dropping and re-creating the table was faster than truncate/delete, even with recovery mode = simple, back in SQL 6.5
August 9, 2021 at 2:08 pm
So, the trigger has been in place, but has not fired. It's simple: On I/U/D, send me an email with the user_name().
I rebuilt the index on Friday morning, this morning, it's at 66.42310000353% (always the same number).
August 9, 2021 at 2:47 pm
So, the trigger has been in place, but has not fired. It's simple: On I/U/D, send me an email with the user_name().
I rebuilt the index on Friday morning, this morning, it's at 66.42310000353% (always the same number).
What is that number coming from, please? Is it the average percent of fragmentation or the average percent of page fullness?
And what is the value immediately after you add the index or is that what your previous post states?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2021 at 3:00 pm
It's coming from Table - Indexes - Reorganize. The underlying source is sys.dm_db_index_physical_stats, I believe.
After load - create index, as well as after rebuild or reorg, avg_fragmentation_in_percent = 0
August 9, 2021 at 3:11 pm
Crud. My apologies. I hit "report" on your last post by mistake and it doesn't provide an "are you sure question".
Anyway, you said...
It's coming from Table - Indexes - Reorganize. The underlying source is sys.dm_db_index_physical_stats, I believe.
After load - create index, as well as after rebuild or reorg, avg_fragmentation_in_percent = 0
That's the number that goes up to 66% in 2 days? If that's true, someone is doing updates, inserts, or deletes somewhere and your trigger isn't catching it.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2021 at 3:38 pm
So... since the trigger isn't catching it, is my next step Extended Events?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply