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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply