Improve company productivity with a Business Account.Sign Up


Identify Missing indexes in SQL server 2005

Posted on 2010-09-06
Medium Priority
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
Question by:sudhirgoogle

Expert Comment

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.


Accepted Solution

Gjslick earned 375 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. 

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

Author Closing Comment

ID: 33742167
highlight potential problems in your SQL Server database

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

595 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