March 8, 2010 at 11:50 am
The index_size in sp_spaceused is the sum of all the indexes. How to find the size of an individual index for all tables in a particular database?
March 8, 2010 at 12:20 pm
Look at the dynamic managment view to see if it has what you are looking for.
sys.dm_db_index_physical_stats
March 8, 2010 at 12:41 pm
try this....I got it from this gentleman (http://www.sqlservercentral.com/scripts/Index+Management/31800/[/url])
-- Select all table names, their individual indexes with keys, description
-- and disk size in MB into a temporary table.
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @TabName varchar(100)
CREATE TABLE #T (TabName varchar(200), IndexName varchar(200), IndexDescr varchar(200), IndexKeys varchar(200), IndexSize_MB int)
DECLARE TCursor CURSOR FAST_FORWARD LOCAL FOR
SELECT name FROM sysobjects WHERE xtype = 'U'
OPEN TCursor
FETCH NEXT FROM TCursor INTO @TabName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #T (IndexName, IndexDescr, IndexKeys)
EXEC sp_helpindex @TabName
UPDATE #T SET TabName = @TabName WHERE TabName IS NULL
FETCH NEXT FROM TCursor INTO @TabName
END
CLOSE TCursor
DEALLOCATE TCursor
GO
DECLARE @ValueCoef int
SELECT @ValueCoef = low FROM Master.dbo.spt_values WHERE number = 1 AND type = N'E'
UPDATE #T SET IndexSize_MB =
((CAST(sysindexes.used AS bigint) * @ValueCoef)/1024)/1024
FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
INNER JOIN #T T ON T.TabName = sysobjects.name AND T.IndexName = sysindexes.name
SELECT * FROM #T
--WHERE IndexDescr LIKE '%nonclustered%' --Here various filters can be applied
ORDER BY TabName, IndexName
GO
DROP TABLE #T
GO
March 8, 2010 at 1:07 pm
Here's another script. This uses the suggestion by Ron.
Declare @DBNamevarchar(50)
Set @DBName = 'YOURDBNAME'
Select Db_name(ps.database_id) as DBName,object_name(ps.object_id) as TableName,I.name as IndexName
,sum(isnull(ps.avg_record_size_in_bytes,0) * isnull(ps.record_count,0)) as IndexSizeBytes
,sum((isnull(ps.avg_record_size_in_bytes,0) * isnull(ps.record_count,0)) /1024/1024) as IndexSizeMB
From sys.dm_db_index_physical_stats(DB_ID(@DBName), Null, NULL, NULL , 'DETAILED') ps
Inner Join sys.indexes I
On I.object_id = ps.object_id
And I.index_id = ps.index_id
Group By ps.database_id,ps.object_id,i.Name
Order by IndexSizeMB desc
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 8, 2010 at 2:00 pm
Good One jason... No Cursors.. 🙂
I could use that script as well...
-Roy
March 8, 2010 at 2:04 pm
Roy Ernest (3/8/2010)
Good One jason... No Cursors.. 🙂I could use that script as well...
Thanks Roy.
Just pounded it out today.
I tried assigning "NULL" to the variable like:
Set @DBName = NULL
That doesn't work properly. So, I would avoid trying the script as is to populate index sizes for all tables in all databases - as the script is anyway.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 9, 2010 at 11:19 am
Declare @DBName varchar(50)
Set @DBName = 'YOURDBNAME'
Select Db_name(ps.database_id) as DBName,object_name(ps.object_id) as TableName,I.name as IndexName
,sum(isnull(ps.avg_record_size_in_bytes,0) * isnull(ps.record_count,0)) as IndexSizeBytes
,sum((isnull(ps.avg_record_size_in_bytes,0) * isnull(ps.record_count,0)) /1024/1024) as IndexSizeMB
From sys.dm_db_index_physical_stats(DB_ID(@DBName), Null, NULL, NULL , 'DETAILED') ps
Inner Join sys.indexes I
On I.object_id = ps.object_id
And I.index_id = ps.index_id
Group By ps.database_id,ps.object_id,i.Name
Order by IndexSizeMB desc
The above script is NOT working as expected. It's displaying only 1 row that to system table info as below:
DBName TableName IndexName IndexSizeBytes IndexSizeMB
Mydbspt_fallback_devNULL 92 8.7738037109375E-05
March 9, 2010 at 11:55 am
pshaship (3/9/2010)
The above script is NOT working as expected. It's displaying only 1 row that to system table info as below:
DBName TableName IndexName IndexSizeBytes IndexSizeMB
Mydbspt_fallback_devNULL 92 8.7738037109375E-05
Is that when you run it from your database or from master?
Also, how many indexes are listed by this query
select i.* from sys.indexes i
Inner Join sys.objects o
on o.object_id = i.object_id
where i.index_id >= 1
And o.is_ms_shipped = 0
The above query will give you a list of the indexes in your system for user tables.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 9, 2010 at 12:16 pm
Is that when you run it from your database or from master?
NO. It's against User database.
I ran the below query in User database & able to get all indexes
select i.* from sys.indexes i
Inner Join sys.objects o
on o.object_id = i.object_id
where i.index_id >= 1
And o.is_ms_shipped = 0
March 9, 2010 at 12:24 pm
Very interesting. I don't get that kind of result on any of the SQL 2005 or 2008 servers that I tested it against.
Did you change 'YOURDBNAME' to be the correct dbname that you are trying to test against?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 10, 2010 at 2:07 am
better use foll script
-- Create the temp table for further querying
CREATE TABLE #temp(
rec_idint IDENTITY (1, 1),
table_namevarchar(128),
nbr_of_rowsint,
data_space_KBdecimal(15,2),
index_space_KBdecimal(15,2),
total_size_KBdecimal(15,2),
percent_of_dbdecimal(15,12),
db_sizedecimal(15,2))
-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space_KB,index_space_KB) exec sp_mstablespace '?'",
@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"
-- Set the total_size and total database size fields
UPDATE #temp
SET total_size_KB = (data_space_KB + index_space_KB), db_size = (SELECT SUM(data_space_KB + index_space_KB) FROM #temp)
-- Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size_KB/db_size) * 100
-- Get the data
SELECT *
FROM #temp
ORDER BY total_size_KB DESC
-- Comment out the following line if you want to do further querying
DROP TABLE #temp
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
March 10, 2010 at 3:37 am
pshaship (3/9/2010)
Declare @DBName varchar(50)
Set @DBName = 'YOURDBNAME'
Select Db_name(ps.database_id) as DBName,object_name(ps.object_id) as TableName,I.name as IndexName
,sum(isnull(ps.avg_record_size_in_bytes,0) * isnull(ps.record_count,0)) as IndexSizeBytes
,sum((isnull(ps.avg_record_size_in_bytes,0) * isnull(ps.record_count,0)) /1024/1024) as IndexSizeMB
From sys.dm_db_index_physical_stats(DB_ID(@DBName), Null, NULL, NULL , 'DETAILED') ps
Inner Join sys.indexes I
On I.object_id = ps.object_id
And I.index_id = ps.index_id
Group By ps.database_id,ps.object_id,i.Name
Order by IndexSizeMB desc
The above script is NOT working as expected. It's displaying only 1 row that to system table info as below:
DBName TableName IndexName IndexSizeBytes IndexSizeMB
Mydbspt_fallback_devNULL 92 8.7738037109375E-05
add use yourdbname before using above script .
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 10, 2010 at 9:33 am
sanketahir1985 (3/10/2010)
better use foll script-- Create the temp table for further querying
CREATE TABLE #temp(
rec_idint IDENTITY (1, 1),
table_namevarchar(128),
nbr_of_rowsint,
data_space_KBdecimal(15,2),
index_space_KBdecimal(15,2),
total_size_KBdecimal(15,2),
percent_of_dbdecimal(15,12),
db_sizedecimal(15,2))
-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space_KB,index_space_KB) exec sp_mstablespace '?'",
@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"
-- Set the total_size and total database size fields
UPDATE #temp
SET total_size_KB = (data_space_KB + index_space_KB), db_size = (SELECT SUM(data_space_KB + index_space_KB) FROM #temp)
-- Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size_KB/db_size) * 100
-- Get the data
SELECT *
FROM #temp
ORDER BY total_size_KB DESC
-- Comment out the following line if you want to do further querying
DROP TABLE #temp
I have this same script and decided against suggesting it. This script does not break down the index size by index. Rather it breaks down total index size by table and lumps all indexes for the table together.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply