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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 | /* 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áveisDeclare @Tabelas table (Tabela varchar(max))Declare @cNomTab as varchar(max)Declare @cString as nvarchar(max)Declare @dInicio as datetimeDeclare @nTabelas as integer --Deixando a base em usoUse Base_de_Dados --Mostrando log de inícioPrint('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 inicioSet @dInicio = GetDate() --Pegando as tabelas da baseSelect @nTabelas = Count(name) From Base_de_Dados.dbo.sysobjectsWhere xtype = 'U' and category = 0 --Inserindo na tabela temporáriaInsert Into @Tabelas (Tabela)Select name From Base_de_Dados.dbo.sysobjectsWhere xtype = 'U' and category = 0Order By name --Declarando o cursorDeclare csTab Cursor For Select Tabela From @Tabelas Order By TabelaOpen csTabFetch Next From csTab Into @cNomTab --Enquanto houver tabelasWhile @@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 @cNomTabEnd Close csTabDeallocate csTab --Mostrando mensagem de quantas tabelas foram processadasPrint('===============================================================================================================================================================================================')Print('A manutenção foi realizada em ' + Ltrim(Rtrim(Str(@nTabelas))) + ' tabela(s) do Banco de Dados Base_de_Dados')Print('===============================================================================================================================================================================================')Print('') --Finalizando o scriptPrint('===============================================================================================================================================================================================')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 tempoPrint('===============================================================================================================================================================================================')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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 | //Bibliotecas#Include "TOTVS.ch"#Include "TopConn.ch"/*/{Protheus.doc} User Function zIndexDBFunçã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...")ReturnStatic 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)ReturnStatic 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 NextReturnStatic 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.
Atilhão como sempre mandando muito bem!!!
Parabéns meu amigo!!!
Valeu mano Gui.
É nozes brother.
Aquele abraço.
Excelente artigo! Só uma dúvida, de quanto em quanto tempo é bom rodar esse script no banco?
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.
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
Opa, obrigado pelo feedback jovem.
Então, no caso, veja o trecho das linhas 32 a 36, é nesse ponto que ele monta uma temporária para percorrer, talvez você terá que adaptar o where em alguma forma, mas realmente não tenho ideia de como =/
Bom dia,
Eu sempre “passo” por aqui quando estou pesquisando alguma informação relacionada à Protheus/Advpl e sempre encontro informações relevantes que ajudam.
Gostaria de agradecer a parabanizar pelo trabalho.
Muito obrigado!
Bom dia Jorez, tudo joia?
Que isso, nós que agradecemos pelo feedback, é muita bondade e generosidade sua.
Tenha um ótimo e abençoado fim de semana.
Um grande abraço.