0

Version: Microsoft SQL Azure (RTM) - 12.0.2000.8 (Azure Managed Instance)

I'm working with a table (ActivityAttributes) that has ~190Mn rows. I've created an Index as below.

CREATE NONCLUSTERED INDEX [IX_ActivityAttributes_namevalue_new] ON [dbo].[ActivityAttributes]
(
    [Activity_id] ASC
)
INCLUDE([name],[value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

And my query is

select
  name,
  value
from ActivityAttributes
where value = 'Apps'
  and [name] = 'New Status'

And my table looks like this,

name Activity_id value Id
New Status 140737 Member 7051743
New Status 1512010 Apps 705211747
New Status 1595147 Apps 705112750

The execution plan I get for this query is, enter image description here

My problem is why it does Index Scans even though I used the included fields in the predicate.

Thank you in advance.

Improve this question
1
  • 1
    Heads up for future reference, SQL Server is not the same as Azure SQL Managed Instance (I fixed your title and tags accordingly). Also, please don't use screenshots to show the execution plan, as it doesn't show all the information that the plan contains. It's better to upload the plan to Paste The Plan and add the link to it in your Post instead. Commented Mar 22, 2023 at 12:22

1 Answer 1

Reset to default
1

Seeks only happen on Key fields [Activity_Id], Include (covering) columns are used to minimise Key/Rid lookups.

This index will give you a Seek (Range Seek because it's non-unique):

CREATE NONCLUSTERED INDEX [IX_ActivityAttributes_namevalue_new] ON [dbo].[ActivityAttributes]
(
   [name] ASC
  ,[value] ASC
)
INCLUDE( [Activity_id]) 
GO

The order of the Key columns is important. Whichever column has the most unique values should be first. However if it's an Inequality i.e. [Date] >= xxx then any key columns after the [Date] field will be not be seeked (only a Residual Predicate).

Improve this answer
0

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.