SQL Server 2005: индекс больше, чем хранятся данные

Ответ 1

Как уменьшить размер индексного файла?

Отбросьте ненужные индексы или уменьшите количество столбцов в существующих. Помните, что столбцы (столбцы) с кластеризацией являются скрытым включенным столбцом во всех некластеризованных индексах.

Если у вас есть индекс на a,b,c,d и индекс на a,b,c, вы можете подумать о том, чтобы удалить второй, поскольку первый из них охватывает второй.

Вы также можете найти потенциальные неиспользуемые индексы, посмотрев sys.dm_db_index_usage_stats

Как узнать, что хранится в индексном файле?

Он будет хранить все, что вы определили для хранения! Следующий запрос поможет вам определить, какие индексы используют наибольшее пространство и по какой причине (в данных строки, данных lob)

SELECT  convert(char(8),object_name(i.object_id)) AS table_name, i.name AS index_name, 
    i.index_id, i.type_desc as index_type,
    partition_id, partition_number AS pnum,  rows, 
    allocation_unit_id AS au_id, a.type_desc as page_type_desc, total_pages AS pages
FROM sys.indexes i JOIN sys.partitions p  
      ON i.object_id = p.object_id AND i.index_id = p.index_id
    JOIN sys.allocation_units a
      ON p.partition_id = a.container_id
      order by pages desc

Ответ 2

Мое предположение (которое, как мне кажется, также относится к marc_s), состоит в том, что вы указали, что ваши кластерные индексы, по крайней мере, для некоторых ваших таблиц, относятся к группе файлов индекса. Кластерный индекс определяет, как (и где) сохраняются фактические данные для вашей таблицы.

Проводка некоторых из вашего кода, несомненно, поможет другим определить проблему.

Я думаю, что Мартин Смит очень хорошо ответил на ваши другие вопросы. Я просто добавлю это... Если вы хотите ограничить размеры индекса, вам нужно оценить ваши индексы. Не добавляйте индексы только потому, что считаете, что они вам могут понадобиться. Проведите тестирование с реалистичными (или идеально реальными) нагрузками на базу данных, чтобы увидеть, какие индексы действительно дадут вам необходимое повышение производительности. Индексы имеют для них затраты. В дополнение к стоимости пространства, которую вы видите, они также добавляют к накладным расходам вставок и обновлений, которые должны поддерживать синхронизацию индексов. Из-за этих затрат вы всегда должны иметь веские основания добавить индекс, и вы должны сознательно думать о компромиссах.

Ответ 3

Считайте, что на самом деле довольно распространено общее хранилище, необходимое для индексов, больше, чем хранилище, необходимое для данных таблицы в данной базе данных.

Однако ваш конкретный сценарий будет казаться чрезмерным. Как указывали другие, если вы присвоили кластерный указатель для данной таблицы для размещения в отдельном файле данных (файл данных индекса), то вся эта физическая таблица также будет находиться в этом файле, так как, Кластеризованный индекс - это таблица.

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

Другие плакаты упомянули, что:

• Вы должны просмотреть свой индекс определения для повторяющихся индексов. Взгляните на Определение перекрывающих индексов Brent Ozar.
• Вам следует искать идентифицированные неиспользуемые индексов. Взгляните как статья SQL Server Pedia: Поиск неиспользуемых индексов

Другие возможности для изучения включают анализ фрагментации ваших индексов, поскольку это может увеличить требования к хранению.

Тяжелая фрагментация, особенно в кластеризованном индексе таблицы, содержащей данные LOB, может привести к значительному увеличению потребностей в хранении. Реорганизация кластеризованного индекса в таблицах, содержащих данные LOB, будет уплотнять данные LOB.

См. Реорганизация и восстановление индексов

Ответ 4

@Ответ martin-smith - это почти то, что мне нужно...

Вот как вы сортируете по размеру индекса в GB (mssql использует 8 КБ страниц == 128 страниц/МБ)

SELECT
  object_name(p.object_id) AS table_name
  , i.name AS index_name
  , i.index_id
  , i.type_desc AS index_type
  -- , partition_id
  -- , partition_number AS pnum
  -- , allocation_unit_id AS au_id
  , rows
  , a.type_desc as page_type_desc
  , total_pages/(1024 * 128.0) AS sizeGB
FROM 
    sys.indexes i
    JOIN sys.partitions p  ON i.object_id = p.object_id AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.partition_id = a.container_id
    JOIN sys.all_objects ao ON (ao.object_id = i.object_id)
    WHERE ao.type_desc = 'USER_TABLE'
ORDER BY
    -- table_name 
    sizeGB DESC