Promises, Promises
I said I’d follow up on this, and here it is. I’m by no means the world’s foremost authority on these, I just played with a simple example until I figured out what worked the best for my query. So you can all play along at home, I’m 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. I can’t promise that I’ll be adding XML indexes to it in the near future. Selective XML indexes are 2012+, so I’d have to wait for 2008R2 to go out of support. At this rate, that won’t happen until I throw SQL Server out a window and open a bar that I only let my friends drink in.
Other forms of XML indexes can end up being quite large, and often not worth the resource expenditure to create for the relatively small amount of XML processing we limit it to, by default. The performance gains just wouldn’t be there.
Hey, Bartender
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. I think they’re at least size of data, if I 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) |
I’m lazy, so I’m just grabbing a bit of code from BlitzCache to query our XML.
Right now, there are a paltry 225 query plans in my temp table. I know, right? Some consultant I am. I’m not even generating a ridiculous workload to pollute 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 I get looks ridiculous and costs 1355 query bucks. Ew. I 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.
Well, nuts. What else can we try?
|
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 also makes no difference whatsoever. I tried defining each path as the SQL type I’m returning. Again, same plan, same cost. What’s next?
|
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, I tried defining each path as the XQuery expression I’m 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
I’m not getting into the other possible XQuery methods, like .exist(), .nodes(), or .query(), or using sql:column to join data anywhere. That’s more XML than I care to mess with in one blog post. If you use XML a lot, and you’re on SQL Server 2012+, you may want to give SXIs a shot. They can be pretty cool when you get them working.
Thanks for reading!
19 Comments. Leave new
Have you tried using FLOAT instead of INT in the second selective XML index attempt? Just curious.
Hi, yeah. It didn’t make a difference for me.
The SQL type is to be used and matched to the return value of the values function when you are returning the value of the attribute. Your query is feeding the value of the attribute to the sum function in xquery and that is why you need to define the type in the index to by XQUERY.
Yeah, I understand why. I thought it was interesting to walk through the different syntax.
Thanks, by the way, for all the great answers you post on Stack about working with XML. They’ve helped me a million times over.
Oh, thanks, that makes me happy.
The way you showed it here was just perfect. The only way I know of to get the selective index definitions right is to try, try and try again until it works :).
Hah. If that’s the only way you know how to do it, that makes me feel way better, because that’s the only way I know how to do it.
Erik, thanks for the article. I didn’t know about SXI’s and they’re definitely not simple, but man o man do they improve performance used correctly.
Thanks! Glad you liked it.
I am using SQL 2012 and this is the output of SELECT @@VERSION from my query window
Microsoft SQL Server 2012 – 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.2 (Build 9200: )
I doubt whether for this edition SELECTIVE XML INDEX can be created as it says this feature is not available. Kindly suggest me an alternate for the version mentioned above
Barani – when you’re running into problems with something, you’ll need to post the exact error that you’re getting. Thanks.
Brent – Below is the query which i am trying to run and also to tell you i have already created a selective XML index on XMLRECORD column in the same table. As the selective index is proven to be performant now, i am trying to create the same for one more tag in the same column as below and it throws error.
Kindly let me know the feasibility of creating the selective XML index that will cover the desired C tag value of the same column in a table
Index created already :
CREATE SELECTIVE XML INDEX IX_FBNK_AAA_C16 ON FBNK_AA_ARRANGEMENT006(XMLRECORD)
FOR
(
pathNAME_1_16 = ‘/row/c16’ as sql varchar(max),
pathNAME_1_16X = ‘/row/c16/text()’ as xquery ‘xs:string’
)
Index trying to create now :
CREATE SELECTIVE XML INDEX IX_FBNK_AAA_C4 ON FBNK_AA_ARRANGEMENT006(XMLRECORD)
FOR
(
pathNAME_1_4 = ‘/row/c4’ as sql varchar(max),
pathNAME_1_4X = ‘/row/c4/text()’ as xquery ‘xs:string’
)
Error message:
selective XML index ‘IX_FBNK_AAA_C16’ already exists on column ‘XMLRECORD’ in table ‘FBNK_AA_ARRANGEMENT006’. Multiple selective XML indexes per column are not allowed.
Yep, that error is pretty clear: multiple selective XML indexes per column are not allowed.
Going forward, you can post questions over at https://dba.stackexchange.com. Thanks!
Thanks for the reply Brent. I read that we can add number of nodes/tags to an existing selective XML index. IS there any limitation for the extra nodes that can be added. This should not degrade the performance and selective XML index benefits as well.
Once this thread is closed, i shall reach through https://dba.stackexchange.com
Thanks in advance
Barani – I can’t do free support here, sorry. For questions, go to a Q&A site like the one I already mentioned. Thanks in advance.
Thanks for the reply Brent.Here is the query that i am trying to run in SQL 2012 version and the error message
EXECUTE sys.sp_db_selective_xml_index
Could not find stored procedure ‘sys.sp_db_selective_xml_index’.
Selective XML indexes were added in SQL Server 2012 SP1; build number 11.0.2100.60 is SQL Server 2012 RTM.
Thanks for the reply.
May i know the equivalent for this for 2012 and 2008 versions. The reason behind is we are trying to create index on XML record for the selected tag. This was sufficed well in this type of SELECTIVE XML INDEX.As some of the clients are still in 2012/2008 i am in need of providing the alternate solution for them
As far as I know, selective XML indexes are not available in SQL Server 2008, 2008 R2, or 2012 RTM.
The documentation (https://docs.microsoft.com/en-us/sql/relational-databases/xml/selective-xml-indexes-sxi?view=sql-server-2017) does list SQL Server 2008 as the lowest version supported, but – to my knowledge – this is incorrect.
SQL Server 2008 support ends soon; therefore, you are encouraged to upgrade anyway.
However, if your application needs to support older versions, you can only use the “regular” XML indexes (https://docs.microsoft.com/en-us/sql/relational-databases/xml/xml-indexes-sql-server?view=sql-server-2017) in those versions.
Thanks Matija.
I also like to know how the comparisional operators like >,=, (sql:variable(“@id0”))]’) = 1 )
ORDER BY RECID