Ok, so that's the answer to your question, but you probably want to know why.
Ok, so why not?
Different databases might have different data, and thus need different plans.
Say you restore the WideWorldImporters database to the same server TWICE (Let's call it WWI_1 and WWI_2). You've got two identical databases. SQL Server could possibly create one ...
With a low amount of records to seek on, the optimizer is able to use the SIX_T_pathXQUERY index:
and filter on moretext with 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 ...
There are many ways :
If you can run the query
you can use set statistics time on which will give you:
SQL Server parse and compile time:
CPU time = xx ms, elapsed time = xx ms.
If you cant run the query, then mine the plan cache to check for query compile time along with many other significant insights.
There are many other ways like profiler, ...
The question is, how can i make counting by xml as fast as counting by
Depending on how flexible you need to be with your xPath expression you could use property promotion.
create function dbo.GetSomeText(@X xml) returns varchar(8) with schemabinding as
return @X.value('(/SomeText/text())', 'varchar(8)');
alter table dbo.T ...
turning on optimize for ad hoc workloads will result in slight reduction in the size of the query plans in the cache
Out of the 158'997 adhoc queries in the plan cache, 126'087 have only been executed once.
I wouldn't call removing 79% of the AdHoc plans a slight reduction.
In which cases are non-parameterized, non-trivial, Adhoc query plans ...
Your index on UserID is not the optimal one for that query. It leaves the optimizer a choice of using it and needing an additional sort by CommentID or scanning the table (backwards) to get the rows already sorted by commentID and filtered on the fly by the where clause and the top operator. Although the clustered PK column is included in each nonclustered ...
I don't think that it's failing to use the index, but that it has a lot to do with a combination of factors:
number of rows in the table
number of unique values in col1
query planner tuning (seq_page_cost,random_page_cost,etc.)
It could very well be that the lowest-cost plan involves a Sequential Scan, at least for this situation.
On my machine, I get the ...
You should use pg_stat_statements.
Modify postgresql.conf as follows:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
track_io_timing = on
and restart PostgreSQL.
CREATE EXTENSION pg_stat_statements;
and find all the data you want in thr view pg_stat_statements.
Should SQLRaptor's answer not work out for you, one other drastic thing you can try is using the query hint FORCESEEK. This essentially forces the optimizer to always use a plan that does an index seek instead of an index scan (if possible).
One reason it's not a first go-to is because it limits the number of query plans that the optimizer can choose to ...
First of all, you can simplify the query you have. format() is not needed, among other things:
WHERE tag_ids @@ array_to_string(ARRAY (
WITH tag_ids AS (SELECT id FROM tags WHERE tag IN ('kittens', 'puppies'))
SELECT 0 WHERE NOT EXISTS (TABLE tag_ids)
For the purposes of answering, let's label these as Query A and Query B. Query A and Query B are not identical (even if they produce equivalent results).
You cannot force SQL to use a plan generated for Query B when asking it to execute Query A.
USE PLAN is for running a specific plan when multiple plans exist for a single query. https://www.brentozar....