Find Tables That Should Have Indexes ?

Question

Find Tables That Should Have Indexes ?

homebrew01

SSC Guru

Points: 55548
More actions
November 21, 2015 at 5:07 pm

#313251

I found a table that should have had some indexes to improve performance. So now I'm wondering what other tables could benefit. I can get a list of tables without indexes, and many are fine, such as small lookup tables, or rarely used history tables that are 99.9 % inserts.
Is there a good method to identify tables that would benefit from index tuning ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

homebrew01 (11/21/2015)
I found a table that should have had some indexes to improve performance. So now I'm wondering what other tables could benefit. I can get a list of tables without indexes, and many are fine, such as small lookup tables, or rarely used history tables that are 99.9 % inserts.
Is there a good method to identify tables that would benefit from index tuning ?

You can find your candidates using things like what are presented in the following...

https://technet.microsoft.com/en-us/library/ms345417(v=sql.100).aspx

Of course, it's as bad as DTA (Database engine Tuning Advisor) when it comes to actual index recommendations, but at least it'll let you know what tables to look at. The ultimate would be to find the queries using the tables and determining if there's a performance problem with those queries or not. I wrote my own but haven't published it. If you do a search for "Find top 10 worst performing queries", you'll find lot's of goodies on the subject.

Don't forget that you can also find some of those queries by right clicking on the instance name in the Object Explorer of SSMS, selecting "Reports", and then follow your nose.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MadAdmin SSChampion Points: 11261 More actions · Answer 2

Jeff Moden (11/21/2015)
The ultimate would be to find the queries using the tables and determining if there's a performance problem with those queries or not. I wrote my own but haven't published it. If you do a search for "Find top 10 worst performing queries", you'll find lot's of goodies on the subject.

/slowclap

select distinct top 20 schema_name(o.schema_id)+'.'+o.name,s.*

from sys.dm_exec_procedure_stats S

inner join sys.objects o on s.object_id = o.object_id

inner join syscomments C on C.ID = o.object_id

--where text like '%mytable%'

order by total_logical_reads desc

--order by min_elapsed_time desc

--order by min_logical_reads desc

Look at the above results first, as JM says, then look at what is wrong with the worst of the worst.

If the query says there is a missing index, look at what it is asking for.

If it is asking for an index which will have high cardinality, then it may need investigation,i.e. why does it need an index on Gender, as an example? is the code bad or is it a report?

If it is asking for an index which has very low cardinality or cardinality = 1, like say, customerID, then it should be a no brainer.

Factorise the suggested indexes from the DMV's, and also maybe consider clustering on a different index.

If the tables with missing indexes are heaps with identities, then maybe consider clustering on the identity with a PK.

Also, have a look at index usage stats because you may find you have a table with millions of scans but does not appear in missing indexes because of dodge sql.

As can be seen from the couple of cases above, analysis is paramount.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

lmarkum SSCertifiable Points: 7989 More actions · Answer 3

I would review the below from the SQL Server Customer Advisory Team. The article addresses the issues below:

(1) How can I find out whether my indexes are useful? How are they used?

(2) Do I have any tables or indexes that are not used (or rarely)

(3) What is the cost of index maintenance vs. its benefit?

(4) Do I have hot spots & index contention?

(5) Could I benefit from more (or less) indexes?

http://blogs.msdn.com/b/sqlcat/archive/2006/02/13/531339.aspx

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Whatever you do, please don't just run the DTA and create everything it wants. You'll probably end up with so much over-indexing that you won't be able to see straight. Look for heaps and consider the missing indexes, but don't let it replace rational thought.

Please, above all else, don't forget to test, test and test some more.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions