Solved

SQL Server 2005 optimize table with indexes

Posted on 2013-01-17
6
442 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
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.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

860 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