I've been experiencing a longstanding issue where SSDT will think a table index in my Azure SQL database doesn't exist, when it actually does.

After the past few hours of digging through XEvent traces and stepping-through SSDT disassembly I found out the reason is because SSDT looks for indexes with this query (repeated below for googleability).

The query has the predicate [i].[auto_created] = 0.

I want to keep the index rather than recreate it - is there any way for me to set sys.indexes.auto_created = 0 for a particular index somehow?

UPDATE: I just found this thread, looks like I'm not alone, but I shouldn't expect any changes to SSDT either, *grumble*

UPDATE2: Curiously, using WITH AUTO_CREATED = ON is a valid option for CREATE INDEX on Azure SQL but not for ALTER INDEX, *more grumbling*: when I tried it I got this error:

ALTER INDEX IX_StarTrekTngBestMoments ON dbo.GeneRoddenberryFanClub SET ( AUTO_CREATED = OFF );

Msg 155, Level 15, State 1, Line 1

'AUTO_CREATED' is not a recognized ALTER INDEX option.

Backstory for the curious:

  • About 2-3 years ago the Azure SQL Performance Analyzer automatically created an index on one of my tables.
    • The kind of indexes with a name like nci_wi_TableName_ABCDEF....
  • When the Azure SQL Performance Analyzer creates an index, view, or other object it sets the auto_created flag to 1.
  • Visual Studio's SQL Server Design Tools (SSDT, aka *.sqlproj) always excludes objects with auto_created <> 0, which mean SSDT didn't see my index.
  • You would think that wouldn't be a problem because the index wasn't in my project in the first place, so no conflict, right?
    • Well, no - at the time I used SSMS, not SSDT, to copy the INDEX's definition into my SSDT project - by using the "Script index as CREATE to..." menu and copying+pasting the SQL into my SSDT table file. And ever since then I've been having the conflicts with SSDT vs. my Azure SQL database - while also not having any problems with my on-prem and local dev versions because SSDT created those without the auto_created flag.
  • I filed a support ticket with Azure support over this in January 2021 - the support request moved very slowly, but by March 2021 they told me they "will be sending out the action plan shortly" (whatever that means), but I haven't heard anything since... Shame, Microsoft, Shame.

I was hoping there might be an option in SSDT for it to not ignore auto_created objects, but unfortunately the SQL query it uses to enumerate indexes in the database has the predicate term hard-coded:

enter image description here

    SCHEMA_NAME([o].[schema_id]) AS [SchemaName]
   ,[i].[object_id]        AS [ColumnSourceId]
   ,[o].[name]             AS [ColumnSourceName]
   ,[o].[type]             AS [ColumnSourceType]
   ,[i].[index_id]         AS [IndexId]
   ,[i].[name]             AS [IndexName]
   ,[f].[type]             AS [DataspaceType]
   ,[f].[data_space_id]    AS [DataspaceId]
   ,[f].[name]             AS [DataspaceName]
   ,CASE WHEN exists(SELECT 1 FROM [sys].[columns] AS [c] WITH (NOLOCK) WHERE [c].[object_id] = [o].[object_id] AND  [c].[is_filestream] = 1) THEN
        END  AS [FileStreamId]
   ,[ds].[name]            AS [FileStreamName]
   ,[ds].[type]            AS [FileStreamType]   
   ,[i].[fill_factor]      AS [FillFactor]    
   ,CONVERT(bit, CASE [i].[type] WHEN 1 THEN 1 WHEN 5 THEN 1 ELSE 0 END) 
                           AS [IsClustered]
   ,[i].[is_unique]        AS [IsUnique]
   ,[i].[is_padded]        AS [IsPadded]
   ,[i].[ignore_dup_key]   AS [DoIgnoreDuplicateKey]
   ,[t].[no_recompute]     AS [NoRecomputeStatistics]
   ,[t].[is_incremental]   AS [DoIncrementalStatistics]
   ,[i].[allow_row_locks]  AS [DoAllowRowLocks]
   ,[i].[allow_page_locks] AS [DoAllowPageLocks]
   ,[i].[is_disabled]      AS [IsDisabled]
                           AS [Predicate]
   ,[i].[compression_delay] AS [CompressionDelay]
   ,CONVERT(bit, ISNULL(INDEXPROPERTY([i].[object_id], [i].[name], N'IsOptimizedForSequentialKey'), 0)) AS [DoOptimizeForSequentialKey]
   ,CONVERT(bit, CASE WHEN [ti].[data_space_id] <> [i].[data_space_id] THEN 0 ELSE 1 END)
                           AS [EqualsParentDataSpace]
   ,[i].[type]             AS [IndexType]
   ,[i].[auto_created]     AS [AutoCreated]
  ,CONVERT(BIT, CASE WHEN [hi].[object_id] IS NULL THEN 0 ELSE 1 END) AS [IsHash]
   ,[hi].[bucket_count] AS [BucketCount]
    [sys].[indexes] AS [i] WITH (NOLOCK)
    INNER JOIN [sys].[objects]           AS [o]  WITH (NOLOCK) ON [i].[object_id] = [o].[object_id]
    LEFT  JOIN [sys].[data_spaces]       AS [f]  WITH (NOLOCK) ON [i].[data_space_id] = [f].[data_space_id]
    LEFT  JOIN [sys].[stats]             AS [t]  WITH (NOLOCK) ON [t].[object_id] = [i].[object_id] AND [t].[name] = [i].[name]
    LEFT  JOIN [sys].[tables]            AS [ta] WITH (NOLOCK) ON [ta].[object_id] = [i].[object_id]
    LEFT  JOIN [sys].[data_spaces]       AS [ds] WITH (NOLOCK) ON [ds].[data_space_id] = [ta].[filestream_data_space_id]
    LEFT  JOIN (SELECT * FROM [sys].[indexes] WITH (NOLOCK) WHERE [index_id] < 2) AS [ti] ON [o].[object_id] = [ti].[object_id]
    LEFT OUTER JOIN [sys].[hash_indexes] AS [hi] WITH (NOLOCK) ON [hi].[object_id] = [i].[object_id] AND [hi].[index_id] = [i].[index_id]
    ([o].[type] = N'U' OR [o].[type] = N'V')
    AND [i].[is_primary_key] = 0
    AND [i].[is_unique_constraint] = 0
    AND [i].[is_hypothetical] = 0
    AND [i].[name] IS NOT NULL
    AND [i].[auto_created] = 0
    AND ([o].[is_ms_shipped] = 0 AND NOT EXISTS (SELECT *
                                        FROM [sys].[extended_properties]
                                        WHERE     [major_id] = [o].[object_id]
                                              AND [minor_id] = 0
                                              AND [class] = 1
                                              AND [name] = N'microsoft_database_tools_support'
                                       ))) indexBase
WHERE [IndexType] NOT IN (3, 4, 5, 6)

Not ideal, but you could drop and recreate the index(es). Obviously this could take some time if the table is large so you may have to schedule it for a maintenance period if this is a production application. And if for any reason you want to keep the flag afterwards you'll need to drop and recreate again to reset it.

As suggested by AMtwo in comments below: you could create the replacement index first, then drop the one with the flag and rename the new one (renaming is important if the original index might be referred to in any query hints). This will take more space temporarily, but won't leave you with a period while there is no index covering those columns which could be important if you are working with an active DB rather than doing this in a maintenance period.

  • Yeah, this is what I ended-up doing - at least with Azure SQL I was able to crank the pricing-tier up to eye-watering levels for the ~15 minutes it took to reindex. Still, I'd have preferred to have a way to reset the auto_created flag.
    – Dai
    Jun 23 '21 at 14:09
  • I don't have a SQL DB handy to check on... But can you use CREATE INDEX....WITH (DROP_EXISTING=ON,AUTO_CREATED=OFF) to avoid separate create & drops?
    – AMtwo
    Jun 23 '21 at 17:55
  • Also, space-permitting, I'd suggest creating a new near-duplicate index (with a different name) first, then dropping the old one. To avoid being index-less for an interim period. It saves some heartache during the whole process.
    – AMtwo
    Jun 23 '21 at 17:56
  • @AMtwo - creating the new index first is a good idea, though make sure you rename it after dropping the original in case the existing name is referred to in any index hints. Jun 24 '21 at 9:27
  • 1
    @Dai - be careful with Azure SQL sizing in production, there can be a brief outage (new connections fail for a second or two, active transactions and held connections dropped) as the switch happens. See docs.microsoft.com/en-us/azure/azure-sql/database/… for reference. Depending on your app(s)'s DB error handling this may not be an issue, but most don't account for this sort fo blip so your end-users could see exceptions. Jun 24 '21 at 9:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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