July 7, 2009 at 5:34 pm
Hi..I am trying to Rebuild the indexes on a table by using the following command...
ALTER INDEX all ON TableName
REBUILD
after running this command, I still find the fragmentation on the indexes on this table. it used to be 90% fragmentation and it reduced to 70%. do we have to run this command multiple times?? or am I missing something here ? Please advise me...
Thanks a lot..
July 7, 2009 at 5:59 pm
If your table is small - it will never be defragmented fully. How large is this table in pages?
The general rule is that you won't see any benefits to rebuilding indexes until the table has more than 1000 pages.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 7, 2009 at 6:58 pm
The table has 5 million records....But in dm_db_index_physical_stats, it shows page_count = 6 and record_count = 1503!! Is that sample data or am I missing something? I m using following query..
SELECT OBJECT_NAME(i.object_id) AS [Table Name],
i.name AS [Index Name],
dm.avg_fragmentation_in_percent, *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') dm
INNER JOIN sys.indexes i ON i.object_id = dm.object_id
AND i.index_id = dm.index_id
where avg_fragmentation_in_percent>20
July 7, 2009 at 8:15 pm
Was this database upgraded from SQL Server 2000? If so, have you run DBCC UPDATEUSAGE and DBCC CHECKDB ... WITH DATA_PURITY?
Since you are using the DETAILED flag, are you sure you are looking at the right object and index?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 7, 2009 at 8:25 pm
No it is not upgraded....it is SQL 2008 ....there are 8 indexes on that table. out of 8, I m getting 3 indexes showing around 50% fragmentation, 1 clustered and 2 non clustered. Detailed in the sense, Do we get any other indexes which are not critical??
Thanks a lot..
July 7, 2009 at 8:26 pm
If I replace Detailed with LIMITED, I don't get any fragmented indexes....What does it mean?? we don't need to worry about this or what??
Thank you
July 7, 2009 at 8:33 pm
Can you provide a sample of what you are seeing when you use DETAILED? I am going to guess that you are seeing fragmentation at one of the leaf levels of the index and not at the data level. When you use limited - you would only be looking at the data level (I believe).
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 7, 2009 at 8:47 pm
This is the result I m getting:
avg_frag db_id
TableName cndx_PrimaryKey_Contact 50 11
object_id index_id Partition
69357750911CLUSTERED INDEXIN_ROW_DATA
index_depth
4
Index_level frag_count avg_frag_size pg_count
2 6 1 6
avg_page_space record_count
77.3474178403756 15030 CLUSTERED
I tried to print the column names, But I couldn't...Thanks for your patience
July 7, 2009 at 9:03 pm
So, that is showing us index level 4 - what are the values for the other index levels? And, where are you seeing that this table has millions of rows?
One of the things you can do is just run the DMV for that specific object using limited, sampled and detailed to see the differences. You can get the object id from sys.objects or sys.tables with a query like:
SELECT * FROM sys.tables WHERE name = 'your table name here';
Then, run the following:
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), {object id here}, NULL, NULL, 'LIMITED');
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), {object id here}, NULL, NULL, 'SAMPLED');
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), {object id here}, NULL, NULL, 'DETAILED');
For limited and sampled, we should only see a single row for each index. For detailed, we should see multiple rows for each index. Each row shows the value for each leaf level of the index.
You can take a screen shot and post it as an attachment here.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 8, 2009 at 4:04 am
yeah...exactly I got the same results as you told...in LIMITED, I returned only 1 row with page count 500000, index level = 0 and fragmentatoin is just .2% which is not bad....
but in Detailed, for the index_level = 2, page_count = 6 the fragmentation is 50%.
Do I have to worry about this??
July 8, 2009 at 9:30 am
When using detailed, you should only be worried about the bottom (data) level. I think you are fine for now.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 8, 2009 at 9:37 am
Awesome...Thank you very much for your help and patience...
July 8, 2009 at 10:14 am
Glad I could help
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply