Creating a new index or modifying an existing index which is best option

Hi All,

I have received a request from a customer having issues when he is executing a function, when i checked the details and executed the function by using the include actual execution plan option, there was a missing index on of the tables named web_function which has columns like projectid, linkid etc..and index was being recommended on both the columns as below from execution plan, but the issue here is there is already a non-clustered index on column projectid.

How should i proceed further on this, whether i have to modify an existing index and add column linkid to the existing index or create a new index, which of it is the best way to proceed?

Please advise.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[web_function] ([projectid],[linkid])

Thanks in Advance.

Regards, Kranthi

May 25th, 2015 2:15pm

well, what index did the query optimizer suggest - in other words, in the suggestion it made, did it have both the columns in one index or just one column as part of the index( i would think both of them).

i would say, use the existing index and add the other column to this index. make sure your order of columns in the index is correct - ProjectId,LinkID

What is data type of LinkId Column and how frequently is this modified vs seeked.

I would say, as a starting point.. modify the current index and add LinkId to the current one..

and check the index 

select * from sys.dm_db_index_operational_stats(db_id(),null,null,null)

select * from sys.dm_db_index_usage_stats


Free Windows Admin Tool Kit Click here and download it now
May 25th, 2015 2:30pm

How should i proceed further on this, whether i have to modify an existing index and add column linkid to the existing index or create a new index, which of it is the best way to proceed?

That depends on the business critical queries. Can you post them?

Covering index: http://www.sqlusa.com/bestpractices/coveringindex/

May 25th, 2015 2:53pm

Hi Folks,

Thanks for your replies.

@Stan - yes as per the suggestion from query optimizer it had both the columns within a single index. The data type of linkid is int. As suggested I'll modify the existing index and see if it helps and if there is any issue then i'll go with the second option which is creating a new index which includes both the columns.

@Toth - I cannot post the queries online, sorry for that.

Free Windows Admin Tool Kit Click here and download it now
May 25th, 2015 3:01pm

@Toth - I cannot post the queries online, sorry for that.

You can, change the table name with dummy name. If optimizer is suggesting and index create and see if this makes execution fast. I have seen optimizer suggesting irrelevant index sometimes because  query written was poor
May 25th, 2015 3:19pm

Can you show us  the query the index was recommend on ? It is possible (depends on the query) that you would need to indexes...
Free Windows Admin Tool Kit Click here and download it now
May 26th, 2015 4:52am

Before you executed the function and included the actual execution plan , did you ensure all statistics were up to date?

You can find the last time the statistics were updated via this query: http://www.sqlserver-dba.com/2013/01/sql-server-find-last-time-statistics-updated-update-statistics.html

May 26th, 2015 5:40am

HI Jack,

I have verified stats are to date and auto-update-statistics option for the database is set to on.

Free Windows Admin Tool Kit Click here and download it now
May 28th, 2015 3:43am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics