Creating recommended Indexes

Question

Hi all,

I am using this query to find suggested indexes.

SELECT sys.objects.name

, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact

, 'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL

 THEN ''

 ELSE CASE WHEN mid.equality_columns IS NULL

  THEN ''

 ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL

 THEN ''

 ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement

, mid.equality_columns

, mid.inequality_columns

, mid.included_columns

, GETDATE() as 'DateTimeStamp'

 FROM sys.dm_db_missing_index_group_stats AS migs

 INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle

 INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()

 INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID

 WHERE (migs.group_handle IN

 (

 SELECT TOP (500) group_handle

 FROM sys.dm_db_missing_index_group_stats WITH (nolock)

 ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))

 AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1

 order by Impact desc

My questions are:

1) As per impact >50,000-- Should i create index on tables?

2) As recommended by query, create non-clustered index with column A and B. What if column A already exist in another index? Should i just add column B in that index or its better to create a seperate index with column A & B. Before anyone ask, we are using 2005 version with Axapta 3.0 as front end. and we have to create indexes through axapta(higher preference because of its synchronisation) and it doesnt have any feature called INCLUDED columns.

Thanks,

---

Dinkar Chalotra

Answer 1

People write about this books. Do not rely blindly on sys.dm_db_missing_index_details , identify long running queries and try optimize them... See Greg's stored procedure to see index usage

create procedure usp_helpindexusagestats
  @tablename varchar(1000)
as

set nocount on

if object_id('tempdb..#helpindex') > 0 drop table #helpindex

create table #helpindex (
   index_name varchar (1000) not null primary key
 , index_description varchar (1000) null
 , index_keys varchar (1000) null
)

insert #helpindex
exec sp_helpindex @tablename

alter table #helpindex add inccols varchar(1000) null

declare cr cursor for
select si.name, sc.name
from sysobjects so
join sysindexes si on so.id = si.id
join sys.index_columns ic on si.id = ic.object_id and si.indid = ic.index_id
join sys.columns sc on ic.object_id = sc.object_id and ic.column_id = sc.column_id
where so.xtype = 'U'
  and so.name = @tablename
  and ic.is_included_column = 1
order by si.name, ic.index_column_id

declare @siname varchar(1000), @scname varchar(1000)

open cr

fetch next from cr into @siname, @scname

while @@fetch_status = 0
 begin

  update #helpindex set inccols = isnull(inccols , '') + @scname + ', ' where index_name = @siname

  fetch next from cr into @siname, @scname
 end

update #helpindex set inccols = left(inccols, datalength(inccols) - 2)
where right(inccols, 2) = ', '

close cr
deallocate cr

select hi.index_name, hi.index_description, hi.index_keys, hi.inccols as included_columns, ius.index_id, user_seeks, user_scans, user_lookups, user_updates
, last_user_seek, last_user_scan, last_user_lookup
from sys.dm_db_index_usage_stats ius
join sysindexes si on ius.object_id = si.id and ius.index_id = si.indid
join sysobjects so on si.id = so.id
join #helpindex hi on si.name = hi.index_name collate database_default
where ius.database_id = db_id()
  and so.name = @tablename

drop table #helpindex
go


exec usp_helpindexusagestats 'tblname'

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Thanks Uri,

Yea Best practices are not always best, depends upon what you got.

No disrespect for Greg's SP but i have seen significant improvement after implementing one of the suggested indexes by query i am using.

Moreover, any idea about my 2nd question while creating indexe?

Much appreciated,

---

Dinkar Chalotra

Answer 3

Again ,  you need to test it... It is possible to 'cover' SELECT statement to add all columns as INCLUDE to the index for the WHERE condition...Also  it is possible that depends on the query you will have to create two indexes to address WHERE condition....

Take a look into execution plan to see how sql server performs that query

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

Hi Uri,

Hope this will help.

Let me know if you need more info.

Thanks,

---

Dinkar Chalotra

Answer 5

Looks like a query that perform aggregation...How does it looks like? On what columns  you have indexes?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 6

yea query is looking for max value on an email subscription.

(@p1 varchar(1000), @p2 VARCHAR(1000) ) select max(a.subscription) from table where ((columnA=@p1) and (columnB=@P2)) OPTION (FAST 2)

as this is a big table and i have indexes on most of the columns except on email  column.

regards,

---

Dinkar Chalotra

Answer 7

(@p1 varchar(1000), @p2 VARCHAR(1000) ) select max(a.subscription) from table where ((columnA=@p1) and (columnB=@P2)) OPTION (FAST 2)

I would expect a composite non-clustered on columnA, ColumnB and subscription to be optimal for this particular query.

I noticed that the execution plan shows this query is part of a cursor.  For performance reasons, it's usually best to avoid cursors and perform set-based operations instead but we can't make specific suggestions without seeing the entire code.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 8

Hi Dan

I also noticed that iterator in EP but have never seen it before. If the OP executes the query within a cursor should  we get the EP for this query only and not as a part of cursor, just wonder...?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 9

Hi Dan

I also noticed that iterator in EP but have never seen it before. If the OP executes the query within a cursor should  we get the EP for this query only and not as a part of cursor, just wonder...?

It looks to me like the execution plan is the DECLARE CURSOR statement so it includes the execution plan of the underlying query as well as the cursor stuff (Fast Forward and Fetch Query operators).  If the first columns of the clustered index are on anything other than columnA and columnB, the clustered index seek is suboptimal for the task at hand.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 10

Yep,make sense , thanks

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 11

Dan,

I can provide you a little example about code.

Like, If someone have subscribed for the newsletter or email or not. Checking whether checkboxes( respectively) are ticked or not.

And yea -as i said before- every creation or deletion have to be done through Axapta because if anything extra exist on sql side, will be wipped off after synchronisation(axapta).

Thanks,

---

Dinkar Chalotra

Answer 12

I understand your query and index tuning options are limited with third party applications.  Without the option to create an index with included columns, your other option is to add other columns to the end if the index key.  Ideally, the execution plan should show a seek predicate using columns specified in JOIN and WHERE clauses and the remaining columns used to cover the query (unless the clustered index is used).

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/