Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have pulled a list of FK's that do not have indexes on them.

If a FK does not have a dedicated index on them but are part of wider indexes used for covering queries, Should they have a dedicated index created ?

for example mytable below is a 20 column table with one varchar(255) and the rest ints, datetime or smallints.

it is missing indexes on

AdvertiserId  
Dirty  
MasterAdGroupId

AdvertiserID missing a foreign key index, but [advertiserID] is part of an index with [dirty]&[error] which are smallints. Should this have its own dedicated index ?

Should I be removing some of these indexes and combining them with included columns instead? then have dedicated indexes for my foreign keys ?

    index_keys  
    -------------
    [AdvertiserAdGroupId]
    [AdvertiserHierarchyId]
    [AdvertiserHierarchyId], [AdvertiserAdGroupId], [MasterAdGroupId]
    [AdvertiserAdGroupCode]
    [AdvertiserAdGroupId], [MasterAdGroupId], [AdvertiserId]
    [AdvertiserId], [Dirty], [Error]
    [MasterAdGroupId], [Deleted], [AdvertiserAdGroupId]
    [AdvertiserHierarchyId], [Error], [AdvertiserAdGroupId], [MasterAdGroupId], [Deleted]
    [Error], [AdvertiserHierarchyId], [Dirty], [AdvertiserAdGroupId]
    [MasterAdGroupId], [AdvertiserAdGroupId], [AdvertiserHierarchyId]
    [AdvertiserId], [AdvertiserAdGroupCode], [LastSyncedDate], [CreatedDate]
    [AdvertiserId], [Deleted], [Paused], [AdvertiserAdGroupCode]
share|improve this question
3  
That list smells like your predecessor was overly trusting of the missing index suggestions in SSMS. –  Mark Storey-Smith Jul 2 '12 at 11:02
    
yes, I have already cleared out a number of hypothetical indexes and duplicates with names like 'DTA_'% –  DamagedGoods Jul 2 '12 at 11:07
2  
If AdvertiserId is the leading column in that index, you won't get much benefit at all from a separate index for that column alone. There certainly does seem to be some consolidation possible here; I suspect a lot of these indexes were created to satisfy a single query with little thought put to the rest of the workload. However it would be very hard for any of us to suggest the best way to consolidate them. For example one of the indexes might be used for a very specific query the CEO runs once a month, and that is more important than consolidating. –  Aaron Bertrand Jul 2 '12 at 12:42

3 Answers 3

up vote 6 down vote accepted

If a FK does not have a dedicated index on them but are part of wider indexes used for covering queries, Should they have a dedicated index created?

It depends on the table's access patterns. If the column is being searched a lot (and, ideally, is highly selective), then yes, you absolutely should have an index on that column, with the column as the first key column in the definition.

Should I be removing some of these indexes and combining them with included columns instead? then have dedicated indexes for my foreign keys?

What was given in the question is somewhat unclear, and the question you've asked is a bit... confused, so let's take a step back for a second.

In SQL Server 2005+, the three most important parts of an index definition are:

  1. The key columns, which determines the index sort order. This means the order of the key columns is very important, because SQL Server uses an index by searching for a value in the first key column, then in the second key column, etc.

  2. The included columns, which are copies of row data tagged onto the index structure. The order included columns are specified is irrelevant.

  3. Is the index unique? This means that the index key can contain only unique combinations of column values.

(While this is not relevant to the discussion at hand, for completeness I will mention it here: SQL Server 2008+ introduces the concept of filtered indexes, which only includes rows in the index that satisfy a predicate.)

The first thing you should do is index consolidation. This involves using the points above to combine indexes that share commonalities.

For example, consider the following two indexes:

CREATE INDEX IX_1 ON [dbo].[t1](C1) INCLUDE(C3, C4);
CREATE INDEX IX_2 ON [dbo].[t1](C1, C2) INCLUDE(C5);

These indexes share the leading key column, C1. Included columns can be specified in any order, so these two indexes could be combined as follows:

CREATE INDEX IX_3 ON [dbo].[t1](C1, C2) INCLUDE(C3, C4, C5);

Where index keys differ in their composition or other properties, you have to be very careful. Consider these indexes:

CREATE INDEX IX_4 ON [dbo].[t1](C1, C3) INCLUDE(C4);
CREATE UNIQUE INDEX IX_5 ON [dbo].[t1](C1, C4) INCLUDE(C5);

Now the decision is not as easy. You have to determine what to do based on your workload, which queries hit the table, and the selectivity of the data itself.

So to answer the question more directly: if you currently have one or more indexes where the column of interest is the first key column in those indexes, you don't have to add more indexes, because the indexes you have are useful.

If the column is searched frequently and there isn't an index with that column as the first key column, you should create an index with that column as the first key column. (Depending on query requirements, you may want to specify other columns as well, for either the key or the included columns.)

If the column is not searched frequently, you can potentially get away with having it contained in another index (not the first key column): the query may be satisfied by scanning the index that contains the column. This is not as efficient as an index seek (for many reasons), but if this operation doesn't happen too often, and the performance in this case is acceptable, you may be okay.

Remember that creating indexes isn't free -- they take up data space, log space, cache memory, and can potentially slow down INSERT/UPDATE/DELETE activity (having said that, there can be other advantages to creating indexes). It's a balance you have to strike for your environment.

share|improve this answer

An index is useful if it will be used. This depends on the type of queries you perform, how data is inserted into the parent tables (assuming FK is active) and the size of the parent table. For example, if MasterAdGroupId is a key for MasterAdGroup table, and that table only has 5 rows, defining an index over it would be useless. Also the column called 'Dirty' sounds like a binary column and I don't think you'd gain great value of indexing it.

share|improve this answer
    
apart from the dirty column which links to a smallint lookup table with 7 or so reasons so that has been omitted, the rest are several million row tables –  DamagedGoods Jul 2 '12 at 11:54
    
@ChrisWood, please check this, while it is for Oracle, the principles in general may apply to SQL Server: asktom.oracle.com/pls/asktom/… –  Emmad Kareem Jul 2 '12 at 12:20

As stated in other answers, you want to make sure that the indexes will be used. Kevin Kline has a nice video about how to check and see what indexes are being used. This is the sql code he uses to check for unused indexes:

SELECT 
o.name
, indexname=i.name
, i.index_id   
, reads=user_seeks + user_scans + user_lookups   
, writes =  user_updates   
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE
    WHEN s.user_updates < 1 THEN 100
    ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
  END AS reads_per_write
, 'DROP INDEX ' + QUOTENAME(i.name) 
+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats s  
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id   
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()   
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.