July 17, 2013 at 8:36 am
Hello Everyone
Happy hump day to all.
I am working on gather some stats for each table in a database. I am gathering row counts, which I already have. But my question is where can I find the data for the size of the table and the size of each index per each table? I like the way that one can see the index space, data space and row count for each particular table by selecting "Storage" under the properties of the table. Where may I find this information, so that I can select that data into another table for stats reporting?
Thank you in advance for your assistance, suggestions and comments.
Andrew SQLDBA
July 17, 2013 at 10:51 am
My object naming needs to be cleaned up a little but I think you are looking for something like this:
IF OBJECT_ID('tempdb..#tableDataPrep') IS NOT NULL
DROP TABLE #tableDataPrep;
IF OBJECT_ID('tempdb..#tableData') IS NOT NULL
DROP TABLE #tableData;
IF OBJECT_ID('tempdb..#IndexData') IS NOT NULL
DROP TABLE #IndexData;
CREATE TABLE #tableDataPrep
(id int identity primary key,
name varchar(100) not null,
[rows] varchar(100) not null,
reserved_kb varchar(100) not null,
data_kb varchar(100) not null,
index_size_kb varchar(100) not null,
unused_kb varchar(100) not null);
CREATE TABLE #tableData
(id int identity primary key,
name varchar(100) not null,
[rows] int not null,
reserved_kb bigint not null,
data_kb bigint not null,
index_size_kb int not null,
unused_kb int not null);
CREATE TABLE #IndexData
(id int identity primary key,
[schema] varchar(100) not null,
table_name varchar(100) not null,
IndexName varchar(120) not null,
IndexSizeKB int not null)
-- get the table info
EXEC sp_msforeachtable'
INSERT INTO #tableDataPrep (name,[rows],reserved_kb, data_kb,index_size_kb,unused_kb)
EXEC sp_spaceused[?];'
-- Removing the KB from the record for easier aggregations
INSERT INTO #tableData
SELECT name,
[rows],
REPLACE(reserved_kb,' KB',''),
REPLACE(data_kb,' KB',''),
REPLACE(index_size_kb,' KB',''),
REPLACE(unused_kb,' KB','')
FROM #tableDataPrep;
WITH IndexSizes AS
(SELECTi.name AS IndexName,
SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name),
IndexTableInfo AS
(SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],
T.[name] AS [table_name], I.[name] AS [index_name], I.[type_desc]
FROM sys.[tables] AS T
JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP')
INSERT INTO #IndexData ([schema], table_name, IndexName, IndexSizeKB)
SELECTidt.[Schema],
idt.table_name,
idt.index_name COLLATE SQL_Latin1_General_CP1_CI_AS +'('+idt.type_desc+')' AS IndexName,
ids.IndexSizeKB
FROM IndexSizes ids
RIGHT JOIN IndexTableInfo idt ON idt.index_name=ids.IndexName
GROUP BY [Schema], table_name,
idt.index_name COLLATE SQL_Latin1_General_CP1_CI_AS +'('+idt.type_desc+')',
IndexSizeKB
ORDER BY [Schema], table_name,
idt.index_name COLLATE SQL_Latin1_General_CP1_CI_AS +'('+idt.type_desc+')';
--Output
SELECT * FROM #tableData;
SELECT * FROM #IndexData;
-- Itzik Ben-Gan 2001
July 17, 2013 at 10:53 am
Try starting with the sys.dm_db_partition_stats DMV. That will give you page counts per index, you can expand from there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2013 at 1:03 pm
Here is what I use
Use {your database here};
Go
With partitionStats (object_id, rows, reserved, data, used)
As (
Select ps.object_id
,sum(Case When ps.index_id < 2 Then row_count Else 0 End) As [rows]
,sum(ps.reserved_page_count) As reserved
,sum(Case When ps.index_id < 2
Then ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count
Else ps.lob_used_page_count + ps.row_overflow_used_page_count
End ) As data
,sum(ps.used_page_count) As used
From sys.dm_db_partition_stats ps
Group By
ps.object_id
)
, internalTables (parent_id, reserved, used)
As (
Select it.parent_id
,sum(ps.reserved_page_count) As reserved
,sum(ps.used_page_count) As used
From sys.dm_db_partition_stats ps
Inner Join sys.internal_tables it On it.object_id = ps.object_id
Where it.internal_type In (202, 204)
Group By
it.parent_id
)
Select a3.name As [schemaname]
,a2.name As [tablename]
,a1.rows As row_count
,(a1.reserved + isnull(a4.reserved, 0)) / 128.0 As 'Reserved (MB)'
,a1.data / 128.0 As 'Data (MB)'
,Case When (a1.used + isnull(a4.used, 0)) > a1.data
Then (a1.used + isnull(a4.used, 0)) - a1.data
Else 0
End / 128.0 As 'Index (MB)'
,Case When (a1.reserved + isnull(a4.reserved, 0)) > a1.used
Then (a1.reserved + isnull(a4.reserved, 0)) - a1.used
Else 0
End / 128.0 As 'Unused (MB)'
From partitionStats a1
Left Join internalTables a4 On a4.parent_id = a1.object_id
Inner Join sys.all_objects a2 On a1.object_id = a2.object_id
Inner Join sys.schemas a3 On a2.schema_id = a3.schema_id
Where a2.type <> N'S'
And a2.type <> N'IT'
Order By
'Data (MB)' desc;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 18, 2013 at 9:16 am
Thank You to everyone
Jeffery, Special thanks to you. That is exactly what I was in need of.
Hope that you all have a great day.
Andrew SQLDBA
July 19, 2013 at 5:45 am
I prefer this
selectOBJECT_NAME(o.OBJECT_ID)AS TableName
, ISNULL(i.name, 'HEAP')AS IndexName
, i.index_idAS IndexID
, i.[type]AS IndexType
, i.is_disabledAS IsDisabled
, FILEGROUP_NAME(i.data_space_id)AS FGName
, p.partition_numberAS PartitionNo
, p.[rows]AS [RowCnt]
, p.data_compression_descAS CompressionType
, au.type_descAS AllocType
, au.total_pages / 128AS TotalMBs
, au.used_pages/ 128AS UsedMBs
, au.data_pages/ 128AS DataMBs
from sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units au ON
CASE
WHEN au.[type] IN (1,3) THEN p.hobt_id
WHEN au.[type] = 2 THEN p.partition_id
END = au.container_id
WHERE o.is_ms_shipped <> 1
ORDER BY p.rows desc
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply