Solved

Identify Missing indexes in SQL server 2005

Posted on 2010-09-06
4
854 Views
Last Modified: 2013-11-05
what is the process to identify which indexes I need to create.  I can run the Database Tuning Advisor or examine the query plans, but is there any easier way to determine which indexes may be needed?

About Environment
Database : SQl Server 2005
The database contains 1000 Plus Tables, In that Table contains averagely 75 columns

Additional information:
I have executed few DMVs but I am not getting the result what i need...
DMVs are
sys.dm_db_missing_index_details
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_columns(index_handle)
0
Comment
Question by:sudhirgoogle
4 Comments
 
LVL 7

Expert Comment

by:lundnak
ID: 33614274
The database tuning advisor is probably the easiest method.

Start a trace of the database.  Let it run for as many minutes as needed.  Specifically, run it during a time period that there is consistent database activity.  Then load the trace file in Database Tuning Advisor.  It will run through the trace file and give you suggestions.  You can even apply the indexes from DTA.

I know Idera has an Index Analyzer tool.

Here is a free tool.  I have never used it, though.  http://indexanalysis.codeplex.com/

0
 
LVL 3

Accepted Solution

by:
Gjslick earned 125 total points
ID: 33614778
There is a great little tool out there called "SQL Cop." It will identify missing foreign key indexes, fragmented indexes, etc., among many other common database issues. http://sqlcop.lessthandot.com/  

For missing indexes based on the queries of your application though, the above suggestion would probably work well.
0
 
LVL 5

Expert Comment

by:ThakurVinay
ID: 33615320
0
 
LVL 1

Author Closing Comment

by:sudhirgoogle
ID: 33742167
highlight potential problems in your SQL Server database
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now