2

I ran a query using the sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_group_stats to determine what are my options for missing indexes. After analyzing the data I determined that I needed to create a non-clustered index for Table A.

Looking at Table A, I noted that there are 55 dependencies (Tables, SPs, Views) and 23 objects that are dependent on this table. My question is, if i create this non-clustered index on this table does it affect the dependencies and dependents of this table?

Moreover, if yes it affects the dependencies, how do I even attempt fix the dependencies?

4
  • If by affect you mean if you can breake something by creating an index, I hardly think so. But if you mean if those objects can benefit from that index, yes. In fact thats the idea: to benefit the performance of as many objects as possible by the creation of the index. A view that has a predicate on a column of the index should perform faster, an SP that query the table of the index should perform better...
    – Ronaldo
    May 7, 2020 at 1:56
  • Thanks @Ronaldo, yes i meant affect it negatively. I've been looking for documentation somewhere which explains this scenario but can't seem to find any.
    – Raidenlee
    May 7, 2020 at 2:06
  • Ok. I think I can see one very specific situation in which an index can break something, but I'd like to test it before I say (I'll only be able to do it later). By now, could you post the create index DDL in order to get an answer that applies to your situation?
    – Ronaldo
    May 7, 2020 at 11:43
  • Oh it's not a special situation, was referring to my post when i said scenario. It just a typical table is a lot of dependencies and dependents linked to it.
    – Raidenlee
    May 7, 2020 at 13:16

3 Answers 3

2

Among the Available index types I could see only one kind of index that would be able to cause an error on an object that depends on the table and it's the Unique index.

According to the Unique Index Design Guidelines:

A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique.

It's an index that comes with a constraint that ensures the values are unique. Apart from that very specific situation, you don't have to worry about breaking a dependency for, as the doc says,

A nonclustered index is an index structure separate from the data stored in a table that reorders one or more selected columns. (emphasis mine)

As you can see, the nonclustered index is separeted from the table and doesn't alter the structure of the table itself, therefore, anything that uses that table shall not be negatively affected the way you're afraid of.

Here's a good start point: SQL Server Index Architecture and Design Guide


Lab: causing error on table dependency by creating an index

First, create a table that will have an object that depends on it:

CREATE TABLE MyTable(
    UserID INT NOT NULL IDENTITY PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Surname VARCHAR(50) NOT NULL
)

Now, crete an SP that is dependent on that table:

CREATE PROCEDURE InsertUser
@Name VARCHAR(50),
@Surname VARCHAR(50)
AS
BEGIN 
    INSERT INTO MyTable(Name, Surname)
    VALUES(@Name, @Surname);
END

Let's insert the user Paul Smith:

EXEC InsertUser 'Paul', 'Smith';

The procedure executes fine. Paul is a very common name and as it is now you could insert as many users named Paul as you wished. But you decided to create a Unique Index on the column Name:

CREATE UNIQUE INDEX AK_MyTable_Name ON MyTable (Name);

After that, you try to insert another user named Paul Jones:

EXEC InsertUser 'Paul', 'Jones';

But now you get an error:

Msg 2601, Level 14, State 1, Procedure InsertUser, Line 6 [Batch Start Line 17] Cannot insert duplicate key row in object 'dbo.MyTable' with unique index 'AK_MyTable_Name'. The duplicate key value is (Paul). The statement has been terminated.

The SP that depended on the table is now broken. That's the only situation I can imagine you could break an object that depends on a table by the creation of an index. If you're not creating a Unique Index now, you can relax and watch the performance of your new indexes.

5
  • Thank you. Great explanation too! I have one scenario, since SPs are pre-complied and the query plan is already set, then if i create an Index on a table does the SP recognize this new Index and use it to be more efficient?
    – Raidenlee
    May 8, 2020 at 15:22
  • 1
    @Raidenlee, I'm glad I could help! Unfortunately the existing SP might not start taking full advantage of a new index right away. Check Kendra Little's article Should I Run sp_recompile After I Create An Index? to better understand why.
    – Ronaldo
    May 8, 2020 at 15:40
  • 1
    To be clear Kendra's article isn't saying that the recompile is needed to get the new index taken advantage of. It is so that the stats for "new" and "old" aren't intermingled May 8, 2020 at 17:23
  • @MartinSmith, thanks for pointing that out. I didn't intend to say that one should always run a sp_recompile after the creation of an index, but I think I misinterpreted the main goal of that article. But your comment made me question what signals the SP to start using the new index. I couldn't match the creation of a new index to any of the reasons listed under the Recompiling Execution Plans section. Could you tell what it would be?
    – Ronaldo
    May 8, 2020 at 18:16
  • 2
    "Adding an index to a table or an indexed view." is mentioned as a schema change here learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/… May 8, 2020 at 19:14
4

When implementing filtered indexes, certain connection settings must be correct:

enter image description here

Otherwise

The Database Engine generates an error and rolls back INSERT, UPDATE, DELETE, or MERGE statements that change data in the index.

So if any of the dependent code does not have the above SET options correctly configured, adding a filtered index will break them. I unfortunately have had to learn the hard way to double check this when working on old systems with questionable stored procedure code :)

2
  • Yeah, I inadvertently broke a process that inserted to a table in the past by adding a filtered index too May 8, 2020 at 16:57
  • Agreed, i'll be creating non-clustered indexes with includes and definitely no filters for those that are legacy because tracing will take ages.
    – Raidenlee
    May 8, 2020 at 19:11
1

The very simple answer is no. However, before you create an index you must understand the workload, how many indexes are presently on the table, whether you can fulfill the new index my modifying an existing index etc.

Never create an index based on SQL Server missing index recommendations https://www.brentozar.com/archive/2017/08/missing-index-recommendations-arent-perfect/

As I mentioned in paragraph 1, you need to understand your workload and the benefit of the new index by analysing usage stats.

3
  • 2
    "Never create an index based on SQL Server missing index recommendations." Could you please elaborate on why should you never create index based on this recommendation and if not by these DMVs then how do you find missing index and create them? May 7, 2020 at 8:03
  • 1
    The missing index recommendations, first and foremost, are just that, recommendations. They are not hard truths that must be implemented. They are frequently wrong. Further, you'll note there is no correlation between the recommendation and any query. Is it a query that was run once and will never be run again? Then the recommendation is a waste of time. I generally never use them because of this. May 7, 2020 at 11:52
  • @user1716729 thank you but why is the answer no? Do you have any supporting documentation to support this? I been told that you need to recompile the schema Option(recompile) of the dependencies so that it can notice the change in Index. Also yes I have analysed the workload before using the optimizer recommendations.
    – Raidenlee
    May 7, 2020 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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