SELECT ISNULL(b.groupname, 'LOG') AS 'File Group' , Name , [Filename] , CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space(MB)] , CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)] , CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)] , CONVERT(Decimal(15,2),CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2))/CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2))*100) FreeSpace_Ratio FROM dbo.sysfiles a (NOLOCK) left outer JOIN sysfilegroups b (NOLOCK) ON a.groupid = b.groupid --ORDER BY b.groupname
Archive for August 4th, 2011
Available File group size
Posted by Simon Cho on 08/04/2011
Posted in Common | Tagged: filegroup, freesize, size | Leave a Comment »
object size each table and index (update for partitioning table)
Posted by Simon Cho on 08/04/2011
This query can check each table and index
And also, if it is partitioning table, it will show up partition # and each partition # size.
select FILEGROUP_NAME(au.data_space_id) as file_group
, o.name
, i.index_id
, i.name as index_name
, au.partition_number
, au.size_MB
, case when i.data_space_id > 65000 then 'Y' else 'N' end IsPartitionedTable
from (
select object_id, index_id, partition_number, data_space_id
, convert(numeric(10,2)
, SUM(in_row_data_page_count
+ lob_used_page_count
+ row_overflow_used_page_count) * 8/1024.0) size_MB
from sys.allocation_units au
join sys.dm_db_partition_stats ps
on au.container_id = ps.partition_id and au.type=1
group by object_id, index_id, partition_number, data_space_id
) au
join sys.indexes i
on i.object_id = au.object_id and i.index_id = au.index_id
join sys.all_objects o
on i.[object_id] = o.[object_id] and o.type='U'
order by 1,2,3,4
Posted in Common | Tagged: index size, object size, partition, table size | 1 Comment »
Disk IO response time check.
Posted by Simon Cho on 08/04/2011
select convert(varchar(19), getdate(), 121) as date_time
, db_name(df.database_id) as DatabaseName
, f.name as FileLogicalName
, f.filename
, case when num_of_reads+num_of_writes>0 then io_stall/(num_of_reads+num_of_writes) else 0 end as ‘ms/io’
, case when num_of_reads>0 then io_stall_read_ms/num_of_reads else 0 end as ‘ms/Read io’
, case when num_of_writes>0 then io_stall_write_ms/num_of_writes else 0 end as ‘ms/Write io’
from sys.dm_io_virtual_file_stats(null,null) as df
join sys.sysaltfiles as f
on f.dbid= df.database_id
and f.fileid = df.file_id
–where df.database_id in(DB_ID(‘db_passport’), DB_ID(‘TempDB’))
where case when num_of_reads+num_of_writes>0 then io_stall/(num_of_reads+num_of_writes) else 0 end >50
or case when num_of_reads>0 then io_stall_read_ms/num_of_reads else 0 end >50
or case when num_of_writes>0 then io_stall_write_ms/num_of_writes else 0 end >50
order by 5 desc
Posted in Common | Leave a Comment »
Simon's SQL 