FYI: projev defragmentace indexu

Autor Téma: FYI: projev defragmentace indexu  (Přečteno 550 krát)

Offline pf1957

  • Padawan
  • ******
  • Příspěvků: 2174
  • Karma: 116
    • Verze Delphi: D2007, XE3, DX10
FYI: projev defragmentace indexu
« kdy: 20-01-2018, 11:31:49 »
Excellent
Rated 1 time
Kdyz uz tady Radek nadnesl zvlastnost MSSQL, tak pridam taky jednu, kterou jsem resil pred 2 tydny a ktera me docela zarazila: vliv fragmentace indexu viz obrazek, ktery predstavuje graf s casy nejakeho zpracovani (tj. zahrnuje nekolik SQL operaci) za cca 1/2 roku ostreho 7x24 provozu aplikace.

Na skokovem zhorseni vykonu mel zasadni podil jeden opakovane spousteny, v principu jednoduchy select (generovany EF z LINQ):
Kód: SQL [Vybrat]
  1. SELECT
  2.         [Extent1].[Amount] AS [Amount],
  3.         [Extent1].[COUNT] AS [COUNT],
  4.         [Extent4].[DATE] AS [DATE],
  5.         [Extent4].[HOUR] AS [HOUR],
  6.         CASE WHEN (([Extent2].[HasAttendance] = 1) OR ([Extent2].[CashNodeRestockingReportId] IS NOT NULL)) THEN CAST(1 AS bit) WHEN ( NOT (([Extent2].[HasAttendance] = 1) OR ([Extent2].[CashNodeRestockingReportId] IS NOT NULL))) THEN CAST(0 AS bit) END AS [C1],
  7.         CASE WHEN ((([Extent1].[Amount] IS NOT NULL) OR ([Extent1].[COUNT] IS NOT NULL)) AND ([Extent2].[IsDispenseEmpty] <> CAST(1 AS bit)) AND ([Extent2].[IsDepositEmpty] <> CAST(1 AS bit)) AND ([Extent2].[IsDepositNull] <> CAST(1 AS bit))) THEN CAST(1 AS bit) WHEN ( NOT ((([Extent1].[Amount] IS NOT NULL) OR ([Extent1].[COUNT] IS NOT NULL)) AND ([Extent2].[IsDispenseEmpty] <> CAST(1 AS bit)) AND ([Extent2].[IsDepositEmpty] <> CAST(1 AS bit)) AND ([Extent2].[IsDepositNull] <> CAST(1 AS bit)))) THEN CAST(0 AS bit) END AS [C2]
  8.         FROM     [dbo].[CashUnitBalanceSplits] AS [Extent1]
  9.         INNER JOIN [dbo].[CashNodeBalanceReports] AS [Extent2] ON [Extent1].[CashNodeBalanceReportId] = [Extent2].[Id]
  10.         INNER JOIN [dbo].[CashNodeConfigs] AS [Extent3] ON [Extent2].[CashNodeConfigId] = [Extent3].[Id]
  11.         INNER JOIN [dbo].[CashRawReports] AS [Extent4] ON [Extent2].[CashRawReportId] = [Extent4].[Id]
  12.         INNER JOIN [dbo].[CashNodes] AS [Extent5] ON [Extent3].[CashNodeId] = [Extent5].[Id]
  13.         WHERE ([Extent5].[IdentCode] = @p__linq__0) AND ([Extent4].[DATE] >= @p__linq__1) AND ([Extent4].[DATE] <= @p__linq__2) AND ([Extent1].[DenominationId] = @p__linq__3)
  14.     ORDER BY [Extent4].[DATE] ASC, [Extent4].[HOUR] ASC

jehož čas se zvětšil ~70x (!). Primarni tabulka mela asi 2M5 zaznamu a kazdy den do ni pribyde asi 10k novych zaznamu. Ty ostatni jsou radove mensi. Jak jsem zjistil, tak pricinou byla neudrzovana DB, u ktere nebyl nastaven maintenance plan.

Co me prekvapilo, ze to za ten 1/2 rok nevadlo postupne, coz byva typicky projev problemu s indexaci, ale ze se to objevilo skokove - zrejme problem s vytecenim neceho z nejake vnitrni cache, protoze ta denne pribyvajici data budou roztrouseny po strankach jak skleroza.



Offline pf1957

  • Padawan
  • ******
  • Příspěvků: 2174
  • Karma: 116
    • Verze Delphi: D2007, XE3, DX10
Re:FYI: projev defragmentace indexu
« Odpověď #1 kdy: 20-01-2018, 14:08:04 »
Jak jsem zjistil, tak pricinou byla neudrzovana DB, u ktere nebyl nastaven maintenance plan.
Protoze tady budeme vetsinou involuntary administrators, tak jeste doplnim, ze jsem to opravil pomoci https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html a u MSSQL 2014 nastavil tydenni plan udrzby, ale jak ta udrzba bude fungovat to teprve vyhodnocuju https://docs.google.com/spreadsheets/d/1qSQ1B836pHcLmDUKI6NWcQnF_jJjG21AM_vAbh6mi-g/edit?usp=sharing.

Tabulka obsahuje seznam fragmentovanych indexu ziskanych selectem (MS doporucuje vsimat si indexu o velikosti pres 1000 stranek, ja je sleduju od 500):
Kód: SQL [Vybrat]
  1. SELECT
  2.         dbschemas.[name] AS 'Schema',
  3.         dbtables.[name] AS 'Table',
  4.         dbindexes.[name] AS 'Index',
  5.         indexstats.avg_fragmentation_in_percent,
  6.         indexstats.page_count
  7. FROM
  8.   sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
  9. INNER JOIN sys.TABLES dbtables
  10.   ON dbtables.[object_id] = indexstats.[object_id]
  11. INNER JOIN sys.schemas dbschemas
  12.   ON dbtables.[schema_id] = dbschemas.[schema_id]
  13. INNER JOIN sys.indexes AS dbindexes
  14.   ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id
  15. WHERE
  16.   indexstats.database_id = DB_ID() AND indexstats.avg_fragmentation_in_percent > 3 AND indexstats.page_count > 500
  17. ORDER BY
  18.   [TABLE], [INDEX];
  19.  

Offline Delfin

  • Guru
  • *****
  • Příspěvků: 1063
  • Karma: 52
  • SW konzultant
    • Verze Delphi: 2009, Tokyo
Re:FYI: projev defragmentace indexu
« Odpověď #2 kdy: 20-01-2018, 18:01:52 »
Verim ze by pomohl table nebo primo index partitioning ;)
I'm a soldier, so don't panic! I know the underground! I like WTFPL license! No more Google, go duck, go!

Offline pf1957

  • Padawan
  • ******
  • Příspěvků: 2174
  • Karma: 116
    • Verze Delphi: D2007, XE3, DX10
Re:FYI: projev defragmentace indexu
« Odpověď #3 kdy: 20-01-2018, 18:31:21 »
Verim ze by pomohl table nebo primo index partitioning ;)
No nevim:
- aplikace v podstate nema data k odkladani do archivu
- v udrzovane DB je rychlost operace OK.
- tech fragmentovanych indexech, co si kazde rano vypisuji, se vlastne ta kriticka data vubec nevyskytuji, protoze tabulky CashFlowHourAverageDerivatives a CashFlowPredictedValues maji v podstate konstanti velikost a CashNodeBalanceReports  obsahuje jen ~M4 zaznamu s prirustkem asi k4 denne
- Tim partitioningem bych se zabyval az v pripade, ze by to bylo pomale i u pravidlene udrzovane DB, protoze bychom museli upgradovat server na 2016, protoze 2014 podporovala partitioning jen v Entreprise edici
« Poslední změna: 20-01-2018, 19:03:58 od pf1957 »

 

S rychlou odpovědí můžete používat BB kódy a emotikony jako v běžném okně pro odpověď, ale daleko rychleji.

Upozornění: do tohoto tématu bylo naposledy přispěno před 120 dny.
Zvažte prosím založení nového tématu.

Jméno: E-mail:
Ověření:
Křestní jméno zpěváka Gotta: