none
Perfomance de Disco Partcionamento de Tabelas RRS feed

  • Pergunta

  • Pessoal, uma dúvida sobre particionamento: entendemos que particionar tabelas tem como um dos grandes benefícios o ganho de desempenho em execução de consultas realizadas em tabelas com grande quantidade de registros, porém tenho um ambiente, no qual os discos estão em RAID 10 particionado em 4 volumes, são eles:

    D:\Data – arquivos mdf do banco;

    L:\Log – arquivo ldf do log;

    B:\Backup – bak do backup;

    T:\TempDB – mdfs do Tempdb.

    Com esse mesmo cenário penso em pegar as maiores tabelas do banco para particionar e jogar no diretório volume D:\Data, mas separando apenas por pastas tipo Data_1,.....Data_n.

    Fazendo desta forma, jogando os filegroups ndf no mesmo diretório do mdf da base, estaria sendo imprudente com relação ao ganho pretendido de performance?

    Agradeço desde já!


    • Editado Jerfeson S. Barbosa quarta-feira, 20 de agosto de 2014 13:27 Modificação no Tópico
    quarta-feira, 20 de agosto de 2014 13:26

Respostas

  • Olá Jeferson,

    Dá uma conferida nesse whitepaper... Ele dá uma noção muito boa sobre particionamento.

    https://www.sqlskills.com/resources/whitepapers/partitioning%20in%20sql%20server%202005%20beta%20ii.htm#_Toc79339947

    Apesar de ser para o SQL2005, vai ajudar e muito..

    O mais importante e acredito que isso responda a sua pergunta é que o ideal é manter em discos separados e filegroups distintos também, pois isso vai auxiliar a performance caso contrário você vai obter um efeito inverso.

    Esse assunto foi discutido em outro tópico estou replicando uma resposta do Gustavo que pode nortear você um pouco.

    "Sim. Há desvantagens em utilizar o particionamento tanto no SQL Server 2005, quanto no 2008 quanto em qualquer outro banco de dados que trabalhe com particionamento. Quando você particiona uma tabela, você está fazendo com que o banco de dados introduza controles adicionais nas operações de gravação, já que é necessário que o registro seja mapeado para a partição correta. Mesmo durante a consulta, será necessário verificar qual será a partição que o registro deve estar e isso irá requer alguns passos extras. Para que isso realmente valha a pena, é imprescindível que você atenda os seguintes pré-requisitos:

    - Você já otimizou suas consultas
    - Você já tentou resolver o problema com o uso de indexação (incluindo cover queries)
    - Sua tabela é suficientemente volumosa (não me refiro somente a quantidade de registros, mas o tamanho da tabela que é o que conta.)
    - Suas consultas utilizam o valor do campo particionado durante as consultas (não adianta particionar por data, se você faz muitas consultas por CPF)

    Caso você não tenha obedecido a esses quatro pré-requisitos, o particionamento só lhe trará dores de cabeça. Já utilizei o particionamento em uma tabela de 2 milhões de registros e o desempenho ficou pior.

    Estou desconsiderando dessa minha resposta as vantagens natas do particionamento em relação à administração como reindexação, expurgo, etc

    [ ]s,
     
    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com"

    Além de outra observação do Pedro Galvão !!

    "representa mais uma cada de* acesso até o SQL Server encontrar o dado desejado.


    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA"

    * Acredito que seja camada de acesso



    Flávio Farias
    "May the Force be with you"
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    quarta-feira, 20 de agosto de 2014 13:58
  • Um dos grandes beneficios em Tabelas/Indices Particionados é em relação ao gerenciamento de grandes volumes de dados, e não ganho de desempenho.

    O ganho de desempenho pode ocorrer por alguns fatores, como por exemplo: uma consulta busca dados somente em uma partição ao invés da tabela toda, um Lock pode ser escalado para a partição e não para a tabela se for necessário, etc.

    Veja no link abaixo, sobre indices não-alinhados, pois esse é um dos piores vilões em particionamento.

    http://msdn.microsoft.com/pt-br/library/ms190787.aspx


    Alex Rosa - Premier Field Engineer - Data Platform

    Disclaimer: This content is provided "as-is" and without warranties of any kind, either express or implied. You should therefore verify any information contained in the content before acting on it.


    sexta-feira, 22 de agosto de 2014 02:16
  • Jeferson,

    Particionar uma ou mais tabelas em FILEGROUPs diferentes realmente vai ajudar a melhorar sua performance desde que cada FILEGROUP corresponda a um arquivo .NDF diferente.

    Em alguns casos, seria interessante você manter a tabela em um FILEGROUP (e seu índice clusterizado) e os índices não clusterizados em outro FILEGROUP (preferencialmente, incluíndo nos índices os campos utilizados pelo usuário para exibição). Veja um exemplo abaixo:

    CREATE NONCLUSTERED INDEX [IDX_ADM_CLI_FILIAL]
    ON [dbo].TB_PESSOA (CD_ADM,CD_CLIENTE,CD_FILIAL)
    INCLUDE (NM_PESSOA, CD_SEXO, NR_IDADE)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [IMPORTACAO] GO

    Mesmo que você mantenha os mesmo diretório, a diferença de performance poderá ser pequena se você não definir bem onde alocar cada tabela e seus respectivos índices. Faça um teste recriando alguns índices como te indiquei (preferencialmente, em um ambiente de testes isolado) e compare realizando um cenário de stress de manipulação de dados.

    Caso você esteja particionando os dados dentro de cada tabela, analise se o campo escolhido realmente é o ideal para você particionar os dados hoje e também para redimensionar este particionamento em novos FILEGROUPs, caso seja necessário no futuro. 

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    quarta-feira, 20 de agosto de 2014 14:21

Todas as Respostas

  • Olá Jeferson,

    Dá uma conferida nesse whitepaper... Ele dá uma noção muito boa sobre particionamento.

    https://www.sqlskills.com/resources/whitepapers/partitioning%20in%20sql%20server%202005%20beta%20ii.htm#_Toc79339947

    Apesar de ser para o SQL2005, vai ajudar e muito..

    O mais importante e acredito que isso responda a sua pergunta é que o ideal é manter em discos separados e filegroups distintos também, pois isso vai auxiliar a performance caso contrário você vai obter um efeito inverso.

    Esse assunto foi discutido em outro tópico estou replicando uma resposta do Gustavo que pode nortear você um pouco.

    "Sim. Há desvantagens em utilizar o particionamento tanto no SQL Server 2005, quanto no 2008 quanto em qualquer outro banco de dados que trabalhe com particionamento. Quando você particiona uma tabela, você está fazendo com que o banco de dados introduza controles adicionais nas operações de gravação, já que é necessário que o registro seja mapeado para a partição correta. Mesmo durante a consulta, será necessário verificar qual será a partição que o registro deve estar e isso irá requer alguns passos extras. Para que isso realmente valha a pena, é imprescindível que você atenda os seguintes pré-requisitos:

    - Você já otimizou suas consultas
    - Você já tentou resolver o problema com o uso de indexação (incluindo cover queries)
    - Sua tabela é suficientemente volumosa (não me refiro somente a quantidade de registros, mas o tamanho da tabela que é o que conta.)
    - Suas consultas utilizam o valor do campo particionado durante as consultas (não adianta particionar por data, se você faz muitas consultas por CPF)

    Caso você não tenha obedecido a esses quatro pré-requisitos, o particionamento só lhe trará dores de cabeça. Já utilizei o particionamento em uma tabela de 2 milhões de registros e o desempenho ficou pior.

    Estou desconsiderando dessa minha resposta as vantagens natas do particionamento em relação à administração como reindexação, expurgo, etc

    [ ]s,
     
    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com"

    Além de outra observação do Pedro Galvão !!

    "representa mais uma cada de* acesso até o SQL Server encontrar o dado desejado.


    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA"

    * Acredito que seja camada de acesso



    Flávio Farias
    "May the Force be with you"
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    quarta-feira, 20 de agosto de 2014 13:58
  • Jeferson,

    Particionar uma ou mais tabelas em FILEGROUPs diferentes realmente vai ajudar a melhorar sua performance desde que cada FILEGROUP corresponda a um arquivo .NDF diferente.

    Em alguns casos, seria interessante você manter a tabela em um FILEGROUP (e seu índice clusterizado) e os índices não clusterizados em outro FILEGROUP (preferencialmente, incluíndo nos índices os campos utilizados pelo usuário para exibição). Veja um exemplo abaixo:

    CREATE NONCLUSTERED INDEX [IDX_ADM_CLI_FILIAL]
    ON [dbo].TB_PESSOA (CD_ADM,CD_CLIENTE,CD_FILIAL)
    INCLUDE (NM_PESSOA, CD_SEXO, NR_IDADE)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [IMPORTACAO] GO

    Mesmo que você mantenha os mesmo diretório, a diferença de performance poderá ser pequena se você não definir bem onde alocar cada tabela e seus respectivos índices. Faça um teste recriando alguns índices como te indiquei (preferencialmente, em um ambiente de testes isolado) e compare realizando um cenário de stress de manipulação de dados.

    Caso você esteja particionando os dados dentro de cada tabela, analise se o campo escolhido realmente é o ideal para você particionar os dados hoje e também para redimensionar este particionamento em novos FILEGROUPs, caso seja necessário no futuro. 

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    quarta-feira, 20 de agosto de 2014 14:21
  • Muito bom o artigo Flávio. Obrigado!

    Bem caros colegas, da forma que pensei então é bem provável não ter benefícios, mas sim ter problema com relação a performance.

    Os pontos iniciais a observar de requisitos levando em consideração dito por Durval na Thread e colocado por Flávio:

    • Correto seria primeiramente otimizar as consultas que usufruem da tabela a se particiona;
    • Identificar nas consultas que usufruem da tabela a particionar quais os campos mais utilizados nos WHERES;
    • Ter os FILEGROUPs separados em discos SAS ou SSD.

    Iniciando a separação dos índices não agrupados dos agrupados do mesmo FILEGROUP já teria ganhos de performance? Isso que quis dizer Durval.

    Obrigado pela atenção.

    quarta-feira, 20 de agosto de 2014 15:01
  • Um dos grandes beneficios em Tabelas/Indices Particionados é em relação ao gerenciamento de grandes volumes de dados, e não ganho de desempenho.

    O ganho de desempenho pode ocorrer por alguns fatores, como por exemplo: uma consulta busca dados somente em uma partição ao invés da tabela toda, um Lock pode ser escalado para a partição e não para a tabela se for necessário, etc.

    Veja no link abaixo, sobre indices não-alinhados, pois esse é um dos piores vilões em particionamento.

    http://msdn.microsoft.com/pt-br/library/ms190787.aspx


    Alex Rosa - Premier Field Engineer - Data Platform

    Disclaimer: This content is provided "as-is" and without warranties of any kind, either express or implied. You should therefore verify any information contained in the content before acting on it.


    sexta-feira, 22 de agosto de 2014 02:16
  • É, na verdade Alex existem contra pontos a se discutir e analisar na implementação de particionamento de tabelas grandes, porém vi que existem ganhos de desempenhos a depender do cenário. O texto foi produtivo, obrigado!

    Vou dar continuidade na pesquisa e ver se realmente vale a pena implementar a técnica no meu ambiente.

    Posto aqui qualquer dúvida.

    Grato pela atenção de todos.


    segunda-feira, 25 de agosto de 2014 14:14