SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance improvement following a disable indexes, shrink, enable indexes - but why?


Performance improvement following a disable indexes, shrink, enable indexes - but why?

Author
Message
pcd_au
pcd_au
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 499
I'm stumped and need the assistance of the wider community to understand what is going on.
The database in question has minimal fragmentation, but performance is slow. We go through the following process:
  • Disable all non clustered indexes
  • Shrink the database - no truncate
  • Rebuild all clustered indexes
  • Rebuild all non clustered indexes (re-enabling them)
  • Shrink the database - truncate only
Performance improves significantly, but fragmentation levels are around the same.

I'm at a loss to explain what is going on here, can anyone help me understand why this is occurring?

pcd
PS Please don't go on about not shrinking the database - I'm fully aware of that etc. The suggestion came from someone on the team.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)

Group: General Forum Members
Points: 936351 Visits: 49080
pcd_au - Sunday, February 11, 2018 8:40 PM
I'm stumped and need the assistance of the wider community to understand what is going on.
  • Disable all non clustered indexes
  • Shrink the database - no truncate
  • Rebuild all clustered indexes
  • Rebuild all non clustered indexes (re-enabling them)
  • Shrink the database - truncate only
Performance improves significantly, but fragmentation levels are around the same.

I'm at a loss to explain what is going on here, can anyone help me understand why this is occurring?

pcd
PS Please don't go on about not shrinking the database - I'm fully aware of that etc. The suggestion came from someone on the team.

Performance of what "improves significantly"?

It could be from things like the fact that disabling an index also disables any FK that may be based on those indexes. Of course, that won't affect SELECTs.... just INSERTs, DELETEs, and some UPDATEs. Any FKs of that nature must be rebuilt using ALTER TABLE CHECK CONSTRAINT to become active again.

The other thing is, how are you doing your normal index maintenance? If it's a mature database and it normally does most REORGANIZE instead of REBUILD, that could be it. I'm working on a project that may prove that but I'm not done yet so I can only say that as a suggestion rather than a demonstrable fact.

And, sorry... I have to say that routine shrinking of files or databases is a total waste of time. Wink Doing it for reasons of recovering space after a "rampant growth accident" might also the wrong thing to do if you don't do it correctly and, it looks like you might be doing it incorrectly. If you pack everything down and then rebuild your indexes, you will have wasted space equal to about 120% of your largest pagecount index and that last shrink with TRUNCATE ONLY won't take it (unwanted free space) out because your index rebuilds build a new copy of the index at the logical end of the file (after a shrink) because there's no room at the beginning of the shrunk file. If the goal really is to recover disk space, then you need to create a new temporary file group, rebuild your largest index(es) to that (moving the index using the DROP EXISTING option), do your first shrink, do all your other rebuilds not once but twice to avoid the end-of-file problem, and then rebuild the largest index back to that original file group and drop the temporary file group. Again, that's just a suggestion at this point... I'm not done experimenting with all this and it could be several months (if at all) before I have a demonstrable example.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pcd_au
pcd_au
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 499
Jeff Moden - Sunday, February 11, 2018 9:25 PM
Performance of what "improves significantly"?

It could be from things like the fact that disabling an index also disables any FK that may be based on those indexes. Of course, that won't affect SELECTs.... just INSERTs, DELETEs, and some UPDATEs. Any FKs of that nature must be rebuilt using ALTER TABLE CHECK CONSTRAINT to become active again.

The other thing is, how are you doing your normal index maintenance? If it's a mature database and it normally does most REORGANIZE instead of REBUILD, that could be it. I'm working on a project that may prove that but I'm not done yet so I can only say that as a suggestion rather than a demonstrable fact.

Jeff
A reasonable ask. It's a Data Warehouse database. We have traced a series of queries (select only) that we execute in sequence. Before we 'compact' routines take 40+minutes, after that take 18 minutes. We flush all the caches etc and perform cold and warm tests. There are no RI constraints (not that there are any inserts or updates).

The reorganize only removes leaf level fragmentation, leaving non leaf level fragmentation, where as the rebuild removes both. That could be the answer. Is there a way of determining fragmentation at either a) non leaf levels or b) a specific level? Time to brush up on sys.dm_db_index_physical_stats by the looks of things.

pcd

pcd_au
pcd_au
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 499
Jeff Moden - Sunday, February 11, 2018 9:25 PM
And, sorry... I have to say that routine shrinking of files or databases is a total waste of time. Wink Doing it for reasons of recovering space after a "rampant growth accident" might also the wrong thing to do if you don't do it correctly and, it looks like you might be doing it incorrectly. If you pack everything down and then rebuild your indexes, you will have wasted space equal to about 120% of your largest pagecount index and that last shrink with TRUNCATE ONLY won't take it (unwanted free space) out because your index rebuilds build a new copy of the index at the logical end of the file (after a shrink) because there's no room at the beginning of the shrunk file. If the goal really is to recover disk space, then you need to create a new temporary file group, rebuild your largest index(es) to that (moving the index using the DROP EXISTING option), do your first shrink, do all your other rebuilds not once but twice to avoid the end-of-file problem, and then rebuild the largest index back to that original file group and drop the temporary file group. Again, that's just a suggestion at this point... I'm not done experimenting with all this and it could be several months (if at all) before I have a demonstrable example.

Jeff
Hopefully your comments may help others, in our case it's not about recovering disk space, it's about removing as much fragmentation as possible. We don't truncate the data file initially as we know we will reuse the space when we rebuild, the second shrink where we just truncate is purely a bonus as the database is only used for reporting. However can you provide more detail on the 'end-of-file problem' you mentioned above. I'm presuming the twice refers to the (shrink and rebuild), and not just the (rebuild)?

pcd

GilaMonster
GilaMonster
SSC Guru
SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)

Group: General Forum Members
Points: 951918 Visits: 48920
pcd_au - Sunday, February 11, 2018 8:40 PM
Performance improves significantly, but fragmentation levels are around the same.

I'm at a loss to explain what is going on here, can anyone help me understand why this is occurring?


Dropping and creating an index results in updated statistics (with FULLSCAN). that's the most common reason why index rebuilds improve performance.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)

Group: General Forum Members
Points: 936351 Visits: 49080
pcd_au - Sunday, February 11, 2018 10:15 PM
Jeff Moden - Sunday, February 11, 2018 9:25 PM
And, sorry... I have to say that routine shrinking of files or databases is a total waste of time. Wink Doing it for reasons of recovering space after a "rampant growth accident" might also the wrong thing to do if you don't do it correctly and, it looks like you might be doing it incorrectly. If you pack everything down and then rebuild your indexes, you will have wasted space equal to about 120% of your largest pagecount index and that last shrink with TRUNCATE ONLY won't take it (unwanted free space) out because your index rebuilds build a new copy of the index at the logical end of the file (after a shrink) because there's no room at the beginning of the shrunk file. If the goal really is to recover disk space, then you need to create a new temporary file group, rebuild your largest index(es) to that (moving the index using the DROP EXISTING option), do your first shrink, do all your other rebuilds not once but twice to avoid the end-of-file problem, and then rebuild the largest index back to that original file group and drop the temporary file group. Again, that's just a suggestion at this point... I'm not done experimenting with all this and it could be several months (if at all) before I have a demonstrable example.

Jeff
Hopefully your comments may help others, in our case it's not about recovering disk space, it's about removing as much fragmentation as possible. We don't truncate the data file initially as we know we will reuse the space when we rebuild, the second shrink where we just truncate is purely a bonus as the database is only used for reporting. However can you provide more detail on the 'end-of-file problem' you mentioned above. I'm presuming the twice refers to the (shrink and rebuild), and not just the (rebuild)?

pcd


Based on what you're saying, shrinking is probably the wrong thing to do for this. You're just trying to defragment indexes, correct? Before I can make additional recommendations or answer the "end-of-file-problem", could you tell me the total size of the database and what the size of the 4 largest tables (including the indexes) are, please?

And Gail is correct. In many cases (especially on mature databases where REORGANIZE is frequently used), people forget to rebuild stats and rebuilding all of the indexes did a lot of that for you, although it doesn't take care of all statistics (particularly, column stats).

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pcd_au
pcd_au
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 499
GilaMonster - Monday, February 12, 2018 2:13 AM

Dropping and creating an index results in updated statistics (with FULLSCAN). that's the most common reason why index rebuilds improve performance.

We tests Ola Hallengren's code against the database - nothing was found, everything check out OK.
I'll give the update statistics with full scan a go.

pcd

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)

Group: General Forum Members
Points: 936351 Visits: 49080
pcd_au - Monday, February 12, 2018 3:02 PM
GilaMonster - Monday, February 12, 2018 2:13 AM

Dropping and creating an index results in updated statistics (with FULLSCAN). that's the most common reason why index rebuilds improve performance.

We tests Ola Hallengren's code against the database - nothing was found, everything check out OK.
I'll give the update statistics with full scan a go.

pcd


That's not quite what she's saying. She's was explaining that you disabling the indexes and then re-enabling them caused an index rebuild, which also causes a stats update with a full scan as a result. It IS a good thing to use OLA's stuff to do your STATS updates on a regular basis whether or not an index needs defragmenting or not.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pcd_au
pcd_au
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 499
Jeff Moden - Monday, February 12, 2018 5:54 PM
That's not quite what she's saying. She's was explaining that you disabling the indexes and then re-enabling them caused an index rebuild, which also causes a stats update with a full scan as a result. It IS a good thing to use OLA's stuff to do your STATS updates on a regular basis whether or not an index needs defragmenting or not.

Jeff, my bad explanation.
Prior to starting down this path we used Ola's scripts, but they indicated that there was nothing for us to do.
I'm aware that rebuilding the indexes will update the stats, but missed the fact that I should have have tried updating the stats as a first option. Sometimes you lose sight of the obvious.
I'm hoping that I can encourage the client to implement the index optimise code as part of their loading process.
pcd
PS Waiting to get you the info on the tables etc requested in a prior post.

mig28mx
mig28mx
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 319
Hi,
The most common factor for slow querys are:
1.- Low space on tmbdb. This is solved by increasing the space. Or allocating them in a high speed disk drives like solid state drives.
2.- Wrong cardinallity on execution plans. This is solved by updating / creatining statistics. As Gail have mention before.

I also will take care about creating index columns in order to improve performance in some querys.

Hope this helps.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search