Sql Azure stopped using index

Weirdest few hours using SQL Azure. We dropped the database from 50DTU to 20DTU and our CPU went through the roof. Turns out that one of our main indexes was simply no longer being used.

The index still existed. It had 27% fragmentation which I understand is not super-terrible, and any way shouldn’t stop SQL from using it. So, these are the things I tried (in order):

• Reorganized index – nothing.
• Rebuilt index – nothing.
• Dropped and recreated – nothing.
• Cleared proc cache (using ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE) – nothing. – – – Dropped and recreated with a different name – worked.

I didn’t take a screenshot of the execution plan when it was failing, but it was basically exactly the same as the execution plan below (of the final working index), except it did not include the usage of the index (circled) – ie. it was just doing the clustered index scan.

The final working query was:

CREATE NONCLUSTERED INDEX [IX_SyncDetail_SyncStatusID_EntityTypeID_ApiConnectionID] ON [client_2].[SyncDetail]
(
    [SyncStatusID] ASC,
    [EntityTypeID] ASC,
    [ApiConnectionID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

This is exactly the same as the original index (which was being ignored), except that a) the name is different; and b) the order of the columns used to be EntityTypeID, SyncStatusID, ApiConnectionID.

I want to stress again that the index was working fine before our database downgrade.

So – any ideas what happened?

Answer

You have a parameterized query. You are getting different plans based on the values being sniffed during compilation. The various actions you are doing are causing (re-)compilations and you are getting the same plan sometimes and a different plan other times. When you are getting a selective value sniffed, it is picking the seek plan. When you get a non-selective value sniffed, it picks the scan plan. So, downgrading the SLO will force a failover and thus flush the plan cache –> new compilation, new opportunity to sniff a value during compilation. When you free the procedure cache –> new compilation. When you drop or add an index, it forces a recompile for plans touching that table on next use.

You can read more about this behavior here: Blog post explaining parameter sniffing

You can also use the query store to see the history for query plans (it is on-by-default in SQL Azure). Here is a link explaining how you can use the query store. Note that with the query store you can easily force a specific query plan if you want only one plan to run through the SSMS UI.

The parameter sniffing space is one where we have desire to do some more work in the future to make this behavior more transparent/less surprising for customers but have not yet been able to do so. So, sorry this has been frustrating for you and I hope the workaround I’ve posted will get you unblocked. Rest assured we are very aware of the problem space and hope to do more here to make SQL better for workloads like this in the future.