Since you have some common filtering between those queries, you might be able to speed things up, and reduce the amount of locking / blocking going on, by adding a filtered index on the "Active" and "Status" columns.
It's difficult to tell which columns belong to which tables without the schema, but the index would look something like this:
You could use a manual plan guide to enforce the desired plan.
Alternatively, you could use Query Store to enforce plan guides via the GUI.
Also, you could run an update statistics job after the big load of messages. I wrote a post on my blog showing an easy way to do that.
Not an answer but if hash join is not forced that query wont get hash join as a plan.
A workaround is to set a bit variable to 1 if rows exist in the table and 0 if not and instead of #Emptytable use
(Select * from #Emptytable where @bit =1)
And add an option recompile to the end , no execution will happen.
I think that condition should never happen if ...
Not running the probe side of the join when the build is empty is an optimization. It is not available for parallel row mode hash join when the probe side has a child branch i.e. when there is an exchange operator.
There was a similar report many years ago by Adam Machanic on the now-defunct Connect feedback site. The scenario was a start-up Filter on the ...
The first part here is removing the high estimates on the TVF.
declare @para as xml
set @para = '<PI><PGID>00000000000000000000000000000000</PGID><PN>0</PN></PI>'
CREATE TABLE dbo.Test(id int identity(1,1) primary key not null, val int, xmldata xml)
INSERT INTO dbo.Test WITH(TABLOCK)(val,xmldata)
I think the base cardinality estimate for XML TVF is 10,000 rows - I read it in a presentation by Michael Rys
To improve estimates, you can either add a schema to your XML or create an XML index.
Other than that I could only direct you to row goal hints and estimates trick I first heard from Adam Machanic here
An index can only be used to process an ORDER BY clause without sorting if the index is in the same order as specified by ORDER BY.
Now your index is (by default) sorted ASC NULLS LAST, and since an index can be scanned in both directions, it can support both ORDER BY sale_id ASC NULLS LAST and ORDER BY sale_id DESC NULLS FIRST. But since the ordering is ...
I cannot understand why the query is so complex.
Query text taken from published plan:
with Object1 as
select Object2.Column1 as Column2
from Schema1.Object3 as Object2
left outer join Schema1.Object4 as Object5
on Object2.Column1 = Object5.Column1
where Object5.Column1 is null
insert into Schema1.Object4
select * from Schema1.Object3
Yes, the dynamic SQL you submit will be treated as ad-hoc. I.e., a hash is generated from the text, making it sensitive to everything (space, upper/lower, search arguments, etc).
If you happen to submit the exact same string as a regular ad-hoc (not dynamic sql from a proc), or from other procedures as dynamic SQL, then the plans can be re-used.
What is ...
Since you have SSD on your prod, have you tried setting seq_page_cost = random_page_cost = 1.0? There are lot of articles which suggest so.
We observed a similar issue which was due to the fact that our dev env had small data while the production env. had a lot of terminated client's data (un-touched rows) which was polluting the indexes. So we tweaked our ...
Your last (fast) query has two identical WHERE conditions, which Postgres is able to identify and fold to one. Hence the simpler plan with just a single index condition.
It gets more expensive with multiple different conditions. But Postgres still keeps operating based on estimates for actual input values. Try with one or more large intervals in the WHERE ...
The problem comes from the bad row count estimate for the bitmap index scan on api.molecular_structure (26392 instead of the actual 89), and it must be a minor difference in the data that tilts the plan.
It is probably difficult to improve the estimate.
How about rewriting the query?
It's a somewhat annoying limitation of the current optimizer.
I wrote about this a little as part of The Eager Index Spool and The Optimizer:
Index spools do not tell the optimizer they support output ordered by the spool's index keys. If sorted output from the spool is required, you may see an unnecessary Sort operator. Eager index spools should often ...
Derived from https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan
DECLARE @SearchForSql AS NVARCHAR(MAX) = 'SQL TEXT'
SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE Text LIKE CONCAT('%' ,...
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 ...