June 17, 2014 at 3:59 pm
I Rebuilt Indexes and it caused excessive Growth.
The Database went from 389 GB to 690 GB.
So I rebuilt the Indexes with a Fill Factor of 100%.
I do not understand why I can't specify 0%.
All of the tables are at 0% or 1005. Not good.
But after the rebuild it only made 7% of free space.
Restores went from just 0ver 30 minutes to over 2 hours.
Any input would be greatly appreciated. I will never do what I did again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 17, 2014 at 4:29 pm
Hi Although I might not help with answers, I do have more questions 🙂
Rebuilt Indexes and it caused excessive Growth.
The Database went from 389 GB to 690 GB.
So for the first attept rebuilding indexes it grew to 690GB?
So I rebuilt the Indexes with a Fill Factor of 100%.
I do not understand why I can't specify 0%.
All of the tables are at 0% or 1005. Not good.
I have never tried 0% fill factor, I wouldn't think that's possible?
I should try that out myself.
Are you familiar with the fill factor option? The fill factor dictates have full you want each page to be.
So if your fill factor is closer to 100% then there would be less pages (8kb per page) equaling potentially a smaller database size.
But after the rebuild it only made 7% of free space.
If Fill factor was set at close to 0% then there's a lot of empty pages "logically" but physical file size will be large because that free space was expanded based on the option choosed w/Fill factor.
If you choose a fill factor closer to 100% let's say 80%-90% // the rule of thumb varies - (all those buzz words people say... depending on access methods, the type of system, activity, etc 🙂 )
Restores went from just 0ver 30 minutes to over 2 hours.
Any input would be greatly appreciated. I will never do what I did again.
Have you looked at your log growth after index rebuilds? You may need to backup the log and clear it, maybe shrink if that's an option for you.
A restore will take much longer with a big log as it has to expand out the log file.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
June 17, 2014 at 6:57 pm
"The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.
Fill-factor values 0 and 100 are the same in all respects."
Source:
June 17, 2014 at 10:12 pm
It is normal during the reindex your log grow hence your database size grow and depending on the database design and activities on the database.
If you do not want to grow your database during reindexing you can use rebuild option short in tempdb and instead of the database it will use tempdb for this operation.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 18, 2014 at 1:25 am
free_mascot (6/17/2014)
If you do not want to grow your database during reindexing you can use rebuild option short in tempdb and instead of the database it will use tempdb for this operation.
That won't stop the database from growing.
Database growth is normal during index rebuild if you don't have sufficient free space. When an index is rebuild, SQL creates a copy of it (and that is in the user DB), the entire index rebuild must be logged and it's a size-of-data operation. Hence rebuilding a 500GB index will require 500GB of free space in the data file, 500 GB free space in the log file and enough place to do the sort if sort_in_tempdb is not specified
If you don't have that space, the DB will grow.
This is why it's advisable to always have enough free space in the DB.
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 18, 2014 at 3:07 am
I made a Decision to rebuild indexes to reduce fragmentation.
I caused the Database to grow big time.
So I set all of the indexes gill factor to 100 percent.
The Database is still 20 GB larger prior to the rebuild.
It used to take 30 + minutes to restore Development from prod but now it takes 3.5 hours.
All of the indexes are set to 0 or 100.
What can I do to get out of this mess?
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 18, 2014 at 3:13 am
Rebuilding indexes will cause the DB to grow if there's not enough free space. This isn't an issue, this is normal behaviour. Fill factor should be based on usage patterns, not blanket set to 100%
The DB being larger is expected and not a problem.
Restore speed is a factor of data size (not database file size) and IO throughput. Check the IO throughput and latency, make sure nothing has changed there. For the restore time to have gone up 7x, it suggests that there's some IO bottleneck.
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 18, 2014 at 3:53 am
In addition to what Gail said, you might want to consider reorganizing indexes instead of rebuilding them, depending on the level of fragmentation. As a rule of thumb, use REORG for fragmentation between 10% and 30% and REBUILD for fragmentation > 30%, but (disclaimer) you need to test for your own situation.
With regard to fillfactor, setting it to 100% is likely to lead to increased fragmentation. Have a read of this[/url] blog for more info.
Regards
Lempster
June 18, 2014 at 1:39 pm
homebrew01 (6/17/2014)
"The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.Fill-factor values 0 and 100 are the same in all respects."
Source:
Thanks for info Homenbrew01 🙂
In addition to what Gail says about the restore comments.
I was wondering do you have "instant file intialization" setup for the SQL Server instance? Just thinking if the data files grew it may take awhile to zero out the data files if instant file initizlization feature isn't enabled. (doesn't work on data files - which is why I wondered if the log file was a lot larger than originally)
Ran into a similar situation but it was backups took longer after a large purge of data (reduced size of db to 60% of original size), nothing could fix it - rebuilding indexes, etc. We didn't have the expertise across the infrastructure to validate if it was a Host layer, storage layer, Fabric layer issue... we ended up creating new mount points and restoring the DB to them which resolved it.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
June 19, 2014 at 7:26 am
I got myself in a bind.
I performed a rebuild of Indexes on all tables in a Database.
The Database grew in size from 406 GB to .9 TB.
I shank it as much as I could knowing that was a good thing to do but I wanted to see if I could get it to it's original size. That did not work.
Now I'm having performance issues with my Data loads.
I'm in the process of refreshing Dev from Prod to verify that the problems I'm experiencing are a result of the reindexing.
Any ideas as to what course of action that I can take would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 23, 2014 at 6:14 pm
Welsh Corgi (6/19/2014)
I got myself in a bind.I performed a rebuild of Indexes on all tables in a Database.
The Database grew in size from 406 GB to .9 TB.
I shank it as much as I could knowing that was a good thing to do but I wanted to see if I could get it to it's original size. That did not work.
Now I'm having performance issues with my Data loads.
I'm in the process of refreshing Dev from Prod to verify that the problems I'm experiencing are a result of the reindexing.
Any ideas as to what course of action that I can take would be greatly appreciated.
What method is being used to rebuild the indexes?
Is it a script?
Mainteance Plan?
Also what version of SQL Server is being used?
Is the server on the latest CU or Service pack?
Maybe there is a bug that you've stumbled across?
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply