March 23, 2009 at 11:43 am
I have created a script which captures missing indexes on 2005 sql server. after capturing missing indexes, I created those indexes on production server and started getting timeout error. I am not sure if I am getting timeout error becasue of that but after dropping those indexes, timeout error is gone.
I know if we create indexes it will affect insert and update operation.
My question is what is the best approach to create indexes if we see some missing indexes on database.
March 23, 2009 at 12:47 pm
The DMVs for missing indexes are not without fault; because you got to realize that these information is correct only until last recycle of you server. And the DMV does not take into account the affect of update or inserts into you table.
It is stating that what benefit I can gain? And how many times SQL Server thinks it will use the index. The timeout issues are probably a result of a blocking; where insert or update is taking too long. Or select is taking too long and blocking the update.
You should take those indexes in DMV, with indexes on your current table and design a proper index:
1) Are there any duplicate indexes?
2) Is there a cluster index? Is the cluster index on a narrow key? Is the index on the field that the data is sorted on?
3) Can I create a new index to replace existing index that will cover both types of queries?
Those are some of the questions that go through my mind ... another is statistics. Are they up-to-date? are there correct statistics on tables?
If you post your query execution plan for the table where you are trying to create index, with table def and current indexes def (zipped attachment) I am sure someone here can help you.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 23, 2009 at 12:54 pm
Really, you can't just follow the recommendations that the server gives you. You have to understand how indexes work, and then test the recommendations in a test environment.
If you have specific questions about specific indexes, you can post the questions here. That might help you to understand how they work and what to do with them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 23, 2009 at 7:45 pm
balbirsinghsodhi (3/23/2009)
I have created a script which captures missing indexes on 2005 sql server. after capturing missing indexes, I created those indexes on production server and started getting timeout error. I am not sure if I am getting timeout error becasue of that but after dropping those indexes, timeout error is gone.I know if we create indexes it will affect insert and update operation.
Could be redundant indexes that are causing excessive blocking. How did you check you have missing indexes? Could you please post the script? and also the queries that you run?
My question is what is the best approach to create indexes if we see some missing indexes on database.
To create indexes on tables that are heavily queried.
March 24, 2009 at 11:46 am
Actually, this is the stored procedure I ran every night and stored in the Missing_Indexes table. In the table , I check the column called Overall_Impact, if the this is > 50 then I create that index. I also eliminate those indexes where User_Scans and user_seeks are 0.
ALTER Proc [dbo].[SPE_Missing_Indexes]
as
--==========================================================================================================
--Purose: Stored procedure to capture Mmmmmmissing Indexes
--CSC 07082007
--==========================================================================================================
insert
[dbo].[Missing_Indexes]
select
mi_n.*
from
(select
gs.Last_User_Seek,
d.Database_Id,
d.Object_Id,
d.Index_Handle,
isnull(d.Equality_Columns,'') as 'Equality_Columns',
isnull(d.Inequality_Columns,'') as 'Inequality_Columns',
isnull(d.Included_Columns,'') as 'Included_Columns',
d.statement as Fully_Qualified_Object,
'Create Index missing_index_' + rtrim(convert(varchar(8),d.index_handle)) + ' ON ' + rtrim(d.statement)
+ ' ('
+ case
when Len(isnull(d.equality_columns,'')) > 0 And Len(isnull(d.inequality_columns,'')) = 0 then rtrim(d.equality_columns)
when Len(isnull(d.equality_columns,'')) = 0 And Len(isnull(d.inequality_columns,'')) > 0 then rtrim(d.inequality_columns)
when Len(isnull(d.equality_columns,'')) > 0 And Len(isnull(d.inequality_columns,'')) > 0 then rtrim(d.equality_columns) + ',' + rtrim(d.inequality_columns)
else
'???'
end
+ ')'
+ case when Len(isnull(d.included_columns,'')) > 0 then ' Include (' + rtrim(isnull(d.included_columns,'')) + ')' else '' end as 'Proposed_Index',
convert(money,gs.Avg_User_Impact + gs.Avg_System_Impact) as 'Overall_Impact',
gs.Unique_Compiles,
gs.User_Seeks,
gs.User_Scans,
convert(money,gs.avg_total_user_cost) as 'Avg_Total_User_Cost',
convert(money,gs.avg_total_user_cost * gs.avg_user_impact * (gs.user_seeks + gs.user_scans)) as 'Anticipated_Cumulative_Improvement'
from
sys.dm_db_missing_index_groups g
join sys.dm_db_missing_index_group_stats gs on gs.group_handle = g.index_group_handle
join sys.dm_db_missing_index_details d on g.index_handle = d.index_handle) mi_n
left outer
join [dbo].[Missing_Indexes] mi_o on
mi_n.[Last_User_Seek] = mi_o.[Last_User_Seek]
and
mi_n.[Database_Id] = mi_o.[Database_Id]
and
mi_n.[Object_Id] = mi_o.[Object_Id]
and
mi_n.[Index_Handle] = mi_o.[Index_Handle]
and
mi_n.[Equality_Columns] = mi_o.[Equality_Columns]
and
mi_n.[Inequality_Columns] = mi_o.[Inequality_Columns]
and
mi_n.[Included_Columns] = mi_o.[Included_Columns]
where
mi_n.[Last_User_Seek] is not null and mi_o.[Last_User_Seek] is null
March 24, 2009 at 11:55 am
balbirsinghsodhi (3/24/2009)
Actually, this is the stored procedure I ran every night and stored in the Missing_Indexes table. In the table , I check the column called Overall_Impact, if the this is > 50 then I create that index. I also eliminate those indexes where User_Scans and user_seeks are 0.
The missing indexes is a suggestion. It just comes from the optimiser noticing that possibly a query could use an index that doesn't exist. It doesn't check to see if there are similar indexes already, it doesn't check to see if there's a similar suggestion already.
You cannot safely take the suggestions and implement them. You need to check and see if the index really is a good idea (often the suggested ones are far, far too wide), if there are existing ones that can be modified and if the suggested index really helps.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply