October 8, 2008 at 2:56 pm
Hello,
I am trying to analyze existing indexes on few tables. I think I can do this by using system views like
sys.db_dm_index_usagestats, physicalstats and operationalstats.
I am here trying to figure out disk space used by each index, I/O stats ,fragmentation etc
I am wondering if ,Apart these views, there are some other effective measures for the same?
For accessing these views, one needs to have VIEW DATABASE STATE permission. As of now, I don't have it.
However,If this is the only way to access information then,
Thanks
October 8, 2008 at 3:13 pm
select * from sys.dm_db_index_physical_stats(db_id(),null,null,null,'detailed')
^can get your free space and fragmentation percentage among other things.
select object_name(p.object_id) as Table_Name
,ISNULL(name, 'THIS IS A TABLE') as 'Index name'
,CAST(used_pages as decimal(12,2))/128 as 'Used Size (MB)'
,CAST(total_pages as decimal(12,2))/128 as 'Total Size (MB)'
,is_primary_key
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
--where used_pages > 127 and total_pages > 255
order by total_pages desc, used_pages desc
^can get your sizes.
Craig Outcalt
October 9, 2008 at 6:21 am
Thanks for providing the code. I appreciate it.
Through the second code, I can see size of each and every index.
However, for first code, I need to have VIEW DATABASE STATE permission along with CONTROL Permission.
Since I do not need to analyze whole server, i don't think I need VIEW SERVER STATE permission. Right?
Just CONTROL & VIEW DATABASE STATE should do??
Thanks
October 9, 2008 at 7:37 am
Yes, you are correct.
CONTROL and VIEW DATABASE STATE are all you need for sys.dm_db_index_physical_stats run (if you're not wildcarding the first argument with NULL)
~Craig
Craig Outcalt
October 9, 2008 at 8:54 am
Thank You. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply