August 13, 2017 at 7:38 am
Hi Team,
I have created one index sometime that includes one column (non primary) and the other column as Included column. It was working good. But later after 2 weeks I started seeing some time outs from the procedure again. While Investigation and to check further I added another index covering both the columns. So currently I have two indexes :
1. One column and other as included
2. Two columns as covering index. Initially this index had one column, I just added the other column which was included to other index
TEchnically are not both the indexes same?
I am little hesitant to delete either of the one as after creating the covering indexes the time out is stopped.
Please suggest with your opinion.
Thanks.
August 13, 2017 at 11:05 am
SQL-DBA-01 - Sunday, August 13, 2017 7:38 AMHi Team,I have created one index sometime that includes one column (non primary) and the other column as Included column. It was working good. But later after 2 weeks I started seeing some time outs from the procedure again. While Investigation and to check further I added another index covering both the columns. So currently I have two indexes :
1. One column and other as included
2. Two columns as covering index. Initially this index had one column, I just added the other column which was included to other indexTEchnically are not both the indexes same?
I am little hesitant to delete either of the one as after creating the covering indexes the time out is stopped.Please suggest with your opinion.
From the information you've given us, there's no way of knowing if they're the same index or not. If they have the same key columns and the same included columns, then they're the same. If not, then they aren't.
The SQL for the creation of the indexes would be the clearest way to be able to answer your question.
The real question is about the statistics on them. You said you created "one index sometime" but that doesn't say when. If the NCI was created, then SQL Server created statistics for it. As the data in the table changes, the statistics get to be out of date. SQL Server uses the statistics to determine how to best execute the query. If the statistics are out of date, then the plan for running the query might not be optimal. Next time, check the statistics before creating more NCIs.
August 13, 2017 at 5:13 pm
SQL-DBA-01 - Sunday, August 13, 2017 7:38 AMSo currently I have two indexes :1. One column and other as included
2. Two columns as covering index. Initially this index had one column, I just added the other column which was included to other index.
A covering index is one that contains all the columns needed for a query (it's covering for that query). The columns can be in the key, or in the include.
Could you be a little clearer about what indexes you have?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply