Solved

Identify Missing indexes in SQL server 2005

Posted on 2010-09-06
4
879 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

752 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