4

I was wondering if the SQL Server 2017 (or SQL Azure) automatic index tuning feature would drop any unique index on a table, since that would be very bad for data consistency.

Daniele Giallonardo
  • 73
  • 4
  • how can I make SQL Server decide wheter to drop or not an index? it's up to the engine's internal statistics, I suppose....do you have any suggestions about the steps I could take to make this happen? – Daniele Giallonardo Aug 03 '18 at 08:19
  • I sincerely doubt they'd build a feature that would change the *correctness* of the database structure. – Damien_The_Unbeliever Aug 03 '18 at 08:21
  • me too, but I've found no mention about this on the documentation....it's just something I want to be sure about, before I enable it in a production environment..... – Daniele Giallonardo Aug 03 '18 at 08:24
  • 1
    This is really only an issue for filtered unique indexes. A non-filtered unique index could (and probably should) be implemented as a constraint as well, and the indexes backing those can't be dropped without explicitly dropping the constraint (which no sane index tuning mechanism would do -- if only because it's more work to generate those statements, and you couldn't do it accidentally). – Jeroen Mostert Aug 03 '18 at 08:26
  • I'm not talking about a Unique constraint backed by an index, I'm talking about a unique index created by a "CREATE UNIQUE INDEX" statement, and droppable by a simple "DROP INDEX" statement. I know that the query optimizer treats them the same way, but there is no additional effort in dropping a unique index instead of a non-unique index – Daniele Giallonardo Aug 03 '18 at 08:32
  • 1
    Yes -- my point is that if you're really concerned about this, and the index isn't just there to speed up queries but to enforce uniqueness, you probably should use a constraint so the question of whether or not tuning will drop unique indexes doesn't come up in the first place. (Notwithstanding that I totally agree that tuning should leave unique indexes alone, since it can't know if they're used for integrity.) – Jeroen Mostert Aug 03 '18 at 08:34
  • Well, it is [documented that](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-unique-indexes?view=sql-server-2017) "There are no significant differences between creating a UNIQUE constraint and creating a unique index that is independent of a constraint" – Damien_The_Unbeliever Aug 03 '18 at 08:36
  • @Damien_The_Unbeliever: yeahhhh... I don't think that was written explicitly with index tuning in mind, though. Of course the statement itself is true in the sense that the engine doesn't care, and index tuning *should*. – Jeroen Mostert Aug 03 '18 at 08:38
  • I agree with you, a good practice would be using unique constraints to ensure integrity and using indexes only for performance....but what if I have a legacy DB with hundreds of unique indexes? The question is, am I safe just turning on the automatic index tuning feature? – Daniele Giallonardo Aug 03 '18 at 08:38
  • Ultimately, the problem with this sort of question is that, unless the person writing the documentation says to themselves "Are we doing something insane like X?", finds the answer, and documents it, you often don't get answers to whether the system *doesn't* do unlikely things. – Damien_The_Unbeliever Aug 03 '18 at 08:40
  • 1
    Remember, you can always have index tuning produce only recommendations, that you then act on yourself. Personally, I'm sufficiently paranoid and not sufficiently overworked that I'd always use it that way, and not on full auto. Otherwise, this question can only really be answered by an MS engineer who says "of course I built it so it doesn't drop unique indexes, I'm not *crazy*" or "whoops, um, could you open a support issue real quick?" So... let's see if one pops up. – Jeroen Mostert Aug 03 '18 at 08:41
  • 1
    It is documented somewhere that it doesn't. Can't remember where though. So only Q is whether this applies to duplicates. I did some testing on this some months ago. Simply create a database with duplicate indexes, and run a workload against it and wait for up to a week. The results from that were that it recommends dropping unique indexes if they are also duplicate indexes (identical key columns, included columns, partition scheme and filter) but didn't automatically drop them. – Martin Smith Aug 03 '18 at 08:42
  • Let's say I don't have duplicate indexes, I couldn't still be sure, even after a week of a running workload, that I can reproduce a scenario in which SQL Server decides (or not) to drop the unique index. – Daniele Giallonardo Aug 03 '18 at 08:45
  • Indexes dropped for reasons other than being a duplicate you would need to [wait 93 days](https://learn.microsoft.com/en-us/azure/sql-database/sql-database-advisor#drop-index-recommendations) before it concludes they are unused and can be dropped – Martin Smith Aug 03 '18 at 08:46
  • Well, I have production environments older than 93 days :-D – Daniele Giallonardo Aug 03 '18 at 08:49
  • I presume that's 93 days from enabling the feature. Not sure! – Martin Smith Aug 03 '18 at 08:51
  • It's quite the same....let's say I enable the feature and then, after 93 days, a unique index gets dropped.....I would not be happy at all..... – Daniele Giallonardo Aug 03 '18 at 09:00
  • As I said above it **is** documented somewere that it doesn't. So only Q would be whether it applies to dupes (as really there is no good reason not to drop these). I'm not going to scour the internet for you to try and find where I read that though. I'll leave that to you. – Martin Smith Aug 03 '18 at 09:01
  • 1
    I'm curious about this as well. I've done testing that suggests it won't, however, I would take that with a grain of salt the size of a house. I'm in touch with Microsoft to see if I can get a definitive answer. – Grant Fritchey Aug 03 '18 at 11:10

1 Answers1

3

About the "Drop Index" option provided by Automatic Tuning in Azure SQL Database, unique indexes introduce a constraint that defines the table behavior, dropping them would change this behavior. As Automatic Tuning is designed to respects user defined constraints, unique indexes are not dropped.

Hope this helps.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30