July 10, 2008 at 11:42 am
As your indexes are being rebuilt, it may appear that your database is 'shrinking' in logical size but what is really happening behind the scenes is that SQL Server is packing the data pages up to the fill factor. So you could have had a bunch of internal fragmentation where your data/index pages have a bunch of free space in them. This means that there are fewer rows per page and that more pages and extents are required to store those rows. When your indexes are rebuilt, the pages are packed up to the fill factor and it then requires less pages and extents to store the same amount of row data thereby making it appear that the logical size is less.
Rebuilding indexes will not reclaim the physical space of the data file. I would not worry about reclaiming it unless you don't plan on your database ever growing again or you have another really good reason to. If you shrink your data file, SQL Server will just have to grow it again as you add data. As it grabs chunks of disk space to grow the file, your data file is then fragmented accross the file system. Make sense?
July 10, 2008 at 11:44 am
Another comment here. If you are seeing the physical size of your data file decrease without running a shrink file, check your DB settings and make sure auto shrink is not on. You shouldn't, in my opinion, be running a production DB with auto shrink ON.
July 10, 2008 at 12:04 pm
All makes sense John. Thanks
Just some more background. I am a one man test in our company. I am a business user who has B- technical skills through some training and "figuring stuff out". The real DBA's don't touch anything of mine and probably get a good chuckle when they do. So i have a lot more to learn. My expertise is in data mining not administering.
The database grows every week so it sounds like i don't have to worry about shrinking. Auto shrink is on. Turn it off?? I append on average 15 million rows a week to my main table and then build 2 aggregate tables from it. So there is alot of inserting going on. There is also alot of selects going on those tables. I know it is art and science but, do you have a suggested fill factor to use??? The inserts happen on Monday mornings only. Would it be insane to change the fill factor before and after the inserts?? Based on what i told you should i schedule a defrag or just wait for lets say >10% fragmentation and run it then?
July 10, 2008 at 2:03 pm
Well, your right, it is an art and a science. First off, yes, disable the auto shrink. Secondly, as far as the fill factor goes, it all depends.
Here's what we discussed so far:
1. Fill factor set too low results in less rows per page results in slower performance for selecting data as more data pages must be read into memory to satisfy the query.
2. Fill factor set to high results in page splits do to data inserts or modifications.
The answer is (your not going to like it) that it depends. Some of this is going to depend on how you've indexed your tables as well. For example, if your clustered index is on an identity column or on a datetime column representing the create date of the row, all of your inserts will be happening at the end of the table which means that new data pages will be created to store the data. If your clustered index is on, say, a status type column or any other non-sequential column, the INSERTS will have to find the correct data page to put the data, possibly split the page, then insert the data.
I would recommend rebuilding your indexes over the weekend, loading your rows on Monday as you've stated, then checking your fragmentation levels to see the impact that your data load had on your fragmentation. Check the fragmentation view throughout the week on a regular basis so that you can create a maintenance strategy that works for you.
Another thing you may consider is using SQL Server's table partitioning on your main table to switch these 15 million row loads into the table without having to run the INSERT directly on your table as this will undoubtedly lock portions of the table/index for a bit of time.
July 10, 2008 at 11:41 pm
PeterG (7/10/2008)
The database grows every week so it sounds like i don't have to worry about shrinking. Auto shrink is on. Turn it off??
Yes. That's the reason your indexes are so fragmented. Read the article I linked above, andread the two articles linked at the end of it.
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
July 11, 2008 at 5:59 am
thanks Gail, i had a look at that. Re run the script but this time play around with the fill factor for the index rebuild, the results are interesting. With a fill factor of 30 my log scan frag was way under 30%, in fact table1 was 0%. My suspicions seem correct that fill factor will effect the shrinking after indexes have been rebuilt, maybe you could test and confirm
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2008 at 7:51 am
Bear in mind that was a very simple 2 table database that I played with there. Don't assume that the 0% will apply to more complex DBs with more tables
In one of the links on that page, Paul Randal explains why shrink so badly fragments indexes. In essence, a shrink operation takes pages at the end of the file and moves them as far forward in the file as possible. Hence it can completely reverse the order of pages in an index.
Since fragmentation is calculated as the % of pages that are out of order (a page further along the index been earlier in the file), the partial or complete reversal of an index results in very high fragmentation
The related effect of the fill factor is to increase the number of pages in the index.
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
July 11, 2008 at 8:35 am
GilaMonster (7/11/2008)
The related effect of the fill factor is to increase the number of pages in the index.
and consequently the overall file size.
it would be interesting to see what happens in a larger more complex database.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2008 at 2:15 pm
Perry Whittle (7/11/2008)
GilaMonster (7/11/2008)
The related effect of the fill factor is to increase the number of pages in the index.and consequently the overall file size.
As well as the depth of the index tree and the cost to use the index.
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
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply