Olá pessoal…

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

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.

Tags: Banco de DadosSQL Server