none
Índice XML em tabela particionada. Como criar? RRS feed

  • Pergunta

  • Olá Pessoal,

    Estou com uma tabela que precisa ser particionada, mas essa tabela possui dados XML e alguns índices do mesmo tipo.

    no ambiente de teste, consigo realizar o particionamento da tabela, índice da chave primária particionado pelo mesmo schema, índices de chaves estrangeiras e mais outros. Para testes, estou usando a tabela Person.Person do banco de dados AdventureWorks2008R2, que possui a mesma estrutura... 

    OBS:

    1 - A função da partição divide a tabela em três partes por um campo nvarchar(50). 

    2 - Estou dividindo a tabela person.person pelo campo FirstName.

    Perguntas:

    1 - A chave primária é um campo INT, Para eu criar a própria chave primaria com o índice clusterizado, preciso incluir nesse índice o campo FirstName e realizar o particionamento por esse campo. Isso realmente é a melhor forma? é assim que eu devo criar um índice "alinhado"?

    2 - Como posso criar um indice XML para essa tabela? Vejam que nessa tabela, existem 5 índices XML.

    Muito Obrigado,

    Lucas Andrade

    segunda-feira, 30 de julho de 2012 13:29

Respostas

  • Bom Dia,

    Honestamente falando, eu não acho que você terá qualquer ganho de desempenho particionando por uma coluna como "First Name". As consultas normalmente não são feitas por colunas como First Name e mesmo quando o são utilizamos predicados como LIKE '%Nome%' o que torna esse critério de particionamento completamente inútil (só irá aumentar o overhead sem prover nenhum benefício). Colunas como Data, Departamento, etc são candidatas muito mais interessantes.

    1 - A chave primária é um campo INT, Para eu criar a própria chave primaria com o índice clusterizado, preciso incluir nesse índice o campo FirstName e realizar o particionamento por esse campo. Isso realmente é a melhor forma? é assim que eu devo criar um índice "alinhado"?
    Para que você possa realizar o particionamento por esse campo, ele tem de ser o primeiro do índice clusterizado (ainda que não seja uma PK). O índice (ou a PK) pode até ter a coluna que é INT, mas é necessário que as colunas de particionamento venham primeiro e na ordem. Veja que clusterizar um índice cuja primeira coluna seja FirstName irá introduzir um nível de fragmentação na sua tabela gigantesco o que irá comprometer a performance.
     
    2 - Como posso criar um indice XML para essa tabela? Vejam que nessa tabela, existem 5 índices XML.
    Apenas crie-os. O pré-requisito para que índices XML possam existir é apenas ter um índice clustered na tabela.

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos:http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível

    segunda-feira, 30 de julho de 2012 14:39

Todas as Respostas

  • Lucas,

    Vamos por partes:

    1 - Sua Primary Key é Composta por um campo Int e o Campo FirstName?

    2 - Não consegui entender qual é a sua dúvida em relação ao campo XML? Você já tem este campo criado?

    3 - Como você esta dividindo esta tabela pelo campo FirstName?


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    segunda-feira, 30 de julho de 2012 13:46
    Moderador
  • Olá Junior,

    Primeiramente, muito obrigado pela atenção....

    Vamos lá.... Eu dei o exemplo da database AdventureWorks2008R2 pois é exatamente o mesmo problema, então, vou utilizar ela p/ gente tentar solucionar o problema.

    A tabela com os dados já existia e um dos campos dessa tabela é do tipo XML. A tabela Cresceu d+ e está causando lentidões, resolvi dividir a mesma em 3 discos diferentes para melhorar o desempenho.

    criei a seguinte Function:

    USE [AdventureWorks2008R2]
    GO
    
    /****** Object:  PartitionFunction [FunctionPerson]    Script Date: 07/30/2012 10:57:46 ******/
    CREATE PARTITION FUNCTION [FunctionPerson](nvarchar(50)) AS RANGE LEFT FOR VALUES (N'h', N'p')
    GO

    Com essa function, minha ideia é dividir a tabela pelo campo FirstName, onde os nomes de 'A' até 'H' fiquei no primeiro arquivo, de 'I' até 'P' no segundo arquivo e de 'Q' até 'Z' no terceiro arquivo.

    Criei a nova tabela, idêntica a tabela populada com a intensão de popular a mesma com os dados da antiga:

    CREATE TABLE [Person].[Person]( [BusinessEntityID] [int] NOT NULL, [PersonType] [nchar](2) NOT NULL, [NameStyle] [dbo].[NameStyle] NOT NULL, [Title] [nvarchar](8) NULL, [FirstName] [dbo].[Name] NOT NULL, [MiddleName] [dbo].[Name] NULL, [LastName] [dbo].[Name] NOT NULL, [Suffix] [nvarchar](10) NULL, [EmailPromotion] [int] NOT NULL, [AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL, [Demographics] [xml](CONTENT [Person].[IndividualSurveySchemaCollection]) NULL, [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL, [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_Person_BusinessEntityID_Partitioned] PRIMARY KEY CLUSTERED ( [BusinessEntityID] ASC, [FirstName] ASC )On SchemePerson (FirstName)

    ) GO

    Na tabela Person.Person original (que foi renomeada), a PK é apenas o campo "BusinessEntityID". Mas para criar essa chave primária isolada, eu precisaria criar uma outra função de partição sobre um campo INT, mesmo que eu diga no Schema atribuído a essa função que os arquivos serão os mesmos do Schema com a função "NVARCHAR(50). Como quero que o index fique alinhado com a tabela, eu "achei" que essa seria a melhor solução (posso estar erradíssimo e essa é a primeira pergunta).

    A parte do XML é que eu simplesmente nao consigo criar um index XML para essa tabela particionada. Sei que nao posso usar o campo XML como função de partição, o que ocasiona em não poder dividir a mesma pelo próprio campo. Acredito que ela precise de algo mais para realizar a divisão, caso isso seja possível.

    tentei utilizar o seguinte código para criar o indice XML:

    CREATE PRIMARY XML INDEX [PXML_Person_AddContact_Partitioned] ON [Person].[Person] 
    (AdditionalContactInfo, Firstname)
    ON SchemePerson(Firstname)
    GO

    Esse código gera o seguinte erro:

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ','.

    O que me leva a perguntar:

    Posso criar um índice XML para uma tabela particionada? Isso realmente existe ou eu inventei?



    segunda-feira, 30 de julho de 2012 14:17
  • Lucas,

    Todo índice primário XML deve fazer parte de uma tabela, e a mesma deverá possuir um campo Primary Key e este índice vai esta armarrado a esta Primary key.

    Em relação a utilizar um índice XML em tabela particionadas, sinceramente não tenho a resposta na ponta da lingua, mas os testes que realizei também não funcionou.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    segunda-feira, 30 de julho de 2012 14:22
    Moderador
  • Lucas,

    Uma alternativa ao invês de utilizar particionamento, seria criar índices XML Secundários, fazendo uso de um dos 3 tipos de índices:

    • Índice XML secundário PATH
    • Índice XML secundário VALUE
    • Índice XML secundário PROPERTY

    Veja alguns exemplos no Books Online.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    segunda-feira, 30 de julho de 2012 14:24
    Moderador
  • Todo índice primário XML deve fazer parte de uma tabela, e a mesma deverá possuir um campo Primary Key e este índice vai esta armarrado a esta Primary key.

    Perfeito.... Tenho a mesma informação pelo livro do training kit de Mike Hotek.

    Em relação a utilizar um índice XML em tabela particionadas, sinceramente não tenho a resposta na ponta da lingua, mas os testes que realizei também não funcionou.

    Esse é meu medo... rsrs...

    Outra coisa: o campo XML não é chave primária, apenas o campo "BusinessEntityID". Claro que mesmo assim eu preciso criar um índice XML primário para depois criar os índices secundários.

    No caso do índice "PK_Person_BusinessEntityID_Partitioned" eu fiz corretamente? existe alguma forma melhor?

    deixo claro que na tabela original, apenas o campo "BusinessEntityID" é chave primária.

    segunda-feira, 30 de julho de 2012 14:31
  • Bom Dia,

    Honestamente falando, eu não acho que você terá qualquer ganho de desempenho particionando por uma coluna como "First Name". As consultas normalmente não são feitas por colunas como First Name e mesmo quando o são utilizamos predicados como LIKE '%Nome%' o que torna esse critério de particionamento completamente inútil (só irá aumentar o overhead sem prover nenhum benefício). Colunas como Data, Departamento, etc são candidatas muito mais interessantes.

    1 - A chave primária é um campo INT, Para eu criar a própria chave primaria com o índice clusterizado, preciso incluir nesse índice o campo FirstName e realizar o particionamento por esse campo. Isso realmente é a melhor forma? é assim que eu devo criar um índice "alinhado"?
    Para que você possa realizar o particionamento por esse campo, ele tem de ser o primeiro do índice clusterizado (ainda que não seja uma PK). O índice (ou a PK) pode até ter a coluna que é INT, mas é necessário que as colunas de particionamento venham primeiro e na ordem. Veja que clusterizar um índice cuja primeira coluna seja FirstName irá introduzir um nível de fragmentação na sua tabela gigantesco o que irá comprometer a performance.
     
    2 - Como posso criar um indice XML para essa tabela? Vejam que nessa tabela, existem 5 índices XML.
    Apenas crie-os. O pré-requisito para que índices XML possam existir é apenas ter um índice clustered na tabela.

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos:http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível

    segunda-feira, 30 de julho de 2012 14:39
  • Perfeito...

    Isso responde várias das minhas perguntas, mas cria novas... rsrs...

    Agora, partindo p/ uma situação hipotética (já que a tabela que preciso solucionar o problema nao possui campo de data).

    Caso eu tivesse nessa tabela, um campo de data, e a PK fosse o mesmo campo INT (BusinessEntityID), como eu poderia criar o indice clustered? Eu também nao deveria incluir o campo "DATA" nesse indice?

    Exemplo:

    CREATE TABLE [Person].[Person](
    	[BusinessEntityID] [int] NOT NULL,
    	[FirstName] [dbo].[Name] NOT NULL,
    	[MiddleName] [dbo].[Name] NULL,
    	[LastName] [dbo].[Name] NOT NULL,
    	[EmailPromotion] [int] NOT NULL,
    	[Date] [datetime] NOT NULL,
     CONSTRAINT [PK_Person_BusinessEntityID_Partitioned] PRIMARY KEY CLUSTERED 
    (
    	[BusinessEntityID],
    	[DATE] 
    )On SchemePerson (Date)
    )
    GO

    Seria isso?

    segunda-feira, 30 de julho de 2012 15:26
  • Se eu criar o índice XML sem o parâmetro "ON <Destino>", o mesmo é criado, mas se quer consigo chamar as propriedades do mesmo. Quando eu Chamo as propriedades é apresentado o seguinte erro:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------
    
    Cannot show requested dialog.
    
    ------------------------------
    ADDITIONAL INFORMATION:
    
    Cannot show requested dialog. (SqlMgmt)
    
    ------------------------------
    
    Index was out of range. Must be non-negative and less than the size of the collection.
    Parameter name: index (mscorlib)
    
    ------------------------------
    BUTTONS:
    
    OK
    ------------------------------
    

    segunda-feira, 30 de julho de 2012 15:40