Mamy kilka baz danych, w których tworzonych i upuszczanych jest wiele tabel. Z tego, co możemy powiedzieć, SQL Server nie przeprowadza wewnętrznej konserwacji tabel systemowych , co oznacza, że z czasem mogą się bardzo rozdrobnić i rozdęć. Powoduje to niepotrzebną presję na pulę buforów, a także negatywnie wpływa na wydajność operacji, takich jak obliczanie wielkości wszystkich tabel w bazie danych.
Czy ktoś ma sugestie dotyczące minimalizacji fragmentacji tych podstawowych tabel wewnętrznych? Jednym oczywistym rozwiązaniem mogłoby być uniknięcie tworzenia tylu tabel (lub tworzenia wszystkich tabel przejściowych w tempdb), ale na potrzeby tego pytania powiedzmy, że aplikacja nie ma takiej elastyczności.
Edycja: Dalsze badania pokazują to pytanie bez odpowiedzi , które wydaje się ściśle powiązane i wskazuje, że pewna forma ręcznej konserwacji ALTER INDEX...REORGANIZEmoże być opcją.
Wstępne badania
Metadane dotyczące tych tabel można wyświetlić w sys.dm_db_partition_stats:
-- The system base table that contains one row for every column in the system
SELECT row_count,
(reserved_page_count * 8 * 1024.0) / row_count AS bytes_per_row,
reserved_page_count/128. AS space_mb
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('sys.syscolpars')
AND index_id = 1
-- row_count: 15,600,859
-- bytes_per_row: 278.08
-- space_mb: 4,136Jednak sys.dm_db_index_physical_statswydaje się , że nie obsługuje przeglądania fragmentacji tych tabel:
-- No fragmentation data is returned by sys.dm_db_index_physical_stats
SELECT *
FROM sys.dm_db_index_physical_stats(
DB_ID(),
OBJECT_ID('sys.syscolpars'),
NULL,
NULL,
'DETAILED'
)Skrypty Oli Hallengren zawierają również parametr umożliwiający rozważenie defragmentacji is_ms_shipped = 1obiektów, ale procedura po cichu ignoruje tabele podstawowe systemu, nawet jeśli ten parametr jest włączony. Ola wyjaśniła, że jest to oczekiwane zachowanie; msdb.dbo.backupsetbrane są pod uwagę tylko tabele użytkowników (nie tabele systemowe), które są ms_shipped (np. ).
-- Returns code 0 (successful), but does not do any work for system base tables.
-- Instead of the expected commands to update statistics and reorganize indexes,
-- no commands are generated. The script seems to assume the target tables will
-- appear in sys.tables, but this does not appear to be a valid assumption for
-- system tables like sys.sysrowsets or sys.syscolpars.
DECLARE @result int;
EXEC @result = IndexOptimize @Databases = 'Test',
@FragmentationLow = 'INDEX_REORGANIZE',
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationHigh = 'INDEX_REORGANIZE',
@PageCountLevel = 0,
@UpdateStatistics = 'ALL',
@Indexes = '%Test.sys.sysrowsets.%',
-- Proc works properly if targeting a non-system table instead
--@Indexes = '%Test.dbo.Numbers.%',
@MSShippedObjects = 'Y',
@Execute = 'N';
PRINT(@result);
Dodatkowe wymagane informacje
Użyłem adaptacji zapytania Aarona poniżej wykorzystania puli buforów tabel systemowych i okazało się, że w puli buforów znajdują się dziesiątki GB tabel systemowych tylko dla jednej bazy danych, w niektórych przypadkach około 80% tego miejsca to wolne miejsce .
-- Compute buffer pool usage by system table
SELECT OBJECT_NAME(p.object_id),
COUNT(b.page_id) pages,
SUM(b.free_space_in_bytes/8192.0) free_pages
FROM sys.dm_os_buffer_descriptors b
JOIN sys.allocation_units a
ON a.allocation_unit_id = b.allocation_unit_id
JOIN sys.partitions p
ON p.partition_id = a.container_id
AND p.object_id < 1000 -- A loose proxy for system tables
WHERE b.database_id = DB_ID()
GROUP BY p.object_id
ORDER BY pages DESC