November 9, 2009 at 9:14 am
Thought I would share this one. I'm sure some of you already know this but I'll bet there is a few of you out there that still don't. I am going to use the AdventureWorks2008 database as an example. Ok, first lets rebuild some clustered idexes in this sample database. This is not all of them. but enough to demonstrate my example:
USE [AdventureWorks2008]
GO
ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] ON [Sales].[SalesOrderDetail] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
ALTER INDEX [PK_Person_BusinessEntityID] ON [Person].[Person] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
ALTER INDEX [PK_BusinessEntity_BusinessEntityID] ON [Person].[BusinessEntity] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
ALTER INDEX [PK_Store_BusinessEntityID] ON [Sales].[Store] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
ALTER INDEX [PK_EmailAddress_BusinessEntityID_EmailAddressID] ON [Person].[EmailAddress] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
ALTER INDEX [PK_PersonPhone_BusinessEntityID_PhoneNumber_PhoneNumberTypeID] ON [Person].[PersonPhone] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
ALTER INDEX [PK_Password_BusinessEntityID] ON [Person].[Password] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
Ok, easy enough, now lets run this query and look at the first column in the results for these clustered indexes. Percentage for the indexes we rebuilt above should all be at or just fractionally above 0 percent, which is wonderful:
select ips.avg_fragmentation_in_percent,
ips.page_count,
object_name(ips.object_id),
si.name,
ips.index_type_desc,
ips.fragment_count,
ips.page_count,
ips.record_count
from sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
join sys.indexes si on si.object_id = ips.object_id and si.index_id = ips.index_id
where page_count>=100
and index_level = 0
and avg_fragmentation_in_percent >= 0
and si.name like 'PK%'
order by object_name(ips.object_id)
GO
Ok, now let's shrink the datafile, or database whatever you prefer with this statement:
USE [AdventureWorks2008]; DBCC SHRINKFILE(AdventureWorks2008_Data, 189)
GO
Ok, piece of cake, now let's run the above query again after the shrink. Notice the first percentage column now on the clustered indexes we had rebuilt before we shrunk the database. They are badly fragged now. Hmmmm.., why is this happening? Well, according to SQL expert Paul S. Randall the shrink is reversing the page order of the clustered index. I am still not exactly sure why the shrink does this, but what is important is to realize here is that it DOES, and to be mindful of it whenever you do a db shrink. Now, if you run the index rebuild once again you will then see after running the query again that fragmentaion has been taken care of on those rebuilt indexes. All is well once again. This is yet another reason you should be very careful with shrinking your database and you should never have them set to AUTO_SHRINK. HTH. Travis.
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
November 9, 2009 at 9:19 am
That is very weird behaviour....
Mind you I never understand why there is a requirement to shrink databases anyway. To my mind you should size the database correctly in the first place, otherwise letting the DB shrink then resize repeatadily may cause disk fragmentation.
Peter Gadsby
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 9, 2009 at 10:04 am
Peter Gadsby (11/9/2009)
That is very weird behaviour....Mind you I never understand why there is a requirement to shrink databases anyway. To my mind you should size the database correctly in the first place, otherwise letting the DB shrink then resize repeatadily may cause disk fragmentation.
Peter Gadsby
Agreed, but there are scenarios where you might want to consider manually doing a db shrink off-hours, as Jason Wong states in his article/script at http://www.sqlservercentral.com/scripts/Shrink+Db/68336/ Just be very mindful of the pitfalls as I stated above... 🙂
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
November 9, 2009 at 12:54 pm
i do db shrinks on archive databases once in a while to clear up space or to delete unused data files. otherwise i always create data files to be twice as large as I think they need to be
November 9, 2009 at 1:10 pm
SQL Noob (11/9/2009)
i do db shrinks on archive databases once in a while to clear up space or to delete unused data files. otherwise i always create data files to be twice as large as I think they need to be
Agreed, but this really isn't about the setting of original sizes of your databases because that doesn't really have anything to do with fragging out all your indexes in your database by doing a db shrink, and sooner or later regerdless of what original size size you set on your dbs you will probably need or want to do a shrink and then what I said will happen, will happen..
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
November 9, 2009 at 1:19 pm
another bad thing about shrinks is it will cause your db files to become physically fragmented on the disk
November 10, 2009 at 7:42 am
talltop-969015 (11/9/2009)
I am still not exactly sure why the shrink does this, but what is important is to realize here is that it DOES, and to be mindful of it whenever you do a db shrink.
Because shrink operates one page at a time, takes the page at the end of the file, moves it as far to the beginning as possible, repeat until no more pages can be moved.
http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
p.s. it's Paul Randal (only 1 l)
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
November 10, 2009 at 7:55 am
p.s. it's Paul Randal (only 1 l)
Yes, thanks for the url though. I know, it's called a type-o. 😉 You would think that reversing the page order of the index would be worth a mention in BOL since it does affect index fragmentation like that so drastically(0-90% in some cases) not just "to a degree", but I couldn't find it anywhere. But then again, you can fill volumes what is not in BOL.:-P
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply