October 11, 2014 at 6:56 pm
I got SQL Standard edition of version 2008R2 with a large database table. Initally, when I used to run below index fragmentation script from Microsoft [http://msdn.microsoft.com/en-us/library/ms188917%28v=sql.105%29.aspx], I see after everytime after maintenance, the database size increases approximately 4 GB.
The line which executes from script:
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
So, I did refered some articles from google, and I see everyone mentions to run index rebuild with 'SORT_IN_TEMPDB = ON' command. So, I changed below query as shown:
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD' + N' WITH( SORT_IN_TEMPDB = ON) ';
But, still I don't see any difference. I still continue to see the database size increases after maintenance.
This is a weekly maintenance job and if everyweek if it increases 5 GB, how should I request my storage team.
Is this general behaviour of SQLServer. And does 'SORT_IN_TEMPDB = ON' option applicable to SQL Standard edition? Any better ways to manage the growth.
Please, let us know with your experience. Referred many articles but not very clear from everyone. Thank you.
October 12, 2014 at 9:58 am
DBA_Learner (10/11/2014)
I got SQL Standard edition of version 2008R2 with a large database table. Initally, when I used to run below index fragmentation script from Microsoft [http://msdn.microsoft.com/en-us/library/ms188917%28v=sql.105%29.aspx], I see after everytime after maintenance, the database size increases approximately 4 GB.The line which executes from script:
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
So, I did refered some articles from google, and I see everyone mentions to run index rebuild with 'SORT_IN_TEMPDB = ON' command. So, I changed below query as shown:
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD' + N' WITH( SORT_IN_TEMPDB = ON) ';
But, still I don't see any difference. I still continue to see the database size increases after maintenance.
This is a weekly maintenance job and if everyweek if it increases 5 GB, how should I request my storage team.
Is this general behaviour of SQLServer. And does 'SORT_IN_TEMPDB = ON' option applicable to SQL Standard edition? Any better ways to manage the growth.
Please, let us know with your experience. Referred many articles but not very clear from everyone. Thank you.
SORT_IN_TEMPDB doesn't actually help the database being defragged very much insofar as preventing growth. Any index larger than 128 extents (8MB) will be built as a new index in the database (and NOT in TempDB) and the old index will not be dropped until the rebuild completes.
"How should I request my storage team"? You first need to analyze what the actual growth will be each week/month and use that to extrapolate the future disk needs for at least a year (I usually work it out for 2 years and include estimated customer growth which also causes disk growth) and then add 10% contingency so that it's not a constant Nickel&Dime request process with the storage team. If you don't have a historical chart (and the online data to back it up) to show such growth, then you need to start one now.
If they don't embrace such time-saving forward thought and allocation of space, it may be time to either get a new storage team or change to a company that does. 😉 Everyone in the company needs to realize that data happens, it's going to grow, and that's it's much less expensive to plan for and implement for growth than to have problems because of disk space not being available. Disk space is cheap compared to an outage.
You also need to do some additional planning. If the indexes are on a mostly-static table like an audit table, you should study-up on partitioning with Read-Only partitions thrown in so that you don't run into the explosive growth that rebuilding indexes on such large tables causes. You also need to check and see what the average % of pages have been used for the indexes (especially the clustered indexes). Re-building indexes can save a whole lot of space by removing extent and page splits. By the same token there, if the clustered indexes regularly suffer from splits and have a low average % of pages used, you should analyze the situation and probably rework the clustered index(es) to be more narrow, unique, and ever increasing to prevent such splits in the future.
Whatever you do, never fight with people on the storage team about any of this. You all work for the same company. Both of you have work to do to keep the company data in good order and safe. Work with each other towards the same goal. It's what everyone has been hired for.
Sorry for that bit of rant but I find that DBAs are frequently at odds with the other teams (dev, ops, security, hr, etc) and that just shouldn't be. Everyone in the company is on the same team.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2014 at 4:56 am
Jeff's reply + 1
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 13, 2014 at 5:58 am
Jeff Moden (10/12/2014)
If you don't have a historical chart (and the online data to back it up) to show such growth, then you need to start one now.
A huge +1 to this point.
Jeff Moden (10/12/2014)
Whatever you do, never fight with people on the storage team about any of this. You all work for the same company. Both of you have work to do to keep the company data in good order and safe. Work with each other towards the same goal. It's what everyone has been hired for.
Fighting with the SAN guys leads to mistrust and a combative relationship, which serves neither of you well. If you set up a way of measuring database growth over time, it will help you in with working with the SAN guys. This, in turn, builds trust that you aren't just grubbing for space constantly and they're more likely to listen to you when you say you need space.
October 13, 2014 at 6:10 am
Thank Jeff.
But, why won't the space reverts back after index rebuild.
October 13, 2014 at 6:21 am
Once the database grows, it doesn't automatically shrink and yield back the space to the operating system. There is an option for this, but I recommend that you don't use it. Disk operations are expensive.
Another key point is that when you shrink your database, your fragmentation skyrockets, which is the exact opposite of what you wanted to accomplish with rebuilding your indexes.
October 13, 2014 at 6:24 am
You do not get the space back because you are not blowing up a balloon.
A data file is a rigid container, not a flexible one. If you increase the size of a rigid container, it stays at its new size.
What you do get is empty space inside the container that can be reused. The next time you do an index rebuild it will use the space vacated by the first index rebuild. Therefore (unless you have increased the size of your table), you should see a pattern where the first index rebuild increases the data file size, but subsequent index rebuilds do not affect data file size.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 13, 2014 at 7:13 am
That's great. Thank you for information.
Also, since my server is of Standard edition and not having any RCSI enabled or having Index rebuild ONLINE option, installing below hot fix should not fetch anything better right?
October 13, 2014 at 7:37 am
DBA_Learner (10/13/2014)
That's great. Thank you for information.Also, since my server is of Standard edition and not having any RCSI enabled or having Index rebuild ONLINE option, installing below hot fix should not fetch anything better right?
It also says in that hotfix that it's included in SP3 of 2008. If your not at sp3, consider it. Of course, do a parallel install so that you can test to make sure nothing breaks, first.
For those using 2005, this problem also affects 2005 but no hotfix for that.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply