Solved

SQL Server 2005 optimize table with indexes

Posted on 2013-01-17
6
447 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 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