Script para manutenção do SQL Server

Olá pessoal…

Trago hoje um script de manutenção para o SQL Server, com intuito de otimizar o Banco de Dados.

SQL Server

SQL Server

Primeiramente gostaria de agradecer ao Ednilson Amarante, vulgo A Lenda, por disponibilizar esse script para ajudar.

Agora como ele funciona, basicamente, pense da seguinte forma, você para ir até a padaria, passa por 12 quadras e sempre você pega o mesmo caminho, porém com o passar do tempo, a avenida entre você e a padaria, começou a ficar extremamente congestionada, além de várias ruas mudarem para se tornar de mão única.

Assim também é um banco de dados, quando ele faz a consulta em uma tabela, supondo que a tabela tenha 10 registros, ele vai criar um “mapa” para buscar esses dados, mas conforme a tabela vai aumentando, esse mapa precisa ser revisto, e muitas das vezes não atualizamos o mapa, limpamos o caminho para o banco.

Essa consulta serve para otimizar e limpar esses gargalos das consultas. Abaixo o código.

Obs.: Substitua Base_de_Dados pelo nome da sua base.

/*
 Script criado por Ednilson Amarante,
 Adaptado por Daniel Atilio.
 
 Deve-se alterar onde está [Base_de_Dados] pelo nome da sua base
 
 Obs.: O ideal é executar a consulta com a saída como texto
 */
 
--Declarando variáveis
Declare @Tabelas table (Tabela varchar(max))
Declare @cNomTab as varchar(max)
Declare @cString as nvarchar(max)
Declare @dInicio as datetime
Declare @nTabelas as integer
 
--Deixando a base em uso
Use Base_de_Dados
 
--Mostrando log de início
Print('Manutenção do Banco de Dados Base_de_Dados iniciada em: ' + Convert(varchar(10),GetDate(),103)) + ' ' + Convert(varchar(10),GetDate(),108)
Print('===============================================================================================================================================================================================')
 
--Definindo a data de inicio
Set @dInicio = GetDate()
 
--Pegando as tabelas da base
Select @nTabelas = Count(name) From Base_de_Dados.dbo.sysobjects
Where xtype = 'U' and category = 0 
 
--Inserindo na tabela temporária
Insert Into @Tabelas (Tabela)
Select name From Base_de_Dados.dbo.sysobjects
Where xtype = 'U' and category = 0
Order By name
 
--Declarando o cursor
Declare csTab Cursor For Select Tabela From @Tabelas Order By Tabela
Open csTab
Fetch Next From csTab Into @cNomTab
 
--Enquanto houver tabelas
While @@Fetch_Status = 0 Begin
 
    --Desfragmentando índices
    Set @cString = 'DBCC INDEXDEFRAG(Base_de_Dados,' + @cNomTab + ')'
    Print('===============================================================================================================================================================================================')
    Print('Desfragmentando índices da tabela Base_de_Dados.dbo.' + @cNomTab + '...')
    Exec sp_executesql @cString
    Print('===============================================================================================================================================================================================')
    Print('')
 
    --Atualizando estatísticas
    Set @cString = 'UPDATE STATISTICS Base_de_Dados.dbo.' + @cNomTab
    Print('===============================================================================================================================================================================================')
    Print('Atualizando estatísticas da tabela Base_de_Dados.dbo.' + @cNomTab + '...')
    Exec sp_executesql @cString
    Print('===============================================================================================================================================================================================')
    Print('')
 
    --Marcando tabela para recompilação
    Print('===============================================================================================================================================================================================')
    Set @cString = 'Exec sp_recompile ''Base_de_Dados.dbo.' + @cNomTab + ''''
    Print('Marcando a tabela Base_de_Dados.dbo.' + @cNomTab + ' para recompilação...')
    Exec sp_executesql @cString
    Print('===============================================================================================================================================================================================')
    Print('')
 
Fetch Next From csTab Into @cNomTab
End
 
Close csTab
Deallocate csTab
 
--Mostrando mensagem de quantas tabelas foram processadas
Print('===============================================================================================================================================================================================')
Print('A manutenção foi realizada em ' + Ltrim(Rtrim(Str(@nTabelas))) + ' tabela(s) do Banco de Dados Base_de_Dados')
Print('===============================================================================================================================================================================================')
Print('')
 
--Finalizando o script
Print('===============================================================================================================================================================================================')
Print('Manutenção do Banco de Dados Base_de_Dados terminada em: ' + Convert(varchar(10),GetDate(),103)) + ' ' + Convert(varchar(10),GetDate(),108)
Print('===============================================================================================================================================================================================')
Print('')
 
--Mostrando a diferença de tempo
Print('===============================================================================================================================================================================================')
Print('Tempo utilizado para a manutenção do Banco de Dados Base_de_Dados: ' + Convert(varchar(12),GetDate() - @dInicio,114))
Print('===============================================================================================================================================================================================')

Update 30/07/2021:

Pessoal, recentemente um cliente que atende TCloud solicitou para que o script fosse adaptado, pois como não tem acesso ao banco de dados, precisamos criar uma função em AdvPL. Segue abaixo:

//Bibliotecas
#Include "TOTVS.ch"
#Include "TopConn.ch"

/*/{Protheus.doc} User Function zIndexDB
Função para refazer as estatísticas de tabelas do Banco de Dados
@type  Function
@author Atilio
@since 22/07/2021
@version version
@obs Script criado por Ednilson Amarante, adaptado por Daniel Atilio, link original abaixo:
@see https://terminaldeinformacao.com/2017/08/02/script-para-manutencao-sql-server/
/*/

User Function zIndexDB()
    //Se a SX2 não estiver aberta, prepara o ambiente (para poder chamar direto no programa inicial ou via job)
    If Select("SX2") == 0
        RPCSetEnv("01", "0101", "", "", "")
    EndIf

    //Exclui os arquivos de log
    Processa({|| fEraseLog() }, "Deletando logs anteriores...")

    //Chama o processamento para refazer os dados do banco SQL Server
    Processa({|| fProcess() }, "Analisando dados do banco de dados...")
Return

Static Function fProcess()
    Local aArea       := GetArea()
    Local cBaseDB     := fAliasDB()
    Local cQryTables  := ""
    Local nTotal      := 0
    Local nCurrent    := 0
    Local cTable      := ""
    Local cQryUpd     := ""
    Local nRetUpd     := 0
    Local cDirLog     := "\x_log_index_db\"
    Local cDirTmp     := GetTempPath()
    Local cFileUpd    := ""
    Local cFileLog    := "zIndexDB_" + dToS(Date()) + "_" + StrTran(Time(), ":", "-") + ".txt"
    Local oFWriter

    //Se a pasta de log não existir, cria ela
    If ! ExistDir(cDirLog)
        MakeDir(cDirLog)
    EndIf

    //Cria o arquivo de log
    oFWriter := FWFileWriter():New(cDirLog + cFileLog, .T.)
    oFWriter:Create()
    oFWriter:Write("Rotina iniciada - " + dToC(Date()) + " " + Time() + CRLF)

    //Busca todas as tabelas do SQL Server
    //  Se houver algum erro na query, é só descomentar o IN abaixo e colocar as tabelas que deram falha
    cQryTables := " SELECT name FROM " + cBaseDB + ".dbo.sysobjects " + CRLF
    cQryTables += " WHERE xtype = 'U' AND category = 0 " + CRLF
    //cQryTables += " AND name>='V6T010' " + CRLF
    cQryTables += " ORDER BY name ASC " + CRLF
    TCQuery cQryTables New Alias "QRY_TAB"

    //Pega o total de registros
    Count To nTotal
    ProcRegua(nTotal)
    QRY_TAB->(DbGoTop())

    //Enquanto houver dados
    While ! QRY_TAB->(EoF())
        //Define a tabela atual em uso
        cTable := Alltrim(QRY_TAB->NAME)

        //Incrementa a régua
        nCurrent++
        IncProc("Processando tabela " + SubStr(cTable, 1, 10) + " (registro " + cValToChar(nCurrent) + " de " + cValToChar(nTotal) + ").")

        //Desfragmentando índices da tabela
        cQryUpd := "DBCC INDEXDEFRAG(" + cBaseDB + "," + cTable + ")"
        nRetUpd := TCSQLExec(cQryUpd)

        //Se houve erro, gera na pasta de log, e pula o registro
        If nRetUpd != 0
            cFileUpd := cTable + "_indexdefrag_" + dToS(Date()) + "_" + StrTran(Time(), ':', '-') + ".txt"
            MemoWrite(cDirLog + cFileUpd, TCSQLError())
            oFWriter:Write("[" + cValToChar(nCurrent) + " de " + cValToChar(nTotal) + "] Tabela '" + cTable + "' houve um erro no indexdefrag, arquivo de log '" + cFileUpd + "' - " + dToC(Date()) + " " + Time() + CRLF)

            QRY_TAB->(DbSkip())
            Loop
	    EndIf

        //Atualizando estatísticas da tabela
        cQryUpd := "UPDATE STATISTICS " + cBaseDB + ".dbo." + cTable
        nRetUpd := TCSQLExec(cQryUpd)

        //Se houve erro, gera na pasta de log, e pula o registro
        If nRetUpd != 0
            cFileUpd := cTable + "_statistics_" + dToS(Date()) + "_" + StrTran(Time(), ':', '-') + ".txt"
            MemoWrite(cDirLog + cFileUpd, TCSQLError())
            oFWriter:Write("[" + cValToChar(nCurrent) + " de " + cValToChar(nTotal) + "] Tabela '" + cTable + "' houve um erro no statistics, arquivo de log '" + cFileUpd + "' - " + dToC(Date()) + " " + Time() + CRLF)

            QRY_TAB->(DbSkip())
            Loop
	    EndIf

        //Marcando tabela para recompilação
        cQryUpd := "Exec sp_recompile '" + cBaseDB + ".dbo." + cTable + "' "
        nRetUpd := TCSQLExec(cQryUpd)

        //Se houve erro, gera na pasta de log, e pula o registro
        If nRetUpd != 0
            cFileUpd := cTable + "_recompile_" + dToS(Date()) + "_" + StrTran(Time(), ':', '-') + ".txt"
            MemoWrite(cDirLog + cFileUpd, TCSQLError())
            oFWriter:Write("[" + cValToChar(nCurrent) + " de " + cValToChar(nTotal) + "] Tabela '" + cTable + "' houve um erro no recompile, arquivo de log '" + cFileUpd + "' - " + dToC(Date()) + " " + Time() + CRLF)

            QRY_TAB->(DbSkip())
            Loop
	    EndIf

        oFWriter:Write("[" + cValToChar(nCurrent) + " de " + cValToChar(nTotal) + "] Tabela '" + cTable + "' processada - " + dToC(Date()) + " " + Time() + CRLF)

        QRY_TAB->(DbSkip())
    EndDo
    QRY_TAB->(DbCloseArea())

    //Encerra o arquivo
    oFWriter:Write("Rotina encerrada - " + dToC(Date()) + " " + Time() + CRLF)
    oFWriter:Close()

    //Copia o log para máquina do usuário e abre o arquivo
    CpyS2T(cDirLog + cFileLog, cDirTmp)
    Sleep(3000)
    ShellExecute("OPEN", cFileLog, "", cDirTmp, 1)

    RestArea(aArea)
Return

Static Function fEraseLog()
    Local aFiles      := Directory("\x_log_index_db\*.*")
    Local nTotal      := Len(aFiles)
    Local nCurrent    := 0

    //Define o tamanho da régua
    ProcRegua(nTotal)

    //Percorre os arquivos e exclui
    For nCurrent := 1 To nTotal
        IncProc("Excluindo arquivo " + cValToChar(nCurrent) + " de " + cValToChar(nTotal) + "...")

        //Se o arquivo existir, apaga
        If File(aFiles[nCurrent][1])
            FErase(aFiles[nCurrent][1])
        EndIf
    Next
Return

Static Function fAliasDB()
    Local cIniFile := GetAdv97()
    Local cDBAlias := ""

    //Busca na seção [DbAccess]
    cDBAlias := GetPvProfString("DBAccess", "Alias", "ERRO", cIniFile )

    //Busca na seção [TopConnect]
    cDBAlias := GetPvProfString("TopConnect", "Alias", cDBAlias, cIniFile )

    //Busca na seção do [Environment] com a chave DB
    cDBAlias := GetSrvProfString("DBAlias", cDBAlias )

    //Busca na seção do [Environment] com a chave Top
    cDBAlias := GetSrvProfString("TopAlias", cDBAlias )
Return cDBAlias

Bom pessoal, por hoje é só.
Abraços e até a próxima.

Dan Atilio (Daniel Atilio)
Especialista em Engenharia de Software pela FIB. Entusiasta de soluções Open Source. E blogueiro nas horas vagas.

6 Responses

  1. Guilherme Lima disse:

    Atilhão como sempre mandando muito bem!!!
    Parabéns meu amigo!!!

  2. Thiago Santos disse:

    Excelente artigo! Só uma dúvida, de quanto em quanto tempo é bom rodar esse script no banco?

    • Dan_Atilio disse:

      Obrigado pelo feedback Thiago.
      Então, depende do tamanho da base do cliente que você for aplicar, já vi cenários em que agendamos uma vez por mês.
      Assim como também já vi casos de uma vez por semana.

  3. rahman13@gmail.com disse:

    Muito bom o script, ajudou muito já que uso um sistema antigo sem muito suporte do desenvolvedor.
    tive que criar uma tabelas manualmente pois o script não encontrou as tabelas e não existia mesmo no banco


    Desfragmentando índices da tabela Gerencial.dbo.Aliquotas…
    Mensagem 2501, Nível 16, Estado 45, Linha 1
    Não é possível encontrar uma tabela ou objeto com o nome “Aliquotas”. Verifique o catálogo do sistema.
    ===============================================================================================================================================================================================

    ===============================================================================================================================================================================================
    Atualizando estatísticas da tabela Gerencial.dbo.Aliquotas…
    Mensagem 2706, Nível 16, Estado 6, Linha 1
    A tabela ‘Aliquotas’ não existe.

    fui criando na mão, tem algum comando pra pular caso não ache a tabela e dar o aviso no final das tablelas não encontadas, ou só pular mesmo já que o sistema esta ok e não esta utilizando as tabelas não encontradas.

    Obrigado

Deixe uma resposta