Recommended number of index for a table I've a table with many columns (~70) which contains computer's configuration. It contains approximately 50 000 rows.

This table is very requested and all these column too.

I use to run the following query to know missing index history:

-- Missing Index Script
-- Original Author: Pinal Dave (C) 2011.

use myDatabaseName

SELECT TOP 25
    dm_mid.database_id AS DatabaseID,
    dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
    dm_migs.last_user_seek AS Last_User_Seek,
    OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
    'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
    CASE
    WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
    ELSE ''
    END
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
    + ']'
    + ' ON ' + dm_mid.statement
    + ' (' + ISNULL (dm_mid.equality_columns,'')
    + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
    '' END
    + ISNULL (dm_mid.inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
    ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
    ON dm_mig.index_handle = dm_mid.index_handle
ORDER BY Avg_Estimated_Impact DESC

GO


But for these specific table it gaves me twenty different indexes with an impact > 300 (impact = dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans)).

I read somewhere that it's not relevant to have more than 10 indexes, is there any recommendations about it and how can I know if it's really usefull to define all these indexes?
I read somewhere that it's not relevant to have more than 10 indexes


No hard-and-fast rules like that are generally applicable. If you have more than ten columns (or combinations of columns as an index can cover several) that are regularly searched by then you probably need more than 10 indexes.

The key problems with too many indexes which you need to consider are:


Space needed. If you have limited disk space then this can be an issue, but space is cheap these days. Remember to consider backup sizes as well as the size of your production databases.
Potential RAM use. Though this effect is usually small unless you have too little RAM to start with.
Insert and update speed, though if your table sees vastly more reads than writes (which is the most common use profile) then this is not generally significant either.



  how can I know if it's really usefull to define all these indexes?


Nothing beats application specific benchmarking for that sort of thing. You know how your application and its users hit the database (or if you don't, you need to study this (or apply some careful thought if the app isn't live yet so doesn't have real users)) so build some benchmarks based on that activity and see what difference it makes. Remember to test queries/updates that don't happen very often as well as those that do though - sometimes infrequent reports are the most important (i.e. in the app that I manage there are reports the users access once or twice per month, but while they aren't used often they are vital to their business and need to respond in reasonable time) so you don't want them timing out or simply taking hours to produce.