Without having much detail, I can't recommend much.
One thing that does jump out at me is that it's very likely you can improve performance on the table by normalizing it! The presence of so many duplicated (so few unique) values in the columns you listed suggests that perhaps many others in the table are not normalized, as well. I'm suggesting making the Name column an int (or even smallint) with a foreign key to a Names table, and the Current_state column bit (or alternately a tinyint) with a foreign key to a WhateverStates table. You would have to, of course, change your data access code to deal with this indirection, but that is nothing more than the basic job relational database developers have always had to do.
Normalizing will reduce the number of bytes per row, increasing the number of rows per page, reducing the number of pages that have to be read to satisfy any particular query, helping performance across the board! Right now the columns given require likely close to 34 bytes each. After the change I suggest, those columns will only require 11 bytes each. Of course, I haven't seen your whole table--your rows may be so big that it doesn't matter.
What columns and datatypes are in the clustered index (if there is one)? This can radically affect the size of the nonclustered indexes, again affecting performance in exactly the way I described (rows per page).
When you do query based on non-selective columns such as Current_state, what other columns are always or almost always included? It may be okay for you to have a nonselective column in an index if the index also contains a more selective column (or that in conjunction with the less selective column is more highly selective). If on the other hand you generally query often for rows based on the single column Current_state = 'Pending', then you can add a filtered index:
CREATE INDEX IX_YourTable_Pending ON dbo.YourTable (ClusteredColumnsInOrder)
WHERE Current_State = 'Pending'; --SQL 2008 and up only
This technique could help you even when you also include other columns: you would want to put those in the index instead of the ClusteredColumnsInOrder columns I suggested (which was just a tricky way to not put any additional columns into the nonclustered index, since--remember, now--nonclustered indexes always have all the columns of the clustered index implicitly included). Or, if you only pull a very few other columns, you can make your nonclustered index cover the query by adding INCLUDE (AdditionalColumn1, AdditionalColumn2) so that the query engine doesn't have to go back to the clustered index to satisfy the query.
You haven't provided very much information such as full table schema, sample data, and sample queries, and without those it's going to be pretty hard to give you very specific advice about what to do.
One thing I can say, though, is that indiscriminately throwing indexes at the table may not improve things much and could in fact hurt performance of your system overall.
If the hints I have given you here don't seem to help much, then I recommend that you do come back with some of the additional info I mentioned so that we can do a better job of assisting you.
The declaration of singleton in the path expression of the index enforces that you can not add multiple <Number> elements but the XQuery compiler does not take that into consideration when interpreting the expression in the value() function. You have to specify [1] to make SQL Server happy. Using typed XML with a schema does not help with that either. And because of that SQL Server builds a query that uses something that could be called an "apply" pattern.
Easiest to demonstrate is to use regular tables instead of XML simulating the query we are actually executing against T and the internal table.
Here is the setup for the internal table as a real table.
create table dbo.xml_sxi_table
(
pk1 int not null,
row_id int,
path_1_id varbinary(900),
pathSQL_1_sql_value int,
pathXQUERY_2_value float
);
go
create clustered index SIX_T on xml_sxi_table(pk1, row_id);
create nonclustered index SIX_pathSQL on xml_sxi_table(pathSQL_1_sql_value) where path_1_id is not null;
create nonclustered index SIX_T_pathXQUERY on xml_sxi_table(pathXQUERY_2_value) where path_1_id is not null;
go
insert into dbo.xml_sxi_table(pk1, row_id, path_1_id, pathSQL_1_sql_value, pathXQUERY_2_value)
select T.ID, 1, T.ID, T.ID, T.ID
from dbo.T;
With both tables in place you can execute the equivalent of the exist() query.
select count(*)
from dbo.T
where exists (
select *
from dbo.xml_sxi_table as S
where S.pk1 = T.ID and
S.pathXQUERY_2_value = 314 and
S.path_1_id is not null
);
The equivalent of the value() query would look like this.
select count(*)
from dbo.T
where (
select top(1) S.pathSQL_1_sql_value
from dbo.xml_sxi_table as S
where S.pk1 = T.ID and
S.path_1_id is not null
order by S.path_1_id
) = 314;
The top(1) and order by S.path_1_id is the culprit and it is [1] in the Xpath expression that is to blame.
I don't think it is possible for Microsoft to fix this with the current structure of the internal table even if you were allowed to leave out the [1] from the values() function. They would probably have to create multiple internal tables for each path expression with unique constraints in place to guarantee for the optimizer that there can only be one <number> element for each row. Not sure that would actually be enough for the optimizer to "break out of the apply pattern".
For you who think this fun and interesting and since you are still reading this you probably are.
Some queries to look at the structure of the internal table.
select T.name,
T.internal_type_desc,
object_name(T.parent_id) as parent_table_name
from sys.internal_tables as T
where T.parent_id = object_id('T');
select C.name as column_name,
C.column_id,
T.name as type_name,
C.max_length,
C.is_sparse,
C.is_nullable
from sys.columns as C
inner join sys.types as T
on C.user_type_id = T.user_type_id
where C.object_id in (
select T.object_id
from sys.internal_tables as T
where T.parent_id = object_id('T')
)
order by C.column_id;
select I.name as index_name,
I.type_desc,
I.is_unique,
I.filter_definition,
IC.key_ordinal,
C.name as column_name,
C.column_id,
T.name as type_name,
C.max_length,
I.is_unique,
I.is_unique_constraint
from sys.indexes as I
inner join sys.index_columns as IC
on I.object_id = IC.object_id and
I.index_id = IC.index_id
inner join sys.columns as C
on IC.column_id = C.column_id and
IC.object_id = C.object_id
inner join sys.types as T
on C.user_type_id = T.user_type_id
where I.object_id in (
select T.object_id
from sys.internal_tables as T
where T.parent_id = object_id('T')
);
Best Answer
Data differences
With a low amount of records to seek on, the optimizer is able to use the
SIX_T_pathXQUERYindex:and filter on
moretextwith a seek predicate:An interesting part here is that it executes the key lookup to get the path_1_id values that are not null.
Since that is a filter definition on the nonclustered xml index...
...while not being present in the index itself.
As a result, for the optimizer to consider using the index it knows that it has to complete these steps:
SIX_Tclustered index on the internal table and filter onpath_1_id is not nullsincepath_1_idis not included in the secondary indexdbo.Tto return the ID to count onTipping points
When the expected rows to be returned are higher, it favours using the selective clustered XML Index, to be able to run a merge join & no key lookup instead:
with a residual predicate:
To filter on the xml column and
path_1_idComparing the plans
You could (but you shouldn't) use the
USE PLANhint to force the plan with the seek on the XML column and see what would happen if we where to search on these values.With the execution time =
And the execution time for the scan + merge join plan:
In short, I believe that the scan with residual predicate + merge join choice was the right choice by the optimizer.
No way around it
While I might be wrong, I don't think there is a way to improve the count query with regular XML Indexes. We also cannot change these internal tables or even query them:
It even gives a missing index hint on the merge join query plan, which you cannot create:
To improve the queries, you would have to look into non-xml index solutions.
Edit
I would advise against this, but for testing purposes it would be fine.
Step 1: You would have to get the actual execution plan of the query and get the xml:
If you don't have the query with the low estimates, enter a value that does not exist like:
Step 2: Replace all
's with''s in the execution plan xml, we will need this later.Step 3: Paste the plan between
OPTION( USE PLAN '')Step 4: I had to change utf-16 to utf-8 to get the use plan hint to work
From:
To:
Step 5: Run the query.
My query now looks like this: