June 17, 2009 at 4:25 pm
select so.Name as TableName
,TableSizeMB = si.dpages *8 / 1024
,IndexSizeMB = sum(isnull(si2.used,0))*8 / 1024
,TotalSizeMB = (si.dpages * 8 /1024) + (sum(isnull(si2.used,0))*8 / 1024)
,TableSizeKB = si.dpages *8
,IndexSizeKB = sum(isnull(si2.used,0))*8
,f.Name as FileGroupName
,d.physical_name as FGFileName
from sysindexes si
Inner join sys.objects so
on so.object_id = si.id
and so.is_ms_shipped = 0
and so.type = 'U'
and si.indid in (0,1)
Inner join sysindexes si2
on so.object_id = si2.id
and si2.indid > 1
and si2.indid < 255
Inner Join sys.filegroups f
on f.data_space_id = si.groupid
Inner Join sys.database_files d
on f.data_space_id = d.data_space_id
group by so.Name,si.dpages,f.Name,d.physical_name
order by FileGroupName asc,TableSizeMB desc
I am building this query to determine how much space tables use, which filegroups they belong to, and since there are multiple files in each filegroup - I would like to find how much data for TableA exists on each file in the filegroup. This will make the reporting more accurate. As you can see now, if there are multiple files - I will get the same value for each file in the group as far as Table and Index Size matter.
SQL 2005; many filegroups and some have multiple files, while others do not; no partitioning yet.
I also realize it would be more accurate to query master.dbo.spt_values to find the low value. However, in my scenario it is 8.
Any Ideas?
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
June 18, 2009 at 9:05 am
Updated query, but still looking for the ability to find the allocation of each table to each file in the filegroup.
So far it is not looking too promising.
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
June 18, 2009 at 3:13 pm
current version
with tablesize as (
select so.Name as TableName
,TableSizeMB = convert(decimal(15,2),si.dpages *8 / 1024)
,IndexSizeMB = convert(decimal(15,2),sum(isnull(si2.used,0))*8 / 1024)
,TotalSizeMB = convert(decimal(15,2),(si.dpages * 8 /1024) + (sum(isnull(si2.used,0))*8 / 1024))
,TableFreeMB = convert(decimal(15,2),(si.reserved * 8 /1024) -(si.used * 8/1024))
,TableSizeKB = convert(decimal(15,2),si.dpages *8)
,IndexSizeKB = convert(decimal(15,2),sum(isnull(si2.used,0))*8)
,f.Name as FileGroupName
,d.physical_name as FGFileName
,(select convert(decimal(15,2),sum(reserved)* 8 /1024) from sysindexes where indid in (0,1)) as DBSize
from sysindexes si
Inner join sys.objects so
on so.object_id = si.id
and so.is_ms_shipped = 0
and so.type = 'U'
and si.indid in (0,1)
Inner join sysindexes si2
on so.object_id = si2.id
and si2.indid > 1
and si2.indid < 255
Inner Join sys.filegroups f
on f.data_space_id = si.groupid
Inner Join sys.database_files d
on f.data_space_id = d.data_space_id
group by so.Name,si.dpages,f.Name,d.physical_name,si.reserved,si.used
)
Select TableName,TableSizeMB,IndexSizeMB,TotalSizeMB,TableFreeMB,TableSizeKB,IndexSizeKB,FileGroupName,FGFileName,DBSize
,convert(decimal(15,12),(TableSizeMB + IndexSizeMB)/DBSize) * 100 as TablePercentofDB
from TableSize
Order by FileGroupName asc,TableSizeMB desc
Still looking for that miracle solution
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply