none
i log saturano 200 GB di disco per la riorganizzazione indici e la compattazione. RRS feed

  • Domanda

  • Ciao a tutti,

    Ho un DB di produzione i cui data file ammontano a circa 110 GB.
    il processore del server (4 core Opteron 2.4 GHz + 16 GB RAM) è costantemente tra il 60 e il 90 % di utilizzo. Ho subito pensato agli indici ed alla compattazione.
    comunque ho previsto di aggiungere altri 2 processori doppio core Opteron ed altra RAM fino ad arrivare a 64 Gigabytes...

     

    Ho appena finito un corso di SQL MS in cui mi hanno consigliato un piano di manutenzione giornaliero con riorganizzazione degli indici, aggiornamento statistiche e compattazione database senza rilascio dello spazio, in aggiunta a quanto già c'era: un piano settimanale con il rebuild completo, agg. statistiche e compattazione con rilascio dello spazio. La cosa mi garba alquanto, perchè penso che sia quello il motivo di una occupazione processore così alta.

     

    Il backup FULL lo faccio girare prima del piano, così dovrebbe azzerarmi i log. (ma non mi è chiaro se i log si shrinkano da soli oppure se è meglio fare un log shrinking

     

    Spremendo la SAN come un'arancia matura (agg.ti firmware ad attivo/attivo, ottimizzazioni, partition alignment, etc etc etc) sono riuscito a rendere il sottosistema dischi non più un collo di bottiglia. (le code disco sono mediamente tra i 2 e i 30, con picchi di attività sporadici di qualche secondo oltre gli 80)

    il recovery model è FULL

    Ho assegnato al disco dei logs ben 200 GB di spazio. Pensavo di non aver più problemi sullo spazio dei log, ma evidentemente mi sbagliavo... Ho sentito da qualche parte una frase, una volta: il disco dei log dovrebbe avere almeno il doppio dello spazio occupato dalla somma dei datafile.

     

    riassunto situazione fisico/logica:
    Cluster di HP DL585 G2 con 2 processori dual core 2.4 GHz Opteron, 16 GB RAM, SAN HP MSA1000 con 42 Dischi dedicata UNICAMENTE a SQL (doppio controller attivo/attivo e preferred path smistate), partizioni perfettamente allineate con i settori disco.

    DATABASE SQL 2008 su istanza clusterizzata, somma dei data files = 110 GB

     

     

    le mie domande:

    1. è normale che il log cresca così tanto?
    2. è bene mantenere il piano di manutenzione giornaliero?
    3. come posso evitare il riempimento del disco, mantenendo comunque il piano di manutenzione giornaliero?
    4. ci possono essere altri motivi per cui il processore è così occupato (il server è dedicato a SQL server ed è quello il processo che occupa)?
    5. altri consiglie per ottimizzare quell'infrastruttura?

     

    Mille grazie.

     


    Diego Castelli - MCSA 2003, MCP ISA 2004, MCTS Forefront. ITA: Questo post è fornito "così com'è". Non conferisce garanzie o diritti di alcun tipo. Ricorda di usare la funzione "segna come risposta" per i post che ti hanno aiutato a risolvere il problema e "deseleziona come risposta" quando le risposte segnate non sono effettivamente utili. Questo è particolarmente utile per altri utenti che leggono il thread, alla ricerca di soluzioni a problemi similari. ENG: This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    mercoledì 1 giugno 2011 09:09

Risposte

  • Ciao Diego,

    1) anche le operazioni di manutenzione vengono loggate e quindi determinano la crescita del file di log. Quando SQL Server reindicizza i dati deve modificare, spostare, generare nuove pagine nel database e tutte queste modifiche devono necessariamente essere loggate per poterle eventualmente riapplicare in caso di crash. Un possibile workaround potrebbe essere quello di mettere il db in SIMPLE prima di fare manutenzione, in modo che ad ogni checkpoint il log venga applicato ai dati e svuotato e successivamente rimetterlo in FULL, ma personalmente non è la soluzione che preferisco.
    Potresti sostituire il piano di manutenzione con lo script di Ola Hallengren che trovi qui. Funziona decisamente meglio e ti dà un maggior controllo.

    2) pesa sia MENTRE viene eseguita che DOPO: pensa ad esempio al log shipping o al database mirroring. In entrambi i casi il log viene riletto (sotto forma di backup nel primo caso, con l'apposito agente nel secondo) e riapplicato dall'altra parte. Senza contare la frammentazione fisica del file di log, soprattutto con l'impostazione di default del 10% (nel tuo caso 200MB->330MB->363MB->399MB etc etc etc). E per il file di log non è possibile usare la Instant File Initialization !

    3) Dubbio: ma al corso di amministrazione non vi hanno parlato delle strategie di backup/restore ?!?

    4) SQLNexus è un tool davvero prezioso per analizzare in forma grafica i dati restituiti dal profiler.

    Buon divertimento :-)

     


    Danilo Dominici MCP MCDBA MCITP MCSE MCAD ..::.. Questo post è fornito "così com'è". Non conferisce garanzie o diritti di alcun tipo. Ricorda di usare la funzione "segna come risposta" per i post che ti hanno aiutato a risolvere il problema e "deseleziona come risposta" quando le risposte segnate non sono effettivamente utili. Questo è particolarmente utile per altri utenti che leggono il thread, alla ricerca di soluzioni a problemi similari. ENG: This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Contrassegnato come risposta Diego Castelli lunedì 6 giugno 2011 09:56
    mercoledì 1 giugno 2011 16:05

Tutte le risposte

  • Ciao Diego,

    con il recovery model FULL o BULK-LOGGED l'unica operazione che svuota il transaction log è il backup del log. Ti rimando a questo post per le spiegazioni e l'esempio.

    Riguardo alle tue domande:

    1) dipende dalle transazioni che fai sul tuo database.

    2) si, per la parte di riorganizzazione indici e ricostruzione statistiche. Eviterei l'operazione di shrink se non assolutamente necessaria (hai finito lo spazio disco e non te ne sei accorto prima) perchè genera I/O sul disco, frammentazione a livello di file system, appesantisce la CPU, appesantisce il transaction log perchè registra tutto e quindi appesantisce il sistema se hai log shipping, mirroring o repliche attive. Insomma... non è una bella cosa.

    3) due soluzioni: backup del log o modello di recovery SIMPLE. Se non ho capito male il tuo piano di backup è composto di un solo backup full del database giornaliero. Potresti passare anche al SIMPLE, ma tieni presente che in caso di crash perderesti tutti i dati modificati dall'ultimo full al momento del guasto.

    4) Prova a registrarti una traccia con SQL Profiler per un periodo ragionevole di tempo ed analizzala con SQLNexus. Di solito i colli di bottiglia risultano abbastanza evidenti.

    Ciao!

    PS: in questo thread trovi qualche link ad info sul transaction log.


    Danilo Dominici MCP MCDBA MCITP MCSE MCAD ..::.. Questo post è fornito "così com'è". Non conferisce garanzie o diritti di alcun tipo. Ricorda di usare la funzione "segna come risposta" per i post che ti hanno aiutato a risolvere il problema e "deseleziona come risposta" quando le risposte segnate non sono effettivamente utili. Questo è particolarmente utile per altri utenti che leggono il thread, alla ricerca di soluzioni a problemi similari. ENG: This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    mercoledì 1 giugno 2011 10:07
  • Ciao Danilo,

     grazie per la tua risposta.

    Preciso alcune cose in modo che tu possa aiutarmi meglio, sempre riferendomi ai punti come sopra:

    1) prima del piano di manutenzione, i log risultano circa di 300 Megabytes. La crescita è tutta durante il piano di manutenzione (ho impostato delle trace con il performance monitor). E' questo che mi lascia perplesso.

    2) La parte di SHRINK appesantisce la CPU, RAM I/O etc etc etc SOLO mentre viene eseguita, mentre invece libera risorse, una volta finito. Sbaglio?

    3) la tua risposta è preziosa, infatti ho controllato ed il backup di tutti i giorni è solo FULL (lanciato con un t-SQL "BACKUP DATABASE"). Stiamo parlando di una compagnia assicurativa e quindi sicuramente faremo il backup dei logs.

    4) avevo lanciato il profiler per capire chi occupava così tanto processore, ma non ho saputo bene analizzare i dati. Forse con il tuo tool sarà più semplice!

     

    Ora proverò ad applicare i tuoi consigli e ti far sapere!
    Intanto grazie ancora.


    Diego Castelli - MCSA 2003, MCP ISA 2004, MCTS Forefront. ITA: Questo post è fornito "così com'è". Non conferisce garanzie o diritti di alcun tipo. Ricorda di usare la funzione "segna come risposta" per i post che ti hanno aiutato a risolvere il problema e "deseleziona come risposta" quando le risposte segnate non sono effettivamente utili. Questo è particolarmente utile per altri utenti che leggono il thread, alla ricerca di soluzioni a problemi similari. ENG: This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    mercoledì 1 giugno 2011 14:00
  • Ciao Diego,

    1) anche le operazioni di manutenzione vengono loggate e quindi determinano la crescita del file di log. Quando SQL Server reindicizza i dati deve modificare, spostare, generare nuove pagine nel database e tutte queste modifiche devono necessariamente essere loggate per poterle eventualmente riapplicare in caso di crash. Un possibile workaround potrebbe essere quello di mettere il db in SIMPLE prima di fare manutenzione, in modo che ad ogni checkpoint il log venga applicato ai dati e svuotato e successivamente rimetterlo in FULL, ma personalmente non è la soluzione che preferisco.
    Potresti sostituire il piano di manutenzione con lo script di Ola Hallengren che trovi qui. Funziona decisamente meglio e ti dà un maggior controllo.

    2) pesa sia MENTRE viene eseguita che DOPO: pensa ad esempio al log shipping o al database mirroring. In entrambi i casi il log viene riletto (sotto forma di backup nel primo caso, con l'apposito agente nel secondo) e riapplicato dall'altra parte. Senza contare la frammentazione fisica del file di log, soprattutto con l'impostazione di default del 10% (nel tuo caso 200MB->330MB->363MB->399MB etc etc etc). E per il file di log non è possibile usare la Instant File Initialization !

    3) Dubbio: ma al corso di amministrazione non vi hanno parlato delle strategie di backup/restore ?!?

    4) SQLNexus è un tool davvero prezioso per analizzare in forma grafica i dati restituiti dal profiler.

    Buon divertimento :-)

     


    Danilo Dominici MCP MCDBA MCITP MCSE MCAD ..::.. Questo post è fornito "così com'è". Non conferisce garanzie o diritti di alcun tipo. Ricorda di usare la funzione "segna come risposta" per i post che ti hanno aiutato a risolvere il problema e "deseleziona come risposta" quando le risposte segnate non sono effettivamente utili. Questo è particolarmente utile per altri utenti che leggono il thread, alla ricerca di soluzioni a problemi similari. ENG: This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Contrassegnato come risposta Diego Castelli lunedì 6 giugno 2011 09:56
    mercoledì 1 giugno 2011 16:05
  • 2) OK, capito.

     

    3) Si, ce ne hanno parlato ed è proprio per questo che sto rivedendo completamente la situazione. Ovviamente il database e tutti i piani di backup/restore/manutenzione sono stati fatti già da un po' (era SQL 2005).

     

     

    Grazie. Ciao!

     


    Diego Castelli - MCSA 2003, MCP ISA 2004, MCTS Forefront. ITA: Questo post è fornito "così com'è". Non conferisce garanzie o diritti di alcun tipo. Ricorda di usare la funzione "segna come risposta" per i post che ti hanno aiutato a risolvere il problema e "deseleziona come risposta" quando le risposte segnate non sono effettivamente utili. Questo è particolarmente utile per altri utenti che leggono il thread, alla ricerca di soluzioni a problemi similari. ENG: This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    lunedì 6 giugno 2011 09:56