2

Refer to this SQL Fiddle and click the "Execution Plan" link at the bottom left for context.

As you can see, the CountryGroup table has a single row with CountryId = 1. I then update the CountryGroup table with a statement that should effectively be a no-op (setting CountryId = 1 when it's already 1). This triggers the rebuild of the clustered index UX_CountryGroupView_Id on the view, which is a relatively expensive operation (60% of the query cost). In the database I'm actually working with, the actual cost is a lot higher, hence this question.

Does SQL Server only check @@rowcount when determining if it should update indexes, or does/can it look at the data to see if it's changed and whether it should expend the effort of rebuilding? It seems that the former is the case, but is it possible to specify the latter behaviour, or control what SQL Server does in this circumstance?

Please note, I have no control over the SQL updatestatement, which is being autogenerated.

Ian Kemp
  • 28,293
  • 19
  • 112
  • 138
  • It would be an exceptionally "narrow" place for an optimization to be applied - by far the *vast* majority of updates will require some index maintenance. What is optimized is that the system shouldn't have to rebuild the entire index though - the various restrictions on indexed views exist so that maintenance is as cheap as possible. – Damien_The_Unbeliever Nov 27 '15 at 13:15
  • It is not clear that this update actually causes 60% runtime cost increase. Also, physically it might not update anything. For normal indexes it does not (look at the t-log). But it needs to process the row to somehow take a lock. – usr Nov 27 '15 at 13:18
  • @usr In my real-world scenario, with actual tables with actual data, the `update` takes under a second if the `CountryId` column is excluded, and well over a minute when that column is included. So 60% is actually on the conservative side. But regardless of that, my question/issue isn't around the time taken, it's around **how to prevent** that expensive side-effect if possible. – Ian Kemp Nov 27 '15 at 14:09
  • 3
    Would `WHERE CountryId <> 1` work? – usr Nov 27 '15 at 14:18
  • What do you mean by "if the CountryID column is excluded"? In your example update, that's all you're affecting so there's no way to exclude it. – Ben Thul Nov 27 '15 at 18:45
  • @BenThul In the actual query there are a bunch of other columns being updated as well as CountryID. I've updated the fiddle to reflect this. – Ian Kemp Nov 30 '15 at 08:21
  • @usr Unfortunately not - this is SQL that's being autogenerated by a homegrown Entity Framework clone (I didn't write it, but I do have to use it), so I don't have any control over what it generates. Updated question to reflect this. – Ian Kemp Nov 30 '15 at 08:23

0 Answers0