TABLE OF CONTENTS
Selective XML Index’ler: Not That Bad at All
We said We’d follow up on this, and here it is. We’re by no means the world’s foremost authority on these, We just played with a simple example until We figured out what worked the best for my query. So you can all play along at home, We’re using XML we all have access to Query Plans!
If any of you have ever used sp_BlitzCache, you’ll know there’s a ton of XQuery involved. We can’t promise that We’ll be adding XML indexes to it in the near future. Selective XML indexes are 2012+, so We’d have to wait for 2008R2 to go out of support. At this rate, that won’t happen until We throw SQL Server out a window and open a bar that We only let my friends drink in.
Other types of XML indexes may end up being rather huge, and frequently they are not worth the resource investment to produce for the typically limited amount of XML processing. Simply put, the performance improvements wouldn’t exist.
So what is a Selective XML index? It’s an index! For XML! Where you pick the parts of the XML to index. Other XML indexes are rather all or nothing and can end up being pretty huge, depending on the size of your documents. We think they’re at least the size of data if We recall correctly. Let’s take a look at some examples.
| 1
2 3 4 5 6 7 8 |
SELECT
ID = IDENTITY(INT,1,1), deq.query_plan INTO #xml_index_test FROM sys.dm_exec_cached_plans AS dec CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq
ALTER TABLE #xml_index_test ADD CONSTRAINT pk_id PRIMARY KEY CLUSTERED (ID) |
There are currently just 225 query plans in my temporary table. We do, don’t we? We are the consultant. We’re not even producing an absurd amount of workload to clog up my plan cache.
| 1
2 3 4 5 6 7 8 9 10 11 |
SET STATISTICS TIME, IO ON
;WITH XMLNAMESPACES(‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’ AS p) SELECT xit.ID, SerialDesiredMemory = xit.query_plan.value(‘sum(//p:MemoryGrantInfo/@SerialDesiredMemory)’, ‘float’) , SerialRequiredMemory = xit.query_plan.value(‘sum(//p:MemoryGrantInfo/@SerialRequiredMemory)’, ‘float’), CachedPlanSize = xit.query_plan.value(‘sum(//p:QueryPlan/@CachedPlanSize)’, ‘float’) , CompileTime = xit.query_plan.value(‘sum(//p:QueryPlan/@CompileTime)’, ‘float’) , CompileCPU = xit.query_plan.value(‘sum(//p:QueryPlan/@CompileCPU)’, ‘float’) , CompileMemory = xit.query_plan.value(‘sum(//p:QueryPlan/@CompileMemory)’, ‘float’) FROM #xml_index_test AS xit; |
But the query plan We get looks ridiculous and costs 1355 query bucks. We already regret working with XML, again.
The first kind of Selective XML index we can try is defined on the two nodes we’re querying. You can create it like this:
| 1
2 3 4 5 6 |
CREATE SELECTIVE XML INDEX ix_xml_selective ON #xml_index_test(query_plan)
WITH XMLNAMESPACES(‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’ AS p) FOR ( MemorygrantInfoNode = ‘//p:MemoryGrantInfo’ AS XQUERY ‘node()’, QueryPlanNode = ‘//p:QueryPlan’ AS XQUERY ‘node()’ ); |
If we run our query again… Nothing changes, only gets worse. Well, not literally worse. The plan and cost are the same, but now we have a useless index.
| 1
2 3 4 5 6 7 8 9 10 |
CREATE SELECTIVE XML INDEX ix_xml_value ON #xml_index_test(query_plan)
WITH XMLNAMESPACES(‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’ AS p) FOR ( SerialDesiredMemory = ‘//p:MemoryGrantInfo/@SerialDesiredMemory’ AS SQL INT SINGLETON, SerialRequiredMemory = ‘//p:MemoryGrantInfo/@SerialRequiredMemory’ AS SQL INT SINGLETON, CachedPlanSize = ‘//p:QueryPlan/@CachedPlanSize’ AS SQL INT SINGLETON, CompileTime = ‘//p:QueryPlan/@CompileTime’ AS SQL INT SINGLETON, CompileCPU = ‘//p:QueryPlan/@CompileCPU’ AS SQL INT SINGLETON, CompileMemory = ‘//p:QueryPlan/@CompileMemory’ AS SQL INT SINGLETON ); |
This is also completely irrelevant. Each path was defined as the SQL type we were returning. Again, same strategy, same price. Next, what?
| 1
2 3 4 5 6 7 8 9 10 |
CREATE SELECTIVE XML INDEX ix_xml_selective ON #xml_index_test(query_plan)
WITH XMLNAMESPACES(‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’ AS p) FOR ( SerialDesiredMemory = ‘//p:MemoryGrantInfo/@SerialDesiredMemory’ AS XQUERY ‘xs:double’ SINGLETON, SerialRequiredMemory = ‘//p:MemoryGrantInfo/@SerialRequiredMemory’ AS XQUERY ‘xs:double’ SINGLETON, CachedPlanSize = ‘//p:QueryPlan/@CachedPlanSize’ AS XQUERY ‘xs:double’ SINGLETON, CompileTime = ‘//p:QueryPlan/@CompileTime’ AS XQUERY ‘xs:double’ SINGLETON, CompileCPU = ‘//p:QueryPlan/@CompileCPU’ AS XQUERY ‘xs:double’ SINGLETON, CompileMemory = ‘//p:QueryPlan/@CompileMemory’ AS XQUERY ‘xs:double’ SINGLETON ); |
Finally, We tried defining each path as the XQuery expression We’re using to retrieve them, along with the data type. This finally makes a difference and a huge difference. Query cost is down to 0.55. That ain’t bad for an index.
Just Selecting XML
We won’t discuss further XQuery techniques like.exist(),.nodes(), or.query() or how to join data anywhere using a SQL: column. We don’t want to play about with so much XML in one blog post. If you frequently utilize XML and are running SQL Server 2012 or later, you might want to try out SXIs. When you get them to function, they can be very cool.
