Index fragmentation status, mgm studio

Except writing new code the programmers at Microsoft has a huge backlog of old stuff to retest when a new version is shipped. So that old bugs don’t get reintroduced. Finally I had time to go back to check for myself if the SQL Server 2008 R2 RTM version is fixed, concerning the performance bug you got earlier, when you clicked on “fragmentation” on an index properties page.This is the gui I’m writing about: 

Before the patched versions, management studio got the “timeglass freeze” and hanged for hours if you let it, but now it took less than 30 seconds to get the fragmentation information back for an arbitrary index. So now it is ok to check that in Managment Studio again.

It was first released for CU 6 for SQL Server 2005 Service Pack 3, CU 8 for SQL Server 2008 RTM and CU 5 for SQL Server 2008 SP1 as described in this KB.It could be checked the T-SQL way of course. Here is an example of checking indexes of the AdventureWorks2008R2 database, table Address in Person schema:

Use AdventureWorks2008R2 Go DECLARE @db_id SMALLINT; DECLARE @object_id INT; SET @db_id = DB_ID(N’AdventureWorks2008R2′); SET @object_id = OBJECT_ID(N’AdventureWorks2008R2.Person.Address’); IF @db_id IS NULL BEGIN; PRINT N’Invalid database’; END; ELSE IF @object_id IS NULL BEGIN; PRINT N’Invalid object’; END; ELSE BEGIN; SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , ‘LIMITED’); END; GO