Fragmentacja plików fizycznych bazy danych SQL

Wiem, że tak naprawdę istnieją trzy rodzaje fragmentacji, o które muszę się martwić jako DBA:

1. Fragmentacja indeksu w plikach danych SQL, w tym fragmentacja indeksu klastrowego (tabeli). Zidentyfikuj to za pomocą DBCC SHOWCONTIG (w SQL 2000) lub sys.dm_ db_ index_ physical_ stats (w 2005+).

2. Fragmentacja VLF w plikach dziennika SQL. Uruchom DBCC LOGINFO, aby zobaczyć, ile VLF znajduje się w każdym pliku dziennika SQL.

3. Fizyczna fragmentacja plików bazy danych na dysku twardym. Zdiagnozuj to za pomocą narzędzia „Defragmentator dysków” w systemie Windows. (zainspirowany tym doskonałym postem na blogu )

Dużo uwagi poświęca się fragmentacji indeksu (patrz ta doskonała odpowiedź na błąd serwera autorstwa Paula Randalla), więc to nie jest sedno mojego pytania.

Wiem, że mogę zapobiec fragmentacji fizycznej (i fragmentacji VLF), gdy baza danych jest pierwotnie tworzona, poprzez zaplanowanie rozsądnego oczekiwanego pliku danych i rozmiaru dziennika, ponieważ fragmentacja ta występuje najczęściej z powodu częstych wzrostów i spadków, ale mam pytania dotyczące tego, jak to naprawić fragmentacja fizyczna po zidentyfikowaniu:

• Po pierwsze, czy fragmentacja fizyczna jest nawet istotna w sieci SAN dla przedsiębiorstw? Czy mogę / powinienem używać Windows Defragmenter na dysku SAN, czy zespół SAN powinien używać wewnętrznych narzędzi do defragmentacji? Czy analiza fragmentacji, którą otrzymuję z narzędzia Windows, jest dokładna nawet po uruchomieniu na dysku SAN?

• Jak dużym problemem jest fizyczne rozdrobnienie wydajności SQL? (Załóżmy, że macierz napędów wewnętrznych oczekuje na wynik poprzedniego pytania.) Czy jest to WIĘKSZA transakcja niż wewnętrzna fragmentacja indeksu? Czy jest to naprawdę ten sam problem (napęd musi wykonywać losowe odczyty zamiast odczytów sekwencyjnych)

• Czy defragmentacja (lub odbudowa) indeksuje stratę czasu, jeśli dysk jest fizycznie rozdrobniony? Czy muszę to naprawić, zanim zwrócę się do drugiego?

• Jaki jest najlepszy sposób naprawy fizycznej fragmentacji plików na produkcyjnym polu SQL? Wiem, że mogę wyłączyć usługi SQL i uruchomić Windows Defrag, ale słyszałem także o technice polegającej na wykonaniu pełnej kopii zapasowej, upuszczeniu bazy danych, a następnie przywróceniu jej z pustego dysku. Czy ta ostatnia technika jest zalecana? Czy przywracanie z takiej kopii zapasowej również buduje indeksy od zera, eliminując wewnętrzną fragmentację indeksu? Czy może po prostu przywraca kolejność stron w taki sam sposób, jak w momencie tworzenia kopii zapasowej? (Jeśli to ważne, korzystamy z kopii zapasowych Quest Lightspeed z kompresją).

AKTUALIZACJA : Jak dotąd dobre odpowiedzi na pytanie, czy defragmentować dyski SAN (NIE) i czy defragmentacja indeksu jest nadal opłacalna na dyskach fizycznie pofragmentowanych (TAK).

Czy ktoś jeszcze zastanawia się nad najlepszymi metodami przeprowadzania defragmentacji? A może szacunkowy czas oczekiwania na defragmentację dużego pofragmentowanego dysku, powiedzmy 500 GB? Jest to oczywiście istotne, ponieważ właśnie wtedy mój serwer SQL przestanie działać!

Również jeśli ktoś ma jakieś niepotwierdzone informacje na temat ulepszeń wydajności SQL wprowadzonych przez naprawienie fragmentacji fizycznej, to też byłoby świetnie. W blogu Mike'a jest mowa o wykryciu problemu, ale nie jest on konkretny odnośnie tego, jaką poprawę wprowadził.

Myślę, że ten artykuł zawiera doskonały przegląd defragmentacji dysków SAN

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

Podstawową kwestią jest to, że defragmentacja nie jest zalecana w przypadku pamięci SAN, ponieważ trudno jest skorelować fizyczną lokalizację bloków na dysku, gdy lokalizacja została zwirtualizowana przez SAN podczas prezentacji jednostki LUN.

Jeśli korzystasz z mapowania urządzeń RAW lub masz bezpośredni dostęp do zestawu RAID, którym jest jednostka LUN, z którą pracujesz, zauważyłem, że degfragmentacja ma pozytywny efekt, ale jeśli otrzymasz „wirtualną” jednostkę LUN z udostępnionego RAID- 5 zestaw, nr.

Wiele części tego pytania i odpowiedzi:

Fragmentacja plików fizycznych nie ma tak naprawdę znaczenia dla pamięci Enterprise SAN, jak już zauważył Kevin - więc nie ma co tu dodawać. To naprawdę sprowadza się do podsystemu we / wy i tego, jak prawdopodobne jest, że będziesz w stanie sprawić, że dyski przejdą od bardziej losowych operacji we / wy podczas skanowania do bardziej sekwencyjnych operacji we / wy podczas skanowania. w przypadku DAS bardziej prawdopodobne jest, że w przypadku złożonej sieci SAN „slice-n-dice” prawdopodobnie nie.

Defragmentacja na poziomie systemu plików - rób to tylko przy wyłączonym SQL. Sam nigdy tutaj nie miałem problemów (ponieważ nigdy nie przeprowadzałem defragmentacji plików bazy danych SQL w trybie otwartym), ale słyszałem wiele niepotwierdzonych dowodów od klientów i klientów o dziwnych problemach z korupcją. Ogólna mądrość nie polega na robieniu tego z SQL online.

Fragmentacja indeksu jest całkowicie ortogonalna względem fragmentacji pliku. SQL Server nie ma pojęcia o fragmentacji plików - zbyt wiele warstw wirtualizacji pomiędzy nimi, aby mógł mieć jakąkolwiek nadzieję na wypracowanie rzeczywistych geometrii podsystemu we / wy. Fragmentacja indeksu jednak SQL wie wszystko. Bez powtarzania się zbyt wiele z odpowiedzi, do której już się odwoływałeś, fragmentacja indeksów uniemożliwi SQL wykonanie efektywnego ponownego skanowania głowy, niezależnie od tego, jak fragmentaryczne (czy nie) są pliki na poziomie systemu plików. Tak więc - absolutnie powinieneś ograniczyć fragmentację indeksu, jeśli widzisz spadek wydajności zapytań.

Nie musisz tego robić w określonej kolejności, chociaż jeśli zajmiesz się fragmentacją systemu plików, a następnie odbudujesz wszystkie swoje indeksy i spowodujesz większą fragmentację systemu plików, powiększając wiele plików na defragmentowanym woluminie, prawdopodobnie być odznaczonym. Czy spowoduje to jakieś problemy z perfem? Jak omówiono powyżej, zależy to :-D

Mam nadzieję że to pomoże!

Jaki jest najlepszy sposób naprawy fizycznej fragmentacji plików na produkcyjnym polu SQL?

Na plikach bazy danych uruchamiam contig SYSINTERNALS.

Zobacz http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx

Polecam odpowiednio zmienić rozmiar bazy danych, wyłączając serwer SQL, skopiuj plik bazy danych do innej macierzy dyskowej, a następnie skopiuj go z powrotem, aby go zdefragmentować. O wiele szybsze niż używanie defragmentacji systemu Windows.

Próbowałem raz zdefragmentować dyski fizyczne w rozwiązaniu SCSI, ale poprawa wydajności była niewielka lub wcale. Lekcja, której się nauczyłem, jest taka, że ​​jeśli doświadczasz niskiej wydajności z powodu systemu dyskowego, nie ma to nic wspólnego z fragmentacją, o ile mówimy o pliku danych, ponieważ korzysta on z dostępu losowego.

Jeśli Twoje indeksy są zdefragmentowane, a statystyki zaktualizowane (bardzo ważne) i nadal widzisz we / wy jako wąskie gardło, to cierpisz z powodu innych rzeczy niż fragmentacja fizyczna. Czy wykorzystałeś ponad 80% dysku? Czy masz dość dysków? Czy Twoje zapytania są wystarczająco zoptymalizowane? Czy wykonujesz dużo skanowania tabeli, czy jeszcze gorzej, dużo wyszukiwania indeksów, a następnie wyszukiwania indeksów klastrowych? Przejrzyj plany zapytań i użyj „ustaw statystyki io”, aby dowiedzieć się, co naprawdę dzieje się z zapytaniem. (poszukaj dużej liczby odczytów logicznych lub fizycznych)

Daj mi znać, jeśli całkowicie się mylę.

/ Håkan Winther

Może indeksy nie są wystarczająco zoptymalizowane dla Twojej aplikacji i nie masz Veritas I3 do optymalizacji bazy danych, możesz użyć takiej instrukcji, aby znaleźć brakujące indeksy:

       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 ;

Lub taka instrukcja, aby znaleźć indeksy, które nie są używane w instrukcjach select i zmniejszają wydajność aktualizacji / wstawiania:

    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

Mam kilka innych instrukcji SQL, których używam podczas analizy problemów z wydajnością w środowisku produkcyjnym, ale myślę, że te dwa są dobrym początkiem.

(Wiem, ten post jest trochę tematem, ale pomyślałem, że możesz być zainteresowany, ponieważ ma to związek ze strategią indeksowania)

/ Håkan Winther