March 16, 2011 at 3:21 pm
I give up!!! I've search google for countless variations for the follwing keywords "DMV show index size (KB)" and get everything but what I am specifically looking for.
In the Object Explorer Details in SQL 2008, I typically show Name, Schema, Create Date, Row Count, Data Space Used (KB) and Index Space Used (KB).
How on earth can I find the "Index Space Used (KB)." information using TSQL???
I am ultimately writing a script to dynamically rebuild/reorg indexes and want to place a check in there that notes the size of the index itself, and then compare that against the remaining space on disk
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 16, 2011 at 3:40 pm
This appears to be the closest thing I can find:
SELECT IndexName = name,
IndexSizeMB = CEILING(1.0 * dpages / 128),
IndexSizeKB = dpages * 8
FROM sysindexes
WHERE NAME LIKE '%MyTable%'
Gail? Are you out there? 🙂
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 16, 2011 at 4:07 pm
Are you looking for sys.dm_db_index_physical_stats?
A query would look something like this:
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2008R2'),object_id('person.address'),NULL,NULL,'sampled') AS ddips
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 16, 2011 at 4:08 pm
BTW, Gail lives in South Africa which is about 6 hours ahead of us. It's near midnight her time. She's up this late sometimes, but sometimes not. Just so you know.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply