June 17, 2021 at 12:10 pm
I found the following script to list indexes and their usage. However, some indexes (with the same Id) are listed twice, but with different values. How come?
SELECT OBJECT_NAME(i.object_id) AS TableName,
i.index_id,
i.name,
i.is_unique,
ISNULL(user_seeks, 0) AS UserSeeks,
ISNULL(user_scans, 0) AS UserScans,
ISNULL(user_lookups, 0) AS UserLookups,
ISNULL(user_updates, 0) AS UserUpdates
FROM sys.indexes I
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius
ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsMSShipped') = 0
ORDER BY tablename,name
June 17, 2021 at 1:55 pm
Found another script that didn't reported "duplicate" rows..
June 17, 2021 at 3:09 pm
In the first query, you need to specify a specific db to look at, or include the db name in the results. The object_ids and index_ids are unique only within one db.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply