March 15, 2012 at 3:27 pm
When running a the included query I would expect to see a single result for an index. but instead I get and index_id of 1, 2, 3, 4, 5 for an index and each has different avg_fragmentation_in_percent. Can someone please help me make sense of what is going on? And if I need to rewrite this query I am more than happy to.
SELECT dbIndx.database_id, dbIndx.object_id, obj.name AS
, dbIndx.index_id, idx.name [index name], dbIndx.avg_fragmentation_in_percent,
dbIndx.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS dbIndx
INNER JOIN sys.indexes AS idx ON (dbIndx.object_id = idx.object_id)
INNER JOIN sys.all_objects AS obj ON (dbIndx.object_id = obj.object_id)
WHERE idx.name IS NOT NULL
ORDER BY avg_fragmentation_in_percent DESC;
GO
produces
database_idobject_idtable nameindex_idindex nameavg_fragmentation_in_percentpage_count
52005582183audit_trail_history4AUD_HIST_MRN99.9943473211441194598
52005582183audit_trail_history3AUD_HIST_MRN99.989287654532392071
52005582183audit_trail_history1AUD_HIST_MRN6.087450015981841379691
52005582183audit_trail_history2AUD_HIST_MRN0.01390561
52005582183audit_trail_history5AUD_HIST_MRN0173512
March 15, 2012 at 3:32 pm
Your join to sys.indexes is wrong. It should look like this:
SELECT
dbIndx.database_id,
dbIndx.object_id,
obj.name AS
,
dbIndx.index_id,
idx.name [index name],
dbIndx.avg_fragmentation_in_percent,
dbIndx.page_count
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS dbIndx
INNER JOIN sys.indexes AS idx ON (dbIndx.object_id = idx.object_id and dbIndx.index_id = idx.index_id)
INNER JOIN sys.all_objects AS obj ON (dbIndx.object_id = obj.object_id)
WHERE
idx.name IS NOT NULL
ORDER BY
avg_fragmentation_in_percent DESC;
March 15, 2012 at 3:46 pm
That worked great. Thanks. So what would have caused the duplication of index record names and different fragmentation information?
EDIT
Ok I think I understand now what this happened. Since sys.dm_db_index_physical_stats has an index_id of 1 -5 and each index under sys.indexes is tied to the object id it looped through that object_id and assigned multiple index_id's to the object name from sys.indexes.
March 15, 2012 at 3:52 pm
bsdtux (3/15/2012)
That worked great. Thanks. So what would have caused the duplication of index record names and different fragmentation information?
Really? Look at each of the tables where I modified the join. Each table has one row for each index in the table. If each table has 5 rows for 1 table (5 indexes) and you only join on the table (object_id) you get 25 joins each record from the first table joins to 5 records in the second table.
March 15, 2012 at 4:01 pm
Thanks. Sorry for asking probably what seems like a basic question. Again thanks for the correction to my query.
March 15, 2012 at 4:07 pm
bsdtux (3/15/2012)
Thanks. Sorry for asking probably what seems like a basic question. Again thanks for the correction to my query.
You'll have to pardon me, fighting with a couple of queries here and then SSMS decided to act up forcing a not too pretty shutdown of my laptop.
I do recommend taking a closer look at the table structures in Books Online. It will help you understand the relationships between the tables.
March 15, 2012 at 4:10 pm
Great I will do that. Probably will help also that I just bought a few books from red gate that can also help with understanding these DMV's alot better.
March 15, 2012 at 4:14 pm
The more you work with them the better. When it comes to indexes, just remember that the object_id is the table on which the index is created. To identify a specific index you need both the object_id and index_id. One other thing to remember about indexes, index_id 0 and 1 are mutually exclusive as index_id represents a heap and index_id 1 is a clustered index.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply