Solved

SQL Server 2005 optimize table with indexes

Posted on 2013-01-17
6
431 Views
Last Modified: 2013-01-17
Is there a tool that will suggest whether or not a set of tables need indexes created to optimize speed?
0
Comment
Question by:lrbrister
  • 2
  • 2
  • 2
6 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 350 total points
ID: 38787650
There are some DMV's that keep track of that for you.

Check out:

http://sqlserverpedia.com/wiki/Find_Missing_Indexes
http://weblogs.sqlteam.com/mladenp/archive/2009/04/08/SQL-Server---Find-missing-and-unused-indexes.aspx

Also Brent Ozar Unlimited has a script that helps assess your index health:
http://www.brentozar.com/blitzindex/
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 150 total points
ID: 38787741
You have to very carefully review the "missing index" info from the MS system views.  Yes, it's nice to have this info, but do not rely on it without a thorough review from a knowledgeable person first.  That applies to DTA recommendations as well.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38787789
Should have mentioned that - the DMV's etc aren't flawless - they are recommendations that should be carefully assessed and tested before implementing anything in production environments.

Any sort of change to indexing strategy should be carefully analyzed before being implemented.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Closing Comment

by:lrbrister
ID: 38787851
Thanks guys.
I'm the SR Developer (.Net) onsite

I'm just making what I THINK may be recommendations for review by the customer DBA.

Their responsibility...ut mine is to at least point them at what I believe may be a bottleneck.

I am certainly no DBA.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38788194
Most important for performance is the proper clustered index, and the missing index views won't help with that at all.

But, if they've got a (true) DBA, he'll know to look at the index usage stats and the other index-related views as well.
0
 

Author Comment

by:lrbrister
ID: 38788410
ScottPletcher

Thanks.  yes, he's a tru DBA.  Not a wannabe like me. :)
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

10 Experts available now in Live!

Get 1:1 Help Now