Databáze > MS SQL

FYI: projev defragmentace indexu

(1/1)

pf1957:
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 ---SELECT        [Extent1].[Amount] AS [Amount],        [Extent1].[COUNT] AS [COUNT],        [Extent4].[DATE] AS [DATE],        [Extent4].[HOUR] AS [HOUR],        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],        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]        FROM     [dbo].[CashUnitBalanceSplits] AS [Extent1]        INNER JOIN [dbo].[CashNodeBalanceReports] AS [Extent2] ON [Extent1].[CashNodeBalanceReportId] = [Extent2].[Id]        INNER JOIN [dbo].[CashNodeConfigs] AS [Extent3] ON [Extent2].[CashNodeConfigId] = [Extent3].[Id]        INNER JOIN [dbo].[CashRawReports] AS [Extent4] ON [Extent2].[CashRawReportId] = [Extent4].[Id]        INNER JOIN [dbo].[CashNodes] AS [Extent5] ON [Extent3].[CashNodeId] = [Extent5].[Id]        WHERE ([Extent5].[IdentCode] = @p__linq__0) AND ([Extent4].[DATE] >= @p__linq__1) AND ([Extent4].[DATE] <= @p__linq__2) AND ([Extent1].[DenominationId] = @p__linq__3)    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.

pf1957:

--- Citace: pf1957  20-01-2018, 11:31:49 ---Jak jsem zjistil, tak pricinou byla neudrzovana DB, u ktere nebyl nastaven maintenance plan.

--- Konce citace ---
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 ---SELECT         dbschemas.[name] AS 'Schema',        dbtables.[name] AS 'Table',        dbindexes.[name] AS 'Index',        indexstats.avg_fragmentation_in_percent,        indexstats.page_countFROM   sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstatsINNER JOIN sys.TABLES dbtables   ON dbtables.[object_id] = indexstats.[object_id]INNER JOIN sys.schemas dbschemas   ON dbtables.[schema_id] = dbschemas.[schema_id]INNER JOIN sys.indexes AS dbindexes   ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_idWHERE   indexstats.database_id = DB_ID() AND indexstats.avg_fragmentation_in_percent > 3 AND indexstats.page_count > 500ORDER BY   [TABLE], [INDEX]; 

98711:
Verim ze by pomohl table nebo primo index partitioning ;)

pf1957:

--- Citace: Delfin  20-01-2018, 18:01:52 ---Verim ze by pomohl table nebo primo index partitioning ;)

--- Konce citace ---
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

Navigace

[0] Seznam témat