Technical :- Microsoft – SQL Server – Indexes – Repetitive Suggestion (2013-Nov)
Basically, the scenario is that you are writing SQL Scripts and you rightfully inspect it using “Display Estimated Query Plan”.
And, SQL Server suggests that you are missing indexes that make things run better and faster. And, so you look at the suggested index and it makes sense per the query. And, so you go create it.
Once the suggested index has been created, you re-visit the “Display Estimated Query Plan” and it comes back asking you to to re-create the same index.
I have touched on the same issue in the past. In a posting titled:
Technical – Microsoft – SQL Server – Missing Indexes – Helper Tools – Repetitive Suggestions
I rested on Paul Randal’s advice and went with applying Service Pack 2 to my MS SQL Server 2008/R2 RTM system.
But, now with SP2 in place, I saw the same problem a couple of weeks ago. In the next few paragraphs, I will talk about what I tried.
Here is a sample table definition
use [tempdb] go set nocount on go if object_id('dbo.promotionOffer') is null begin print 'creating table'; --drop table dbo.promotionOffer create table dbo.promotionOffer ( [id] int not null identity(1,1) primary key , [offerStartDate] datetime not null , [offerExpirationDate] datetime not null , [corporateID] int not null ) print 'created table'; end
Here is sample query:
declare @travelStartDate datetime declare @travelEndDate datetime set @travelStartDate = '4/1/2013' set @travelEndDate = '4/14/2013' select tblPromotionOffer.[id] , tblPromotionOffer.offerStartDate , tblPromotionOffer.offerExpirationDate , tblPromotionOffer.corporateID from dbo.promotionOffer tblPromotionOffer where tblPromotionOffer.[offerStartDate] < @travelStartDate and tblPromotionOffer.[offerExpirationDate] > @travelEndDate
In our real system, we added data through the traditional means. But, for our lab let us spoon feed data.
truncate table dbo.promotionOffer go insert into dbo.promotionOffer ( [offerStartDate] , [offerExpirationDate] , [corporateID] ) select dateadd(day, rand() * 1000, SYSDATETIME()) , dateadd(day, rand() * 1010 + 7, SYSDATETIME()) , 1 go 100000
And, so back to the problem.
When I issue the “Display Estimated Query Plan” button, we get a warning that an helpful index is not in place.
And, so I went and created it:
create index idx_OfferStartDate on dbo.[promotionOffer] ( [offerStartDate] )
Created Index with included columns
Happy that I now have an index in place, I went back and retried the “Display Estimated Query Plan” button.
But, I am still getting the same warning that an helpful index is not in place.
The only difference between the index in place and the one SQL is suggesting is that the one being suggested has a lot more included columns.
And, so I went ahead with dropping the existing index and re-created it. During this creation I ensured that I noted the included columns:
if exists ( select * from sys.indexes tblIndex where tblIndex.object_id = object_id('[dbo].[promotionOffer]') and tblIndex.name = 'idx_OfferStartDate' ) begin drop index [dbo].[promotionOffer].[idx_OfferStartDate] end go create index idx_OfferStartDate on dbo.[promotionOffer] ( [offerStartDate] ) includes ( [corporateID] )
Retried “Display Estimated Execution Plan”
Once again, ran the query through the “Display Estimated Execution Plan”, but no help.
Returned back to the Query and Determined if other indexes can help
So I returned back to the Query wanting to see if another index will help…
Here is the Query:
set @travelStartDate = '4/1/2013' set @travelEndDate = '4/14/2013' select tblPromotionOffer.offerStartDate , tblPromotionOffer.offerExpirationDate from dbo.promotionOffer tblPromotionOffer with (nolock) where tblPromotionOffer.[offerStartDate] < @travelStartDate and tblPromotionOffer.[offerExpirationDate] > @travelEndDate
So the columns that we are comparing against are offerStartDate and offerExpirationDate.
Let us create a corresponding index on offerExpirationDate.
Compare new index on the second qualifier column
We create that secondary index as:
if exists ( select * from sys.indexes tblIndex where tblIndex.object_id = object_id('[dbo].[promotionOffer]') and tblIndex.name = 'idx_OfferExpirationDate' ) begin drop index [dbo].[promotionOffer].[idx_OfferExpirationDate] end go create index idx_OfferExpirationDate on dbo.[promotionOffer] ( [offerExpirationDate] ) includes ( [corporateID] )
No more warnings
When we now issue “Display Estimated Query Plan”, we are glad no more warnings.
Now we have two indexes; it makes sense to compare them and review our workload to see if we need both indexes or one is more worthy than the other.
Compare Indexes using “DBCC SHOW_STATISTICS”
The “DBCC SHOW_STATISTICS” statement allows us to quickly review and compare our indexes.
We are able to do the following things:
- When the index’s statistics were last updated
- How many records the indexes have and how many of them were sampled (during the last Statistics update)
- How many buckets was used to group each the statistics histogram
- The index’s density ( 1/ number-of-unique values)
- And, for each index column how much if affected the index’s length and density — these two attributes play a role in the expensiveness of the index and whether it has enough variability
DBCC SHOW_STATISTICS ('dbo.promotionOffer', 'idx_OfferStartDate') with stat_header; DBCC SHOW_STATISTICS ('dbo.promotionOffer', 'idx_OfferExpirationDate') with stat_header;
Note, the scenario I painted above is not real. It is just intended to give as broad a guideline on our troubleshooting path as possible.
In summary, here are some things that are real:
- As “Display Estimated execution plan” only gives one index suggestion per run, it sometimes gets confused when it digs in its plan to give that one suggestion; it very well might re-pick one you have already addressed.
- We saw his on a Microsoft SQL Server – v2008 R2 with SP2 instance