CODEWITHSUNDEEP
sqldatabaset-sql
witttness
witttness

Reputation: 4994

When to create a new SQL Server Index?

Obviously (methinks), creating an index on a BIT column is unnecessary. However, if you had a column that you need to search in which every value is likely unique, like BlogPost or StreetAddress or something, then an index seems appropriate (again, methinks).

But what's the cutoff? What if you expect 10,000 rows and you'll have about 20 unique values among them. Should an index be created?

Thanks in advance.

Upvotes: 17

Views: 6920

Answers (9)

LCJ
LCJ

Reputation: 22652

There are good answers already posted here... Just adding my two cents.... Execute the Missing Index DMV and see whether the table that you mentioned is listed as a candidate for creating new index and see the definition of the index.

From Are you using SQL's Missing Index DMVs?

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Upvotes: 0

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12555

one of the best way is use that mvp views in SQL Server i sugest dont restart your server for one week and then run this query :

USE master; 
Go
SELECT d.database_id,
  d.object_id,
  d.index_handle,
  d.equality_columns,
  d.inequality_columns,
  d.included_columns,
  d.statement AS fully_qualified_object,
  gs.*
FROM   sys.dm_db_missing_index_groups g
JOIN   sys.dm_db_missing_index_group_stats gs ON   gs.group_handle = g.index_group_handle
JOIN   sys.dm_db_missing_index_details d ON   g.index_handle = d.index_handle

Go

SELECT mig.index_group_handle,
  mid.index_handle,
  migs.avg_total_user_cost AS AvgTotalUserCostThatCouldbeReduced,
  migs.avg_user_impact AS AvgPercentageBenefit,
  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle)
  + '_' + CONVERT (varchar, mid.index_handle)
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
  + CASE
         WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns
         IS NOT NULL THEN ','
         ELSE ''
    END
    + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS  create_index_statement
FROM sys.dm_db_missing_index_groups mig 
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle =  mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle =  mid.index_handle
Order By migs.avg_user_impact Desc

then verify your tables and create requerd index.

Upvotes: 0

dkretz
dkretz

Reputation: 37645

Indexes with low cardinality are very problematic. If there are only several possible values, SQL Server almost always index-scans, no matter what the proportions.

Example: I've had a table with a State field that only accepted the values "A", "N", and "R" (for Active, New, and Retired.) Typically you'd approach a condition where 95% were "R", 4+% were "A", and a few were "N". SELECT WHERE state = 'N' would table-scan, no matter what.

BUT - there's a newish index type called a Filtered Index, which finally handles this condition. It's also handy when you want to exclude records with NULL values.

Upvotes: 2

Nicholas Piasecki
Nicholas Piasecki

Reputation: 25573

You should also carefully examine your indexes if you start experiencing deadlocks among queries, usually between a SELECT and an INSERT/UPDATE. A poorly chosen index can contribute to deadlocks, as can not having an index at all. See this knowledge base article for additional information. Usually, adding an index or modifying its included columns will help resolve such deadlocks. Be sure to examine the query plan of the affected queries.

Upvotes: 0

Darian Miller
Darian Miller

Reputation: 8088

I would say it all depends on how the table is being used and the overall system requirements. For example, if it's part of a large JOIN and the parent is a huge report type table then you'd want the index for sure. If it's relatively small in comparison to your other tables in the database, and it's heavily inserted and rarely read, then an index is likely not desired.

But, scope of operation amongst the entire database versus available resources to be allocated is the key decision factor. It's how this table + possible index behaves in the entire system versus all of your other tables and their requirements. If you don't keep the big picture in mind you could kill the whole system by trying to apply some arbitrary rule simply for the sake of applying an arbitrary rule.

Upvotes: 0

James Schek
James Schek

Reputation: 17960

The best answer to this is to profile your queries and see if the index improves your queries. The difficulty in answering this is that it is nearly impossible to generalize the behavior of the query optimizer.

That said, a rule-of-thumb is if your selectivity is 10% or less on a given query on a table, then you will most likely benefit from an index. So in your example, you might benefit from an index if your values are evenly distributed. However, considering that your table is small, so your performance boost might be negligible.

This is not a hard and fast rule as there are a lot of factors that can change the 10% number, including the use of a clustered or other index types, size of the rows, if some columns not inline, query structure, etc.

Also keep in mind there is a significant performance penalty for inserting into a table with an index. If this table is frequently updated or appended, the speed boost from the index may be negated by the slower inserts and updates.

See the MSDN article on Tablescan vs Index access.

Edit: As others have pointed out, your query may benefit from an index if you are performing aggregation queries, such as counting the number of times a particular value appears. You may also benefit if you frequently sort on a particular column.

Upvotes: 19

Tom H
Tom H

Reputation: 47454

James hit the nail on the head. I'll just add that even a bit column might benefit from an index depending on how you are using the table. For example, if you need to count the number of rows that have a 1 many times throughout the day, an index there could be useful. Indexes aren't always about finding a single record - they can also be used for aggregations.

Upvotes: 2

kͩeͣmͮpͥ ͩ
kͩeͣmͮpͥ ͩ

Reputation: 7846

creating an index on a BIT column is unnecessary.

You'd be suprised.

I've had to create an index involving a bit column for a query like:

SELECT foo.Name FROM foo WHERE foo.Active = 1

There were about 300,000 rows in the table though.

Upvotes: 1

Galwegian
Galwegian

Reputation: 42227

In the column you suggest, there would be rationale to create a reference or lookup table for the data to avoid data redundancy. This would make your column a foreign key, pointing towards the PK of the new lookup table.

All foreign key columns should be indexed.

Otherwise, I would avoid placing an index under normal conditions on such a column.

Upvotes: 1

Related Questions