none
Performance do banco SQL 2005 RRS feed

  • Pergunta

  • Amigos,

     

    ao executar uma consulta o banco utiliza a temdb para manipular os dados. Certo? Porém quanto vou ver o tamanho do banco está muito maior do que o previsto, pois está tabela pelo que parece ocupou o espaço em disco mais ao termino da consulta ela não foi compactada. Como deixamos esse procedimento de forma automatica, pois só consigo voltar com o espaço em disco despois de reiniciar a máquina.

     

    Outro ponto é o serviço sqlservr.exe quando termina a mesma consulta ele está ocupando memoria a vera.

     

    Poderiam ajudar?

    sábado, 19 de abril de 2008 14:51

Respostas

  • Olá Daniel,

     

    Precisamos acordar algumas definições para que você compreenda o que acontece. Uma coisa é o tamanho real, outra coisa é o tamanho alocado.

     

    Quando criamos um banco de dados definimos um tamanho para ele (digamos 1MB). Com o tempo, vários INSERTs irão tornar o tamanho desse banco bem maior (digamos 10GB). As operações de INSERT irão gerar novos dados e o banco não tem escolha senão crescer (respeitando as configurações de GROWTH e MAXSIZE que por padrão irão deixar o banco crescer indefinidamente).

     

    Após excluirmos dados do nosso banco de 10GB (digamos 30% dos dados) se verificarmos o tamanho do banco continua em 10GB. Se excluímos 30% dos dados o tamanho correto seria 7GB certo ? Por que então ficamos com 10GB ? Eis a diferença entre tamanho real e tamanho alocado. O tamanho real será 7GB e o tamanho alocado será 10GB. De fato os dados "realmente" ocupam agora 7GB, mas o SQL Server "aloca" o tamanho de 10GB que deve ser o tamanho a qual você se refere.

     

    Você deve estar imaginando por que então o SQL Server não reduz automaticamente o tamanho para 7GB ? Assim sobraria o espaço de 3GB. Bom eu diria que o SQL Server não faz isso por duas razões.

     

    A primeira razão é que trabalhar com arquivos é uma operação que exige I/O e como bem sabemos o I/O é tipicamente o componente mais lento de um sistema. Dessa forma se o SQL Server puder economizar operações de I/O ele o fará.

     

    A segunda razão é que um banco de dados típico cresce. Dessa forma, mesmo que haja espaço, seria um desperdício reduzir o espaço sendo que futuramente quando o banco de dados crescer, mais espaço será necessário. Iríamos gastar tempo e recursos reduzindo o tamanho do banco de dados e aumentando-o posteriormente.

     

    Se eu ainda não o tiver convencido, você poderá argumentar que precisa fazer a redução do tamanho seja para liberar espaço em disco, ou seja, para que na hora de restaurar um backup o arquivo não fique gigante. É bem verdade que essas são boas razões para aproximar o tamanho alocado do tamanho real, mas nesse caso há um solução manual para tal. Use o comando DBCC SHRINKFILE e o mesmo será reduzido. Ex:

     

    Code Snippet

    USE <BANCO>

    GO

    DBCC SHRINKFILE(<Nome ou Numero do Arquivo Logico>,<Tamanho MB desejável>)

     

     

    Onde será o seu banco de dados. Para saber o nome ou o número do arquivo lógico, rode a procedure sp_helpdb ''. Se o seu banco tiver um MDF e um LDF basta usar o exemplo abaixo (reduz o arquivo de dados para 1GB do banco Northwind):

     

    Code Snippet

    USE Financas

    GO

    DBCC SHRINKFILE(1,1024)

     

     

    Se ainda assim o seu banco continuar grande, o log deve estar gigante (afinal excluir 4GB gera muito log). Nesse caso, faça um backup de log e execute o mesmo comando no arquivo de LOG.

     

    [ ]s,

     

    Gustavo

    quinta-feira, 24 de abril de 2008 13:39

Todas as Respostas

  • Olá Daniel,

     

    A preocupação é válida mas há alguns equívocos. O banco não utiliza o TempDB para manipular os dados. Ele o faz recuperando os dados dos arquivos de dados e os coloca em memória para utilização (isso se os dados já não estiverem em memória). Há algumas ocasiões onde o TempDB pode ser utilizado mas elas são mais exceções do que regra.

     

    Se o TempDB está ocupando mais espaço que o previsto é preciso verificar se as atividades nesse banco estão sendo feitas corretamente. Ex:

     

    - Evite as tabelas temporárias, se utilizá-las lembre-se de dropá-las o mais rápido possível

    - Evite colocar tabelas temporárias dentro de transações

    - Se sua consulta trouxer grandes conjuntos de resultados opte por ordenar o resultado na aplicação

     

    Existem N outras recomendações sobre o TempDB e esse assunto já foi extensamente discutido nos demais fóruns. Dê uma olhada nos posts antigos sobre o TempDB

     

    Sobre a utilização de memória é normal e que bom que funciona dessa forma. Quanto mais os dados estiverem na memória menos o SQL Server terá de ir ao disco buscá-los. Então é normal ele usar a memória para manter os planos de execução bem como os dados de uma consulta.

     

    [ ]s,

     

    Gustavo

     

    sábado, 19 de abril de 2008 19:10
  • Gustavo,

     

    resolvi reabrir o post pois estou com uma dúvida bem parecida com o banco TempDB. Quando eu reinicio o serviço do sqlserver o banco do TempDB volta ao tamnho normal, porém o banco que eu criei após excluir algumas tabelas bem grandes tipo 4 giga o meu banco não volta ao tamanho origianl.

     

    Poderia me ajudar?

    quinta-feira, 24 de abril de 2008 12:51
  • DrAlves,

     

    Vamos lá, espero conseguir dar uma força.

     

    O seu banco não volta ao tamanho original!!!

     

    É isso?

     

    Atualize as informações sobre o espaço utilizando no banco, através da sp_updatestast.

    quinta-feira, 24 de abril de 2008 13:08
  • É isso mesmo ela não volta ao tamanho original. Após eu ter excluído o banco.

    quinta-feira, 24 de abril de 2008 13:36
  • Olá Daniel,

     

    Precisamos acordar algumas definições para que você compreenda o que acontece. Uma coisa é o tamanho real, outra coisa é o tamanho alocado.

     

    Quando criamos um banco de dados definimos um tamanho para ele (digamos 1MB). Com o tempo, vários INSERTs irão tornar o tamanho desse banco bem maior (digamos 10GB). As operações de INSERT irão gerar novos dados e o banco não tem escolha senão crescer (respeitando as configurações de GROWTH e MAXSIZE que por padrão irão deixar o banco crescer indefinidamente).

     

    Após excluirmos dados do nosso banco de 10GB (digamos 30% dos dados) se verificarmos o tamanho do banco continua em 10GB. Se excluímos 30% dos dados o tamanho correto seria 7GB certo ? Por que então ficamos com 10GB ? Eis a diferença entre tamanho real e tamanho alocado. O tamanho real será 7GB e o tamanho alocado será 10GB. De fato os dados "realmente" ocupam agora 7GB, mas o SQL Server "aloca" o tamanho de 10GB que deve ser o tamanho a qual você se refere.

     

    Você deve estar imaginando por que então o SQL Server não reduz automaticamente o tamanho para 7GB ? Assim sobraria o espaço de 3GB. Bom eu diria que o SQL Server não faz isso por duas razões.

     

    A primeira razão é que trabalhar com arquivos é uma operação que exige I/O e como bem sabemos o I/O é tipicamente o componente mais lento de um sistema. Dessa forma se o SQL Server puder economizar operações de I/O ele o fará.

     

    A segunda razão é que um banco de dados típico cresce. Dessa forma, mesmo que haja espaço, seria um desperdício reduzir o espaço sendo que futuramente quando o banco de dados crescer, mais espaço será necessário. Iríamos gastar tempo e recursos reduzindo o tamanho do banco de dados e aumentando-o posteriormente.

     

    Se eu ainda não o tiver convencido, você poderá argumentar que precisa fazer a redução do tamanho seja para liberar espaço em disco, ou seja, para que na hora de restaurar um backup o arquivo não fique gigante. É bem verdade que essas são boas razões para aproximar o tamanho alocado do tamanho real, mas nesse caso há um solução manual para tal. Use o comando DBCC SHRINKFILE e o mesmo será reduzido. Ex:

     

    Code Snippet

    USE <BANCO>

    GO

    DBCC SHRINKFILE(<Nome ou Numero do Arquivo Logico>,<Tamanho MB desejável>)

     

     

    Onde será o seu banco de dados. Para saber o nome ou o número do arquivo lógico, rode a procedure sp_helpdb ''. Se o seu banco tiver um MDF e um LDF basta usar o exemplo abaixo (reduz o arquivo de dados para 1GB do banco Northwind):

     

    Code Snippet

    USE Financas

    GO

    DBCC SHRINKFILE(1,1024)

     

     

    Se ainda assim o seu banco continuar grande, o log deve estar gigante (afinal excluir 4GB gera muito log). Nesse caso, faça um backup de log e execute o mesmo comando no arquivo de LOG.

     

    [ ]s,

     

    Gustavo

    quinta-feira, 24 de abril de 2008 13:39
  • ja tentou forcar um srhinkfile no tempdb ?

     

    Abs;

    quinta-feira, 24 de abril de 2008 13:40
  • Colla,

     

    Não consegui entender o que o DrAlves, esta querendo!!!

     

    Ele deseja diminuir o tamanho do banco de dados físicamente ou do espaço alocado no banco?

    quinta-feira, 24 de abril de 2008 13:47
  • Gustavo,

     

    mais uma vez uma excepcional resposta mais uma vez... Bom entendi tudo mais eu estou utilizando o SQL 2005 não teria uma outra maneira para realizar essa compctação? Pelo que pude ver quando clico com o direito em cima do banco que eu quero realizar o serviço vou na opção TASK e seleciono a opção SHINK onde possui duas opções DataBase/Files. Qual deles devo usar e como operar?

     

    Desde já agradeço.

    quinta-feira, 24 de abril de 2008 13:51
  • acho que o arquivo fisico mesmo, pelo que eu etendi era o problema da tempdb, o gustavo falou para fazer backup de log na tempdb mais ela e simple nao rola.... vamos ver se ele explica um pouco melhor.

     

    Abs;

     

    quinta-feira, 24 de abril de 2008 13:52
  • DrAlves,

     

    A opção Database, esta a compactação o banco de dados lógicamente liberando espaços em branco não utilizados, especificamente o banco de dados selecionado. O processo de shrink database esta relacionado com alguns objetos existentes dentro de um banco de dados, entre eles: Tables, Index e Views Indexadas.

     

    Já a File, esta relacionada com os arquivos de dados existentes para o banco de dados selecionado, fazendo também a diminuição física do tamanho destes arquivos, reduzindo o espaço ocupado em disco, e eliminando os espaços em branco não utilizando dentros dos arquivos de dados.

     

    Particularmente, eu gosto de após realizar este tipo de procedimento, atualizar as estatísticas tanto do banco de dados, como também das tables e index existentes.

     

     

    quinta-feira, 24 de abril de 2008 14:03
  • Oi Daniel,

     

    Estou vendo que estamos todos começando a nos confundir. A explicação que passei é válida para bancos de dados de negócio. Não que o raciocínio não sirva para o TEMPDB, mas é que no caso do TempDB, a diminuição de tamanho passa por outros passos.

     

    O problema é com o seu banco ou com o TempDB ? Se for com o seu banco, minha explicação continua válida. Se for com o TempDB, há mais coisa a acrescentar

     

    A opção de SHRINK DATABASE irá compactar todo os arquivos de dados e de log retirando o espaço alocado e não utilizado enquanto a opção de FILE fará isso arquivo por arquivo. Pode parecer interessante fazer o SHRINK DATABASE direto, mas muitas vezes é desejável (e mais performático) a redução arquivo a arquivo. Pode ser interessante reduzir o LOG e não reduzir o arquivo de dados por exemplo. Esse cenário é típico se você estiver esperando uma nova carga.

     

    [ ]s,

     

    Gustavo

     

    quinta-feira, 24 de abril de 2008 14:28
  • Junior,

     

    obrigado pela explicação, entendi. Porém não como faço esse procedimento de estatística e efetivamente para que serve?

    quinta-feira, 24 de abril de 2008 14:37
  • Oi Daniel,

     

    O procedimento de atualização de índices e estatísticas é importante, mas acho que deveríamos nos concentrar no seu problema (isso pode ser discutido em um outro post). Você conseguiu resolvê-lo (seja ele qual for) ?

     

    [ ]s,

     

    Gustavo

     

    quinta-feira, 24 de abril de 2008 14:55
  • DrAlves,

     

    As estatísticas representam um conjunto de informações sobre como esta a organização dos dados dentro da tables, fragmentação, porcentagem de utilização dos índices, entre outras coisas.

     

    Através deste conjunto o SQL Server trabalha de algumas maneiras para fazer a otimização das consultas, pois o plano de execução que é processado sobre uma transação também utiliza estes dados para melhorar ainda mais a performance durante a execução de algum comando ou bloco de transações.

     

    quinta-feira, 24 de abril de 2008 14:57
  • Gustavo,

     

    O problema já foi resolvido e estava relacionado ao banco que eu criei. Com relação a diferença de tratamento entre o meu banco e o TempDB já foi compreendida por mim.

     

    Desde já agradeço aos amigos.

    quinta-feira, 24 de abril de 2008 16:14
  • Amigos,

     

    ficou uma coisa pendente como faço para realizar esse procedimento de atualização das estatísticas.

    quinta-feira, 24 de abril de 2008 16:17
  • Daniel,

     

    Consulte o comando UPDATE STATISTICS no Books OnLine para melhor entendimento. Vale a pena lembrar que quando você reconstrói os índices clustered essas estatísticas já são atualizadas.

     

    [ ]s,

     

    Gustavo

     

    quinta-feira, 24 de abril de 2008 16:25