1

I have a simple table partitioned by Date for each day

so ranges are like ('2023-03-07 23:59:59.997', '2023-03-08 23:59:59.997') etc with about few millins records per each range

the table is as following

Id - PK
Message, nvarchar
RedirectId - INT
Date - datetime (partitioned by this exact column)

Common query scenario is deleting by date and querying by RedirectId AND Date

So I ended up creating index for it:

create index IX_Date_RedirectId on dbo.PendingMessages(date, RedirectId); 

When I am looking at executing plans for some query like

select * from dbo.PendingMessages
where Date > '2023-03-08 13:59:59.997' and Date < '2023-03-10 13:59:59.997'
and RedirectId = 2

It returns me about 1 million records and I see just a simple table scan with a very high (44) subtree cost. No non-clustered index seek whatsoever

But when I look up with a similar query but with some unique RedirectId that I added (only 1 row will be returned)

select * from dbo.PendingMessages
    where Date > '2023-03-08 13:59:59.997' and Date < '2023-03-10 13:59:59.997'
    and RedirectId = 2222 (only 1 record with such RedirectId)

I see that it occasionally uses the index that I created to find the records but in such case it says that the query memory grant detected

What am I doing wrong here. Could someone advice?

2
  • Your question is not answerable without seeing the full table defintion (including the partitioning scheme) and please share the query plan via pastetheplan.com Oct 26, 2022 at 19:03
  • You probably want to add INCLUDE columns to your non-clustered index. Oct 26, 2022 at 19:04

1 Answer 1

1

It returns me about 1 million records and I see just a simple table scan...No non-clustered index seek whatsoever.

But when I look up with a similar query but with some unique RedirectId that I added (only 1 row will be returned) I see that it occasionally uses the index that I created to find the records.

The difference between when you'll see an Index Scan vs an Index Seek for the same query (but different predicate values) usually has to do with the tipping point. The tipping point is based on the cardinality of rows being returned from the index based on those predicate values.

Generally speaking, the more rows being returned (relative to the total rows in the table) the higher chance of hitting the tipping point where the SQL Server Engine thinks it's more efficient to just scan the entire index rather than do a seek against it. Sometimes this is more efficient of an operation (again depending on how many rows are actually being returned and the purpose of the query).

What am I doing wrong here. Could someone advice?

Also, it's generally better to lead your indexes with the most selective columns or columns that your queries are doing equality searches on. So I'd recommend trying the index on (RedirectId, date) instead, to see if it improves the performance you're seeing in both cases.

Aside from that advice, providing your actual execution plan via Paste The Plan is always a good idea for performance questions.


Also per AMtwo on the importance of partition alignment:

It's also important to note that the CREATE INDEX from OP is not partition-aligned. Indexes can have different partition schemes from the table itself (Clustered Index), or from other indexes. I don't expect that partitioning will make a significant performance difference here, but just worth noting that the nonclustered index is NOT partitioned.

1
  • 2
    It's also important to note that the CREATE INDEX from OP is not partition-aligned. Indexes can have different partition schemes from the table itself (Clustered Index), or from other indexes. I don't expect that partitioning will make a significant performance difference here, but just worth noting that the nonclustered index is NOT partitioned.
    – AMtwo
    Oct 25, 2022 at 23:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.