Solved

Identify Missing indexes in SQL server 2005

Posted on 2010-09-06
4
858 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

929 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

11 Experts available now in Live!

Get 1:1 Help Now