Is there a way to fix this without changing the code?
Create the following index to eliminate the eager index spools:
-- Give this index a better name
CREATE INDEX i ON dbo.TABLE2 (COLUMN4, COLUMN7) INCLUDE (COLUMN3);
With that in place, you should get a plan similar to:
The other problems described in the question will almost certainly disappear once you ...
I agree the optimizer could be smarter about where the key lookup should occur in the plan with OFFSET and FETCH.
As a workaround, you could use a CTE like below.
WITH Top10Keys AS (
[target_user_id] = 100017
It's because the order of operations that the clauses of your query are executed in. The WHERE clause occurs before your OFFSET and FETCH clauses. That's why not only are there about 10,000 (9,000 in your first example) key lookups, but there's just as many index seeks. This is the filtering that occurs as a result of your predicate on target_user_id in your ...
Given the size of your table (~11k rows), I think it would be safe to assume that SQL Server estimated that the cost of performing a seek on the non-clustered index and then potentially multiple RID lookups was more expensive than performing a table scan.
There is some evidence to support this theory within the second query plan that you pasted. I would ...
Hmm 🤔 so as I mentioned in the comments, your actual execution plan reveals there is a severe cardinality estimate issue (screenshot of operation details for reference) on the Clustered Index Seek operation occuring on the atividade table. I'm not seeing much else in the execution plan or your query that jumps out at me explaining why you might be ...
the operator highlighted in green (Clustered Index scan on TABLE2) shows 100% but it's execution time continues clocking up.
The percentages there are based on row estimates. It will show 100% once it reaches the estimated numbers of rows expected, but the operator continues running until all of the actual required rows have been read.
There are valid reasons for disabling AUTO_UPDATE_STATISTICS, such as wanting to have more control over the updating process of your stats. However disabling the auto update to prevent plans from changing destroys the purpose of the optimizer.
If you know your data very well and you are confident that a given plan will lead to good results regardless of the ...
A bit of extra info:
this is the statistics for when you use table scan vs index seek+RID Lookup
Table 'MP_Notification_Audit'. Scan count 1, logical reads 154, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob ...
The villain is not GROUP BY and COUNT, but LIKE "%..." and OR. But, I will launch into a major rewrite of your application...
In order to make an efficient real estate search tool, you must
Divide up the data into "text search" and "numeric search"
Also split into "commonly searched on" versus "rarely searched ...
The usual index type (btree) on text columns orders values alphabetically, so it is fast to find a specific value, or a range between two bounds (say, A to B), or values that start with a specific prefix. This means it will optimize LIKE 'foo%' quite well.
It's a bit like a dictionary. You can quickly find a word if you know the first letters, but the btree ...
One thing you can try is re-writing the query to use the UNION ALL clause instead of a bunch of OR operators in the WHERE clause's predicate. It depends on the execution plan you were getting previously vs what this one generates though.
J.D. has covered the stats bit - essentially, you might get better estimates if you have a more representative stats sample for the indexes on the SubHourlyMeasurementData table. Right now it's only sampling around 1% of the rows in the table:
Here we can see that the stats have been updated (ModificationCount is 0), but the SamplingPercent was only 1....
So you definitely have some wild cardinality estimate issues going on based on your execution plan (along with some sub-performant index scans and warnings such as residual I/O issues probably resultant of the cardinality estimate issues).
Please see the following couple of cases where SQL Server thinks your query is only going to return a small number of ...
First, the index scan is slower than necessary, because your work_mem is not big enough to contain the bitmap. Increase it until you get no more “lossy” heap blocks during the bitmap heap scan.
The second plan is faster than the first because it uses two parallel workers (which the other plan cannot). But it uses way more resources: it keeps three processes ...
I found the solution. All you need to do is add an ORDER BY with the TAG field. So the end query becomes:
where ID =~ /4372502|4399699|4406512|4407840/
ORDER BY ID
The version with IN will be slightly faster, because = ANY is cheaper than ~.
The estimate suggests otherwise, but only because it estimates a different number of result rows.
You can make these queries faster with an index on the more selective condition.
Per our discussion in the comments, I think this Microsoft doc might be what you're looking for, except I have a feeling this is only applicable to an on-prem instance and you won't be able to adjust this in Azure.
I've also found a tangentially related StackOverflow post where the accepted answer was to increase the DTUs for a highly intensive IO workload, ...
Basic query to sample data for arbitrary fixed time intervals:
SELECT grid_time, message_date, message
FROM generate_series(timestamp '2021-01-21 01:00:00' -- always use unambiguous ISO format
, timestamp '2021-01-21 11:00:00'
, interval '73 minutes') grid_time -- arbitrary time interval
LEFT JOIN LATERAL (
Depending on the size of your dataset querying over a 'large' interval may not use an index (as your explain shows), there are several options I'd try:
Generate a series over your 'outer interval' (01hr - 11hr) splitted by your sampling interval (1m, 1hr, etc), idea:
query and group rdm.telemetry
where message_date >= a and