Technical :- Microsoft – SQL Server – Indexes – Repetitive Suggestion (2013-Nov)

Technical :- Microsoft – SQL Server – Indexes – Repetitive Suggestion (2013-Nov)

Introduction

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.

Background

I have touched on the same issue in the past.  In a posting titled:

Technical – Microsoft – SQL Server – Missing Indexes – Helper Tools – Repetitive Suggestions

https://danieladeniji.wordpress.com/2013/06/14/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.

Same Problem

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.

Table Definition

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

Sample Query

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

Added Data

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

Created Index

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.

Compare Indexes

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

Syntax



DBCC SHOW_STATISTICS ('dbo.promotionOffer', 'idx_OfferStartDate') 
     with stat_header;

DBCC SHOW_STATISTICS ('dbo.promotionOffer', 'idx_OfferExpirationDate') 
     with stat_header;

Output:

DBCC - ShowStatistics -with 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.

Take Away

In summary, here are some things that are real:

  1. 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.
  2. We saw his on a Microsoft SQL Server – v2008 R2 with SP2 instance

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s