July 2, 2007 at 7:43 am
Hi All,
I want to know all those columns for which I have to create index , for this there is dynamic management object " SELECT * FROM sys.dm_db_missing_index_details", this is returning columns which required indexing for that query which is executed for a database.
But I am not able to use this for already executed stored procedure, can you please tell me how I can use "sys.dm_db_missing_index_details" this for Stored Procedures.
Thanks in advance.
July 2, 2007 at 2:20 pm
You are not able to use for already executed stored procedure ? As far I as know, these views display missing indexes for any form of SQL - stored procedure, scripts, etc.
Did you run these views after executing a specific stored procedure and views displayed nothing ? Fine ! It sound like you don't have missing indexes. In this case maybe it would make sence to run the view selecting redundant indexes.
July 11, 2007 at 6:58 am
the views return data regardless of the method or source. You should not blindly add recommendations but research carefully.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 12, 2007 at 6:25 am
SELECT TOP 100 'CREATE NONCLUSTERED INDEX IX1_' + object_name(c.object_id) + left(cast(newid() as varchar(500)),5) + char(10)
+ ' on ' + object_name(c.object_id)
+ '('
+ case when c.equality_columns is not null and c.inequality_columns is not null then c.equality_columns + ',' + c.inequality_columns
when c.equality_columns is not null and c.inequality_columns is null then c.equality_columns
when c.inequality_columns is not null then c.inequality_columns
end
+ ')' + char(10)
+ case when c.included_columns is not null then 'Include (' + c.included_columns + ')'
else ''
end as includes
FROM sys.dm_db_missing_index_group_stats a
inner join sys.dm_db_missing_index_groups b
on a.group_handle = b.index_group_handle
inner join sys.dm_db_missing_index_details c
on c.index_handle = b.index_handle
where db_name(database_id) = 'databasename'
and equality_columns is not null
ORDER BY a.avg_total_user_cost * a.avg_user_impact * (a.user_seeks + a.user_scans)DESC
July 18, 2007 at 9:45 am
from what I've read the DMV gives the best results for 'reading' data, is there any way to determine (other than doing) if the include columns could make matters worse?
select
* from
(select user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage,
migs.* from sys.dm_db_missing_index_group_stats migs) as migs_adv
inner join sys.dm_db_missing_index_groups as mig on migs_adv.group_handle=
mig.index_group_handle
inner join sys.dm_db_missing_index_details as mid on mig.index_handle=mid.index_handle
order by migs_adv.index_advantage
I ran the previous msg script and got back 8 possible indexes to create for a particular database. Running the above shows the 'index_advantage' to be <1000, whereas the documentation I gleamed it from (MCTS study guide) says that >5000 evaluate, >10000 create the index.
I suppose I could do a baseline of performance before and after the index creation I was just wondering if there was some way of predetermining it.
thanks,
Chris
July 19, 2007 at 2:47 am
you illustrate my point, just adding indexes without knowing why or understanding is dangerous.
Use a test system, compare performance, io etc. etc.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply