Selective XML Indexes

Article
12/18/2012

Con il Service Pack 1 di SQL Server 2012 è stata aggiunta una funzionalità molto utile a chi utilizza il tipo nativo di SQL Server dedicato ai documenti XML: gli indici XML selettivi. Questi indici selettivi permettono di specificare quali elementi (o, meglio, percorsi) da indicizzare in contrasto col l’indice primario XML che indicizza tutto il documento. Come potete immaginare, è evidente che – a fronte di pattern di ricerca prevedibili – sia più efficiente indicizzare solo quei percorsi specifici.

Per maggiori dettagli vi rimando all’articolo sull’MSDN: http://msdn.microsoft.com/en-us/library/jj670108.aspx.

Vediamo insieme un esempio pratico di questo nuovo indice. Per farlo creiamo un database, una tabella molto semplice e popoliamo con XML di esempio. Sottolineo che fra i prerequisiti ci sia la presenza dell’indice clustered (http://msdn.microsoft.com/en-us/library/jj670108.aspx#prereq). Quindi niente indici XML selettivi su tabelle heap (un motivo in più… :)).

 CREATE DATABASE TestSXI;
GO
USE TestSXI;
GO

CREATE TABLE testTbl(
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    XmlDoc XML);
GO

INSERT INTO testTbl(XmlDoc) VALUES(
'<Meme xmlns="http://schemas.test.com/xyz">
<WorkHours>500</WorkHours>
<TaskName>Mission Xyz III</TaskName>
</Meme>');
GO 500
INSERT INTO testTbl(XmlDoc) VALUES(
'<Meme xmlns="http://schemas.test.com/xyz">
<WorkHours>1</WorkHours>
<TaskName>Mission Xyz I</TaskName>
</Meme>');
GO 
INSERT INTO testTbl(XmlDoc) VALUES(
'<Meme xmlns="http://schemas.test.com/xyz">
<WorkHours>222</WorkHours>
<TaskName>Mission Xyz II</TaskName> 
<TaskName>Mind your head!</TaskName>
<TaskName>No task name</TaskName>
</Meme>');
GO 222

INSERT INTO testTbl(XmlDoc) SELECT XmlDoc FROM testTbl;

GO 7

Come potete notare inseriamo dei documenti che potrebbero avere uno schema simile al seguente:

 <?xmlversion="1.0"encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://schemas.test.com/xyz" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Meme">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="1" maxOccurs="1" nillable="false" name="WorkHours" type="xs:integer" />
        <xs:element minOccurs="0" maxOccurs="unbounded" nillable="false" name="TaskName" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Fino al Service Pack 1 di SQL Server potevamo solo indicizzare tutto il documento con un indice XML. Ad esempio con:

 CREATE PRIMARY XML INDEX [PXML_XmlDoc] ON [dbo].[testTbl](XmlDoc);

GO

Creaimo un indice XML completo. Supponiamo che la nostra query ricorrente sia di questo tipo:

 WITH XMLNAMESPACES ('http://schemas.test.com/xyz' as yming)
SELECT COUNT(*)
FROM testTbl
WHERE XmlDoc.exist(N'(/yming:Meme/yming:WorkHours[.=222])') = 1

Ovviamente sarebbe molto più efficiente indicizzare il solo percorso che identifica WorkHours dentro il tipo Meme. Per far questo cerchiamo di creare il nostro indice XML selettivo:

 CREATE SELECTIVE XML INDEX sxi_Test ON [dbo].[testTbl](XmlDoc)
WITH XMLNAMESPACES ('http://schemas.test.com/xyz' as yming)
FOR
(    
    pathWH2 = '/yming:Meme/yming:WorkHours' AS XQUERY 'xs:integer' SINGLETON
);
GO

SQLMS vi dirà che non può farlo a meno che voi abilitiate la funzionalità:

Msg 9539, Level 16, State 1, Line 1

Selective XML Index feature is not supported for the current database version

Per farlo basta eseguire questo comando:

 EXEC sp_db_selective_xml_index NULL, 'TRUE';
GO

Perché è necessario abilitare espressamente la funzionalità? Questo perché abilitando il supporto alla funzionalità di indici XML selettivi si aumenta la versione del database. In questo modo SQL garantisce che un database con questa funzionalità (che evidentemente è stata abilitata da un SQL 2012 almeno SP1) non venga restorato su un SQL che non la supporta (ad esempio, ma non solo, SQL 2012 RTM).

Per vederlo utilizziamo il comando non documentato né supportato DBCC DBINFO (quindi non usatelo se non per scopi didattici!):

 EXEC sp_db_selective_xml_index NULL, 'TRUE';
GO
DBCC DBINFO('TestSXI') WITH TABLERESULTS;
EXEC sp_db_selective_xml_index NULL, 'FALSE';
GO
DBCC DBINFO('TestSXI') WITH TABLERESULTS;

Per maggiori informazioni vi rimando a questo post di Igor: http://blogs.technet.com/b/italian_premier_center_for_sql_server/archive/2011/12/14/il-falso-mito-della-migrazione-da-una-versione-di-sql-all-altra.aspx.

Abilitato il supporto alla nuova funzionalità riproviamo a creare l’indice XML selettivo. Notiamo come venga specificato l’attributo SINGLETON per migliorare le performance di SQL. Questo attributo equivale a maxOccurs=”1” dell’XSD.

SQL anche in questo caso non ci permette di creare l’indice. Questa volta è perché abbiamo scelto un tipo non supportato per gli indici XML selettivi su XML untyped. Qui è chiaramente dettagliato l’elenco supportato: http://msdn.microsoft.com/en-us/library/jj670107.aspx#untyped . Essi sono:

xs:boolean
xs:double
xs:string
xs:date
xs:time
xs:dateTime

Per i nostri scopi scegliamo xs:string e riproviamo:

 CREATE SELECTIVE XML INDEX sxi_Test ON [dbo].[testTbl](XmlDoc)
WITH XMLNAMESPACES ('http://schemas.test.com/xyz' as yming)
FOR
(    
    pathWH2 = '/yming:Meme/yming:WorkHours' AS XQUERY 'xs:string' SINGLETON
);
GO

Questa volta abbiamo successo.

Per testare l’efficacia dell’indice effettuiamo nuovamente la nostra query (dopo avere abilitato le statistiche con SET STATISTICS IO ON e aver abilitato la visualizzazione del piano di esecuzione effettivo in SSMS). Per vedere l’incremento di performance eseguiamo la query due volte nello stesso batch. Fra una esecuzione e l’altra droppiamo l’indice XML selettivo (per il momento non possiamo usare i query hints).

 WITH XMLNAMESPACES ('http://schemas.test.com/xyz' as yming)
SELECT COUNT(*)
FROM testTbl
WHERE XmlDoc.exist(N'(/yming:Meme/yming:WorkHours[.=222])') = 1
GO
DROP INDEX sxi_Test ON [dbo].[testTbl]
GO
WITH XMLNAMESPACES ('http://schemas.test.com/xyz' as yming)
SELECT COUNT(*)
FROM testTbl
WHERE XmlDoc.exist(N'(/yming:Meme/yming:WorkHours[.=222])') = 1
GO

Notiamo anche la differenza tangibile di IO:

Happy Coding,

Francesco Cogno

Selective XML Indexes

Additional resources