데이터베이스

파티션 구성 정보를 조회하면서 파티션의 사이즈 및 압축 정보까지 한꺼 번에 볼 수 있도록 수정 하였음. select a.PartitionName, a.FuncName, a.PartitionCount, a.Type, a.Value, a.Equality, a.Destnation_id, c.name as 'FileGroupName' , object_name(d.object_id) as 'ObjectName' , d.used_page_count * 8 as 'Total Used(KB)' , d.reserved_page_count * 8 as 'Total Reserved(KB)' , row_count as 'RowCount' , data_compression_desc from ( select a.name as '..

파티션을 고려하지 못한 부분과 index_id 비교를 잘못해서 인덱스 크기 잘못 계산된 부분 재공유 합니다. 파티션을 이루는 파티션의 개수와 압축을 이용한 파티션의 개수를 같이 보여 줍니다. SQL Server 2008 Enterprise Edition이 아니면 압축 관련 부분은 주석 처리하시면 됩니다. select object_name(max(object_id)) , max(b.PartitionCnt) as 'PartitionCnt' , max(b.CompressionCnt) as 'CompressionPartitionCnt' , sum(used_page_count) * 8 as 'Total Used(KB)' , sum(reserved_page_count) * 8 as 'Total Reserved(KB..

select a.name , b.size * 8 / 1024 as 'PhysicalSize(MB)', (b.size - sum(total_pages)) * 8 / 1024 as 'UnAllocationSize(MB)' , sum(total_pages) * 8 / 1024 as 'TotalAllocation(MB)', sum(used_pages) * 8 / 1024 as 'UsedAllocation(MB)' , sum(data_pages) * 8 / 1024 as 'DataSize(MB)', sum(total_pages - used_pages) * 8 / 1024 as 'FreeSize(MB)' from sys.filegroups a with(nolock) inner join ( select data_sp..

select top 20 db_name(a.database_id) as 'DBName', object_name(b.object_id, a.database_id) as 'OBJName' , b.index_id, isnull(max(c.name), 'Heap') as 'IndexName', a.page_type , left(cast(count(*) * 1.0 / max(b.used_pages) as varchar(100)), 6) as 'Ratio' , count(*) as 'BufferPageCNT', max(b.used_pages) as 'UsedPageCNT', max(b.total_pages) as 'TotalPageCNT' , sum(case when is_modified = 1 then 1 els..

select a.session_id as 'BlockedSessionID', b.blocking_session_id , b.wait_duration_ms / 1000 as 'WaitDuration(Sec)', b.wait_type , c.resource_type, db_name(c.resource_database_id) as 'ResourceDBName', c.request_mode, c.request_type, c.request_status, b.resource_description , db_name(e.dbid) as 'BlockedDBName', object_name(e.objectid) as 'BlockedObjectName', e.text as 'BlockedQuery' , substring(e..

select top 10 a.session_id, a.host_name, a.client_interface_name, a.login_name, a.status, a.last_request_start_time , b.start_time, b.status, b.command, db_name(c.dbid) as 'DBName', object_name(c.objectid, c.dbid) as 'OBJName', b.last_wait_type, b.wait_time , b.cpu_time, b.total_elapsed_time, b.reads, b.writes, b.logical_reads , substring(c.text, (b.statement_start_offset / 2) + 1, ((case b.stat..

select top (100) case when c.dbid = 32767 then 'ResourceDB' when c.dbid is null then 'NONE' else db_name(c.dbid) end as 'DBName' , isnull(object_name(c.objectid, c.dbid), c.text) as 'PlanQuery' , b.execution_count as 'Total Execute' , b.execution_count * 1.0 / datediff(ss, b.creation_time, dateadd(ss, 1, b.last_execution_time)) as 'ExecCount/sec' , b.total_worker_time * 1.0 / b.execution_count /..