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.

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.
    – J.D.
    Mar 22, 2023 at 12:22

1 Answer 1

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).

0

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.