Блог

Главная - Вопросы по программированию - SQL Server: количество дисковых блоков, используемых индексом или таблицей

SQL Server: количество дисковых блоков, используемых индексом или таблицей

#sql-server #database-tuning

#sql-сервер #настройка базы данных

Вопрос:

Как я могу в SQL Server увидеть количество дисковых блоков, используемых индексом или таблицами?

Комментарии:

1. Это лучше подходит для dba.stackexchange.com

Ответ №1:

Используя этот запрос, вы можете определить количество страниц (8 КБ блоков пространства), используемых таблицей SQL Server:

 SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) AS TotalSpace, 
    SUM(a.used_pages) AS UsedSpace
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.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 a ON p.partition_id = a.container_id
WHERE 
    t.NAME = 'YourTableNameHere'
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    t.Name

Это суммирует все данные и индексные страницы, используемые таблицей.

Комментарии:

1. Каждая строка в этом запросе должна быть связана с идентификатором index_id или i.object_id, так разве запрос не суммирует только количество страниц, используемых для индексов ? Хотя это можно легко переписать…

2. @someName: в кластеризованной таблице (таблице с ключом кластеризации) индекс = 1 — это индекс кластеризации, который является данными (а для таблицы без ключа кластеризации это индекс = 0, куча) — так что это делает список данных и индексных страниц

3. Ага? Index_id= 1 — это просто сама таблица (если она кластеризована)? Я этого не знал. Большое спасибо!!!

4. @someName: да, index=1 — это кластеризованный индекс (который по умолчанию находится в первичном ключе, если вы не определяете что-то еще конкретно), а конечный уровень кластеризованного индекса — это данные.

Ответ №2:

Это лучшее, что я использовал до сих пор. Взято отсюда

 set nocount on
--dbcc updateusage(0) with count_rows

declare @tables table (
   sizerank int identity (1, 1) not null
 , table_name varchar (255) null
 , table_total_size_dec decimal(18, 1) null
 , table_size_measure varchar(10) null
)

insert into @tables (table_name, table_total_size_dec, table_size_measure)
select upper(name) as [table_name]
     , convert(decimal(15, 1), left(sizemb, len(sizemb)-2)) as sizedec
     , right(sizemb, 2) as measure
from      (select convert(varchar(255), so.name) as name
                , case when sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1024 > 1000 
                            then convert(varchar(255), convert(decimal(15, 1), round((sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1048000), 1)))   'mb'
                       else convert(varchar(255), convert(int, round((sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1024), 0)))   'kb' 
                       end as sizemb
           from sysindexes si join sysobjects so on si.id = so.id join sysfilegroups sf on si.groupid = sf.groupid
           where objectproperty(so.id, 'IsUserTable') = 1
           group by so.name) as dv
order by right(sizemb, 2) desc, convert(decimal(15, 1), left(sizemb, len(sizemb)-2)) desc

select t.*, indname, indid, convert(decimal(15, 1), left(indsizemb, len(indsizemb)-2)) as indsizedec, right(indsizemb, 2) as indmeasure
from @tables t
left join (
select convert(varchar(255), so.name) as tblname
     , convert(varchar(255), si.name) as indname
     , si.indid
     , case when sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1024 > 1000 
                 then convert(varchar(255), convert(decimal(15, 1), round((sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1048000), 1)))   'mb' 
            else convert(varchar(255), convert(int, round((sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1024), 0)))   'kb' 
            end as indsizemb
from sysindexes si join sysobjects so on si.id = so.id join sysfilegroups sf on si.groupid = sf.groupid 
where si.dpages > 0
group by so.name, si.name, si.indid
) as dvtablesindexes on t.table_name = dvtablesindexes.tblname
where table_total_size_dec > 0
order by sizerank, right(indsizemb, 2) desc, convert(decimal(15, 1), left(indsizemb, len(indsizemb)-2)) desc