Solved

SQL Server 2005 optimize table with indexes

Posted on 2013-01-17
6
438 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:Scott Pletcher
Scott Pletcher 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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:Scott Pletcher
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

785 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