When I was young we used to go to see our local football, sorry, soccer team and cheer them on rain or shine on a Saturday afternoon in England. In those days the cheapest way to watch the game was “standing on the terraces” – no seats for the weary, but we would always stand in the same place so friends knew where we would be in the crowd. Yes, this was long before the cell phone revolution. I remember there was always an old man there that would criticize the team week in week out. No cheers from him when they scored a goal, but plenty of abuse when they didn’t. Even when things were going well, he would tell the players to get their haircut if he ran out of bad things to say. We used to call him the “Old Moaner” and would listen and laugh at his comments. Well, now I am getting on in years and I am writing this blog, I am beginning to hear the “Old Moaner” in me! Heaven forbid. Let’s talk about Index Fragmentation in SQL Server.
Fragmentation is a natural effect of rows being inserted, updated and deleted especially with variable length columns which may not allow space on a data page to be easily reused. Also, data may not be as contiguous as we would like, with pointers being used excessively by the system to access data in the correct order. This fragmentation impacts the performance advantages of indexes with more data reads needed so it is always best practice in SQL Server to rebuild indexes on a regular basis, for instance weekly as part of a maintenance plan.
Before SQL Server 2005, we would always use DBCC SHOWCONTIG to identify fragmentation in indexes. We would use DBCC INDEXDEFRAG to perform an online defrag and DBCC DBREINDEX to rebuild indexes offline. Simple. Well, now these DBCC commands are deprecated and although we can still use them, they will go away in a future release. So Microsoft want us now to avoid the DBCC commands and use the new system catalog views, some specifically called dynamic management views. The one that should be used instead of DBCC SHOWCONTIG is called sys.dm_db_index_physical_stats. I am sorry, but straight away I have a problem with remembering the name of this DMV. I can remember DBCC SHOWCONTIG easily. Also the DMV requires a database id and an object id so we need to use the DB_ID function and the OBJECT_ID function before accessing it. To get the same information as DBCC SHOWCONTIG, we need to join with several other DMVs, adding to the complexity. Isn’t the point of views to simplify access?
Instead of DBCC INDEXDEFRAG we now can use the ALTER INDEX REORGANIZE clause. That’s OK. And instead of DBCC DBREINDEX you can use ALTER INDEX REBUILD. Fine. And we can now rebuild indexes “online” without locking out the users with a table lock as long as we are using the Enterprise Edition. It uses snapshot technology to allow the users to continue while the index is being rebuilt. That’s the idea, make administration more consistent and throw in some new functionality that’s useful. Very good.
OK, we now have the new Fragmentation page in SSMS under the Index Properties. We also had a nice Disk Usage report in SSMS 2005 with 3D pie charts and table and index space and fragmentation information on a single report. For some inexplicable reason, this report has since been divided into several smaller reports as we see in SSMS 2008 which forces us to run multiple reports to get the same information. And the 3D pie charts are now 2D. Shame!
Well, now you can see that I am an “Old Moaner” about SQL Server. But really, I just want to see a great product that I know and love, get better and better.
Cheers
Brian
Recent posts
More on the SSWUG.org Virtual Conference
SSWUG.org Virtual Conference first impressions
You have been notified – SQL Server 2005 SP3 in Beta
