Когда речь идет об оптимизации производительности вашей базы данных SQL Server, следует учитывать несколько факторов. Один из важных аспектов – это коэффициент заполнения ваших индексов, который определяет количество свободного места на каждой странице. В этой статье мы рассмотрим, как определить таблицы с коэффициентами заполнения менее 100 и проанализировать количество неиспользуемого пространства в вашей базе данных.

Для начала давайте рассмотрим скрипт, который предоставляет ценную информацию о ваших коэффициентах заполнения и неиспользуемом пространстве:

Это немного избыточно, но вот быстрый скрипт, который показывает ваши коэффициенты заполнения для каждой таблицы, не равные 100, а также сколько у вас неиспользуемого пространства для каждой таблицы. Он также создает скрипт для перестроения, будьте внимательны к установленным параметрам.

SELECT db.name AS databasename, tbl.name AS tablename, idx.name AS indexname, stats.index_id, fill_factor, last_user_update, user_seeks, user_scans, user_lookups, user_updates, part.rows, part.data_compression_desc, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB, 'ALTER INDEX [' + idx.name + '] ON [' + Schema_Name(sch.schema_id) + '].[' + object_Name(idx.object_id) + '] REBUILD PARTITION = ALL WITH ( FILLFACTOR = 100, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = ON )' AS Rebuild_Script
FROM sys.dm_db_index_usage_stats stats
INNER JOIN sys.tables AS tbl ON stats.object_id = tbl.object_id
INNER JOIN sys.schemas AS sch ON tbl.schema_id = sch.schema_id
INNER JOIN sys.databases AS db ON stats.database_id = db.database_id
INNER JOIN sys.indexes AS idx ON stats.object_id = idx.object_id AND stats.index_id = idx.index_id
INNER JOIN sys.partitions AS part ON stats.object_id = part.object_id AND stats.index_id = part.index_id
INNER JOIN sys.allocation_units a ON part.partition_id = a.container_id
WHERE fill_factor != 0 AND fill_factor != 100
GROUP BY user_updates, fill_factor, db.name, tbl.name, idx.name, stats.index_id, fill_factor, last_user_update, user_seeks, user_scans, user_lookups, user_updates, part.rows, part.data_compression_desc, sch.schema_id, idx.object_id

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

Выполнив этот скрипт, вы можете определить таблицы с коэффициентами заполнения менее 100 и определить количество неиспользуемого пространства в вашей базе данных. Эта информация крайне важна для поддержания эффективности и производительности вашей среды SQL Server.

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

При перестроении индексов важно учитывать установленные параметры. Сгенерированный выше запрос скрипта включает такие параметры, как PAD_INDEX, STATISTICS_NORECOMPUTE, ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, ONLINE и SORT_IN_TEMPDB. Эти параметры могут быть настроены в соответствии с вашими конкретными требованиями и средой.

Регулярное мониторинг и оптимизация коэффициентов заполнения и неиспользуемого пространства в вашей базе данных SQL Server могут значительно улучшить производительность и обеспечить эффективное хранение данных. Определение таблиц с неоптимальными коэффициентами заполнения и восстановление неиспользуемого пространства позволяет улучшить время выполнения запросов и сократить использование дискового пространства.

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