Фрагментация физического файла базы данных SQL

Думаю, эта статья дает отличный обзор дефрагментации дисков SAN.

http://www.las-solanas.com/storage_virtualization/san_volume_defragmentation.php

Основным моментом является то, что дефрагментация в хранилище SAN не рекомендуется, поскольку при представлении LUN трудно сопоставить физическое расположение блоков на диске, когда местоположение было виртуализировано SAN.

Если вы использовали сопоставления устройств RAW или у вас есть прямой доступ к набору RAID, который является LUN, с которым вы работаете, я мог бы увидеть, что дегфрагментация имеет положительный эффект, но если вам предоставляется «виртуальный» LUN из общего RAID- 5 комплект, шт.

Несколько частей к этому вопросу и ответу:

Как уже отмечал Кевин, физическая фрагментация файлов на самом деле не актуальна для хранилища Enterprise SAN, так что добавлять туда нечего. Это действительно сводится к подсистеме ввода-вывода и к тому, насколько вероятно, что вы сможете заставить диски перейти от более случайных операций ввода-вывода при выполнении сканирования к более последовательным операциям ввода-вывода при выполнении сканирования. для DAS это более вероятно, а для сложной SAN по принципу slice-n-dice - вероятно, нет.

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

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

Ты не делай иметь делать это в любом конкретном порядке, хотя, если вы позаботитесь о фрагментации файловой системы, а затем перестроите все свои индексы и вызовете еще большую фрагментацию файловой системы за счет увеличения нескольких файлов на дефрагментированном томе, вы, вероятно, будете отмечены галочкой. Но вызовет ли это проблемы с производительностью? Как обсуждалось выше, это зависит от :-D

Надеюсь это поможет!

Как лучше всего исправить физическую фрагментацию файлов в производственном блоке SQL?

Я запускаю контиг SYSINTERNALS для файлов своей базы данных.

Видеть http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx

Я бы порекомендовал соответствующим образом изменить размер базы данных, выключить sql-сервер, скопировать файл базы данных на другой дисковый массив, а затем скопировать его обратно для дефрагментации. По моему опыту, намного быстрее, чем при использовании дефрагментации Windows.

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

Если ваши индексы дефрагментированы и статистика обновляется (очень важно), а ввод-вывод все еще рассматривается как узкое место, то вы страдаете от других вещей, кроме физической фрагментации. Вы использовали более 80% диска? Достаточно ли у вас дисков? Достаточно ли оптимизированы ваши запросы? Вы часто просматриваете таблицы или, что еще хуже, выполняете поиск по индексу с последующим поиском по кластерному индексу? Посмотрите планы запросов и используйте команду «установить статистику io on», чтобы узнать, что на самом деле происходит с вашим запросом. (ищите большое количество логических или физических чтений)

Пожалуйста, дайте мне знать, если я ошибаюсь.

/ Хокан Винтер

Возможно, индексы недостаточно оптимизированы для вашего приложения, и у вас нет Veritas I3 для оптимизации вашей базы данных, тогда вы можете использовать подобный оператор для поиска недостающих индексов:

       SELECT
      mid.statement,
      mid.equality_columns,
      mid.inequality_columns,
      mid.included_columns,
      migs.user_seeks,
      migs.user_scans,
      migs.last_user_seek,
      migs.avg_user_impact,
      user_scans,
      avg_total_user_cost,
      avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) AS [weight]--, migs.*--, mid.*
   FROM
      sys.dm_db_missing_index_group_stats AS migs
      INNER JOIN sys.dm_db_missing_index_groups AS mig
         ON (migs.group_handle = mig.index_group_handle)
      INNER JOIN sys.dm_db_missing_index_details AS mid
         ON (mig.index_handle = mid.index_handle)
   ORDER BY
      avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC ;

Или подобное выражение для поиска индексов, которые не используются в операторах выбора и снижают производительность обновления / вставки:

    CREATE PROCEDURE [ADMIN].[spIndexCostBenefit]
    @dbname [nvarchar](75)
WITH EXECUTE AS CALLER
AS
--set @dbname='Chess'
declare @dbid nvarchar(5)
declare @sql nvarchar(2000)
select @dbid = convert(nvarchar(5),db_id(@dbname))

set @sql=N'select ''object'' = t.name,i.name
        ,''user reads'' = iu.user_seeks + iu.user_scans + iu.user_lookups
        ,''system reads'' = iu.system_seeks + iu.system_scans + iu.system_lookups
        ,''user writes'' = iu.user_updates
        ,''system writes'' = iu.system_updates
from '+ @dbname + '.sys.dm_db_index_usage_stats iu
,' + @dbname + '.sys.indexes i
,' + @dbname + '.sys.tables t
where 
    iu.database_id = ' + @dbid + '
and iu.index_id=i.index_id
and iu.object_id=i.object_id
and iu.object_id=t.object_id
AND (iu.user_seeks + iu.user_scans + iu.user_lookups)<iu.user_updates
order by ''user reads'' desc'

exec sp_executesql @sql

set @sql=N'SELECT
   ''object'' = t.name,
   o.index_id,
   ''usage_reads'' = user_seeks + user_scans + user_lookups,
   ''operational_reads'' = range_scan_count + singleton_lookup_count,
   range_scan_count,
   singleton_lookup_count,
   ''usage writes'' = user_updates,
   ''operational_leaf_writes'' = leaf_insert_count + leaf_update_count + leaf_delete_count,
   leaf_insert_count,
   leaf_update_count,
   leaf_delete_count,
   ''operational_leaf_page_splits'' = leaf_allocation_count,
   ''operational_nonleaf_writes'' = nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count,
   ''operational_nonleaf_page_splits'' = nonleaf_allocation_count
FROM
   ' + @dbname + '.sys.dm_db_index_operational_stats(' + @dbid + ', NULL, NULL, NULL) o,
   ' + @dbname + '.sys.dm_db_index_usage_stats u,
    ' + @dbname + '.sys.tables t
WHERE
   u.object_id = o.object_id
   AND u.index_id = o.index_id
    and u.object_id=t.object_id
ORDER BY
   operational_reads DESC,
   operational_leaf_writes,
   operational_nonleaf_writes'

exec sp_executesql @sql

GO

У меня есть несколько других операторов SQL, которые я использую при анализе проблем производительности в производственной среде, но я думаю, что эти два - хорошее начало.

(Я знаю, этот пост немного тематический, но я подумал, что вам это может быть интересно, так как он связан со стратегией индексации)

/ Хокан Винтер