Suggested indexes

Question

I am new to indexing in general and have just started learning about query plans etc. I am working on this one specific query that keeps advising me to create the following index:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Company] ([CreatedById],[TenantId],[CreatedDate])

I already have the following index created:

CREATE NONCLUSTERED INDEX [IX_Company_Tenant] 
ON [dbo].[Company] ([TenantId],[UserId],[CreatedDate])

What i am wondering is, will i get the same result in speed increase if i changed the already created index to be:

CREATE NONCLUSTERED INDEX [IX_Company_Tenant] 
ON [dbo].[Company] ([TenantId],[UserId],[CreatedDate], [CreatedByID])

Instead of creating the index that is being suggested by the query store?

EDIT

Even after applying the third index:

CREATE NONCLUSTERED INDEX [IX_Company_Tenant] 
ON [dbo].[Company] ([TenantId],[UserId],[CreatedDate], [CreatedByID])

I still get the same suggested index.

Answer 1

You have to create the index with the exact column order suggested (or with a technical almost equivalent column order).
Column order sometimes/often matters for usability and efficiency of an index for a specific query.

In above case it is important to have CreatedDate behind CreatedByID and TenantId or even directly behind these fields (so no UserId in between) to allow SQL Server to diretly jump to the index blocks for CreatedByID and TenantId and then finding the requested CreatedDate start value within the consecutive orderes entries for that column and read all the row references one after the other scrolling through the index until it reaches the requested CreatedDate end value (and increment counter for each row found).

Opposed to that, think about Your adjusted index.
SQL server can find the area for requested TenantId. But then it has to read all the entries for that TenantId and check whether CreateDate is in range and if it is, it has to check whether CreatedByID satisfies Your query conditions. These can be quite many rows where these checks are needed, depending on table size and data distribution.

Answer 2

First up, be sure that you're dumping the cached plan for that query in between creating indexes. The nuclear option is to do a cache flush ( DBCC FREEPROCCACHE ) but you can also hunt down the plan handle and just do the specific one. I'd also recommend dropping the non-clustered indexes inbetween experimenting with them - if you've got three indexes on this table all trying to speed up the same query it's not the greatest situation.

When thinking about creating indexes, keep in mind that the index creation suggestions that SQL provides you with are of limited value. SQL generally gets it right when saying "I could do a better job with an index here", but it really doesn't have a good way to tell you the exact index that will fit your need the best.

Most of the time it will give you a suboptimal column order. Many times it will miss useful columns, or tell you to put too many in.

In general, you want have a solid understanding of the query that is causing the need for an index and then to create an index to fit that query. Most queries take the form of homing in on some specific set of records, so the columns in the index are put in order of most able to home in on the desired data to least able. In other words, if one column can eliminate 95% of all possible results and another column can eliminate 20% of the results, put the 95% column first.

For a simple example, if I have a query that selects a small (1%) subset of records by date and also eliminates some of the remaining records via a flag that 50% of them have, I would be much better off to order my index columns by date first and then the flag second. Doing it the other way around will cause me to perform more work for the same end result.

Looking at your query while not knowing your data, I'd guess that a pretty good index for it will be:

CREATE NONCLUSTERED INDEX [IX_Company_Tenant] 
ON [dbo].[Company] ([CreatedDate],[CreatedByID],[TenantId])

Here's a good blog article that explains it more in-depth - https://www.brentozar.com/archive/2019/10/how-to-think-like-the-sql-server-engine-adding-a-nonclustered-index/