Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Identify Missing indexes in SQL server 2005

Posted on 2010-09-06
4
Medium Priority
?
909 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 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. http://sqlcop.lessthandot.com/ 

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

Author Closing Comment

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

578 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