June 1, 2017 at 1:21 am
I have a table with two indexes. It has showing 60% fragmented.
So tried rebuild the indexes. Still indexes are showing 60% fragmented.
How Can I go ahead here?
June 1, 2017 at 1:27 am
krishnabudampati - Thursday, June 1, 2017 1:21 AMI have a table with two indexes. It has showing 60% fragmented.
So tried rebuild the indexes. Still indexes are showing 60% fragmented.
How Can I go ahead here?
How big is the table in pages? If the table is very small you won't see any difference.
Thanks
June 1, 2017 at 3:23 am
Hi,
how did you rebuild the index. With a maintenance plan, or with some scripts from Ola Hallengren?
In this script are some values, to control the script, not to rebuild index with less than xxx rows.
Kind regards,
Andreas
June 1, 2017 at 4:20 am
I rebuild indexes using script.
June 1, 2017 at 4:57 am
krishnabudampati - Thursday, June 1, 2017 4:20 AMI rebuild indexes using script.
And what are the sizes of the tables please?
June 1, 2017 at 5:50 am
This was removed by the editor as SPAM
June 1, 2017 at 6:45 am
JasonClark - Thursday, June 1, 2017 5:50 AMRebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. see here for more information about SQL Server index Optimization
Except when the index is stored on a single extent or on mixed extents when the index is less than 8 pages in size. That's why several other people have asked how big the index is. If it's on one extent, defragmentation doesn't really work, nor is it necessary, at all. Further, with the exception of columnstore indexes, one could argue that index defragmentation is a total waste of time if a system has well-tuned queries and well-maintained statistics. Of the three choices, fragmentation, queries or statistics, fragmentation is near the bottom of my list (except for columnstore indexes).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2017 at 4:43 am
krishnabudampati - Thursday, June 1, 2017 1:21 AMI have a table with two indexes. It has showing 60% fragmented.
So tried rebuild the indexes. Still indexes are showing 60% fragmented.
How Can I go ahead here?
Check how many pages the table have. Rebuild and reorg ignores tables with less than 1000 Pages, that might be the case with you.
June 4, 2017 at 4:48 am
VastSQL - Sunday, June 4, 2017 4:43 AMRebuild and reorg ignores tables with less than 1000 Pages, that might be the case with you.
They don't.
Most custom scripts exclude indexes under 1000 pages, because there's little use in rebuilding them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2017 at 5:15 am
GilaMonster - Sunday, June 4, 2017 4:48 AMVastSQL - Sunday, June 4, 2017 4:43 AMRebuild and reorg ignores tables with less than 1000 Pages, that might be the case with you.They don't.
Most custom scripts exclude indexes under 1000 pages, because there's little use in rebuilding them.
Thanks Gail , Good to know.
But I have tried rebuild indexes with small page count and the fragmentation value didn't change.
June 4, 2017 at 9:47 am
VastSQL - Sunday, June 4, 2017 5:15 AMGilaMonster - Sunday, June 4, 2017 4:48 AMVastSQL - Sunday, June 4, 2017 4:43 AMRebuild and reorg ignores tables with less than 1000 Pages, that might be the case with you.They don't.
Most custom scripts exclude indexes under 1000 pages, because there's little use in rebuilding them.
Thanks Gail , Good to know.
But I have tried rebuild indexes with small page count and the fragmentation value didn't change.
No, it won't. Doesn't mean that the rebuild ignored that index, just that it rebuilt and had no effect
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 5, 2017 at 1:43 am
GilaMonster - Sunday, June 4, 2017 9:47 AMVastSQL - Sunday, June 4, 2017 5:15 AMGilaMonster - Sunday, June 4, 2017 4:48 AMVastSQL - Sunday, June 4, 2017 4:43 AMRebuild and reorg ignores tables with less than 1000 Pages, that might be the case with you.They don't.
Most custom scripts exclude indexes under 1000 pages, because there's little use in rebuilding them.
Thanks Gail , Good to know.
But I have tried rebuild indexes with small page count and the fragmentation value didn't change.No, it won't. Doesn't mean that the rebuild ignored that index, just that it rebuilt and had no effect
Thanks a lot Mam..
June 5, 2017 at 9:50 am
The table has 15000 rows only.I observed 60% indexes fragmentation.
We are deleting approximately 12000 rows daily and inserting updated data again into the table.
June 5, 2017 at 10:01 am
Adding to the above replay.
I have a table, we are deleting data 90% of the data with in that table and inserting again.
Due to heavy deletion, We have been observing fragmented indexes on that table and I am rebuilding indexes those indexes.
Here I want know about allocated space of deleted rows in the table.
Does table require reorganization to improve performance?
or
rebuilding of indexes & update statistics of the table are enough to improve performance.
Please post your value comments here.
June 5, 2017 at 12:13 pm
krishnabudampati - Monday, June 5, 2017 10:01 AMAdding to the above replay.I have a table, we are deleting data 90% of the data with in that table and inserting again.
Due to heavy deletion, We have been observing fragmented indexes on that table and I am rebuilding indexes those indexes.
Here I want know about allocated space of deleted rows in the table.
Does table require reorganization to improve performance?
or
rebuilding of indexes & update statistics of the table are enough to improve performance.Please post your value comments here.
If you rebuild an index, that updates the statistics.
The question is, are you rebuilding the index before or after you delete 4/5 of the data? Also, how does data get back into the table during the day? A trickle or a batch? All of this could affect the state of the statistics which will affect performance more than fragmentation (depending on your queries, etc.). By and large fragmentation is a major performance issue when you have lots of scans. However, eliminating the scans should be your goal, fragmentation or not. If you have lots of point lookups, fragmentation shouldn't matter much.
Have you evaluated the performance from stand point of looking at waits, execution plans, and stuff like that?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply