June 3, 2008 at 10:14 pm
hi
I found index defragmentation as 66.66666667 % by executing
SELECT database_id,OBJECT_ID,index_id,index_type_desc,avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID('adventureworks'),30623152,2,1,'detailed')
and the results are
db_idobject_id index_idindex_type_desc avg_fragmentation_in_percent
8306231522NONCLUSTERED INDEX66.66666667
8306231522NONCLUSTERED INDEX0
I foundout the table name as 'StoreContact' by executing SELECT OBJECT_NAME(30623152).
n I rebuild the index using ALTER INDEX ALL ON Sales.StoreContact rebuild
n I updated the statistics using UPDATE STATISTICS Sales.StoreContact
But the fragmentation is still 66.66666667 % ?
Why the fragmentation is not reduced?
is this correct approach to reduce fragmentation?
any help plz...............
June 3, 2008 at 11:47 pm
BOL says:
If ALL is specified and the underlying table is a heap, the rebuild operation has no affect on the table.
Your table is a heap (= table without clustered index).
Suresh
June 4, 2008 at 6:25 am
my table has 3 non-clustered indexes n 1 clustered index
June 4, 2008 at 6:48 am
How many rows are in the table? I ran into a similar issue and posted it on Microsoft Connect because I thought I had a bug. It has to do with how the first 8 pages are stored. Read it for yourself.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2008 at 11:59 am
Microsoft has as whitepaper on this.
"Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Kalen Delaney has also written a blog post about this.
http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/28/fragmentation-revisited.aspx
I have a stored procedure that could help you with this.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Ola Hallengren
June 4, 2008 at 12:01 pm
thank you...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply