How many indexes should we have?

  • Just a little information about the DB:

    DB size = 10GB

    Number of tables = 1476

    This simple query tell me how many indexes I have in the DB.

    SELECT

    so.name AS TableName

    , si.name AS IndexName

    , si.type_desc AS IndexType

    FROM

    sys.indexes si

    JOIN sys.objects so ON si.[object_id] = so.[object_id]

    WHERE

    so.type = 'U' --Only get indexes for User Created Tables

    AND si.name IS NOT NULL

    ORDER BY

    so.name, si.type

    Total Number of Indexes = 2342

    This query gives me a list of unused indexes.

    SELECT

    DB_NAME() AS [DatabaseName]

    , SCHEMA_NAME([sObj].[schema_id]) AS [SchemaName]

    , [sObj].[name] AS [ObjectName]

    , CASE

    WHEN [sObj].[type] = 'U' THEN 'Table'

    WHEN [sObj].[type] = 'V' THEN 'View'

    END AS [ObjectType]

    , [sIdx].[index_id] AS [IndexID]

    , ISNULL([sIdx].[name], 'N/A') AS [IndexName]

    , CASE

    WHEN [sIdx].[type] = 0 THEN 'Heap'

    WHEN [sIdx].[type] = 1 THEN 'Clustered'

    WHEN [sIdx].[type] = 2 THEN 'Nonclustered'

    WHEN [sIdx].[type] = 3 THEN 'XML'

    WHEN [sIdx].[type] = 4 THEN 'Spatial'

    WHEN [sIdx].[type] = 5 THEN 'Reserved for future use'

    WHEN [sIdx].[type] = 6 THEN 'Nonclustered columnstore index'

    END AS [IndexType]

    FROM

    [sys].[indexes] AS [sIdx]

    INNER JOIN [sys].[objects] AS [sObj]

    ON [sIdx].[object_id] = [sObj].[object_id]

    WHERE

    NOT EXISTS (

    SELECT *

    FROM [sys].[dm_db_index_usage_stats] AS [sdmfIUS]

    WHERE

    [sIdx].[object_id] = [sdmfIUS].[object_id]

    AND [sIdx].[index_id] = [sdmfIUS].[index_id]

    AND [sdmfIUS].[database_id] = DB_ID()

    )

    AND [sObj].[type] IN ('U','V') -- Look in Tables & Views

    AND [sObj].[is_ms_shipped] = 0x0 -- Exclude System Generated Objects

    AND [sIdx].[is_disabled] = 0x0 -- Exclude Disabled Indexes

    Total number of unused indexes = 1829

    Question:

    1. Is this normal?

    2. About the unused index. Does it tell me the list of indexes which are never used or is it a list of unused indexes which are not used recently.

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • 1400 tables, 2300 indexes. That's about 1.5 indexes per table. Actually kind of a low number, IMHO.

    Does this seem normal? Define normal. What works fine for you and your environment may work terribly for me and my environment.

    Since the DB is only 10 GB, you may not see a need for more indexes. At this size, unless you have a server with 512 mb of RAM, everything should be fast!

    As for the unused indexes, these statistics are from the last time the server was re-booted. So, if you re-booted yesterday, then these numbers may be misleading.

    The question I have is where did these indexes come from? The developers? A DBA? How much analysis was completed before indexes were put in place?

    And, what exactly are you trying to determine? Are you seeing performance issues? Are you simply trying to learn more about your server and databases?

    Download Brent Ozar's Blitz Index.

    http://www.brentozar.com/blitzindex/

    This is a good starting point when attempting to learn and analyze indexes in a database.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Since the DB is only 10 GB, you may not see a need for more indexes. At this size, unless you have a server with 512 mb of RAM, everything should be fast!

    Server has 32GB of RAM and 8GB is assigned to this DB which to me is a lot.

    As for the unused indexes, these statistics are from the last time the server was re-booted. So, if you re-booted yesterday, then these numbers may be misleading.

    Got it. It was rebooted 5 days ago.

    The question I have is where did these indexes come from? The developers? A DBA? How much analysis was completed before indexes were put in place?

    Don't know.

    And, what exactly are you trying to determine? Are you seeing performance issues? Are you simply trying to learn more about your server and databases?

    I am just trying to learn about the environment. I am not seeing any performance issues other than some queries are running, generating 1 or 2 columns, going through 13000+ logical reads so I am assuming this is bad.

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • New Born DBA (7/17/2014)


    Since the DB is only 10 GB, you may not see a need for more indexes. At this size, unless you have a server with 512 mb of RAM, everything should be fast!

    Server has 32GB of RAM and 8GB is assigned to this DB which to me is a lot.

    Uh, when you say "8GB is assigned to this DB", how are you determining that? Do you mean this INSTANCE?

    As for the unused indexes, these statistics are from the last time the server was re-booted. So, if you re-booted yesterday, then these numbers may be misleading.

    Got it. It was rebooted 5 days ago.

    I think your query that checks for unused indexes may be faulty.

    This is the one I use, I'm sure that credit is due for this, but I cannot remember where this came from.

    SELECT '[' + DB_NAME() + '].[' + SU.[name] + '].[' + O.[name] + ']' [statement],

    I.[name] [index_name],

    IUS.[user_seeks] + IUS.[user_scans] + IUS.[user_lookups] [user_reads],

    IUS.[user_updates] [user_writes],

    SUM(SP.rows) [total_rows]

    FROM sys.dm_db_index_usage_stats IUS

    INNER JOIN sys.indexes I ON IUS.[object_id] = I.[object_id]

    AND I.[index_id] = IUS.[index_id]

    INNER JOIN sys.partitions SP ON IUS.[object_id] = SP.[object_id]

    AND SP.[index_id] = IUS.[index_id]

    INNER JOIN sys.objects O ON IUS.[object_id] = O.[object_id]

    INNER JOIN sys.sysusers SU ON O.[schema_id] = SU.[UID]

    WHERE IUS.[database_id] = DB_ID() -- current database only

    AND OBJECTPROPERTY(IUS.[object_id], 'IsUserTable') = 1

    AND IUS.[index_id] > 0

    GROUP BY SU.[name], O.[name], I.[name], IUS.[user_seeks] + IUS.[user_scans] + IUS.[user_lookups], IUS.[user_updates]

    HAVING IUS.[user_seeks] + IUS.[user_scans] + IUS.[user_lookups] = 0

    ORDER BY IUS.[user_updates] DESC, SU.[name], O.[name], I.[name]

    And, what exactly are you trying to determine? Are you seeing performance issues? Are you simply trying to learn more about your server and databases?

    I am just trying to learn about the environment. I am not seeing any performance issues other than some queries are running, generating 1 or 2 columns, going through 13000+ logical reads so I am assuming this is bad.

    Probably not a good assumption. 13000+ may be normal for your system.

    The answer is, "It depends".

    What is the query doing? Can you post the execution plan?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael L John (7/17/2014)

    Uh, when you say "8GB is assigned to this DB", how are you determining that? Do you mean this INSTANCE?

    Sorry my bad. 8GB is assigned to the instance and there is only 1 DB.

    Michael L John (7/17/2014)

    I think your query that checks for unused indexes may be faulty.

    This is the one I use, I'm sure that credit is due for this, but I cannot remember where this came from.

    SELECT '[' + DB_NAME() + '].[' + SU.[name] + '].[' + O.[name] + ']' [statement],

    I.[name] [index_name],

    IUS.[user_seeks] + IUS.[user_scans] + IUS.[user_lookups] [user_reads],

    IUS.[user_updates] [user_writes],

    SUM(SP.rows) [total_rows]

    FROM sys.dm_db_index_usage_stats IUS

    INNER JOIN sys.indexes I ON IUS.[object_id] = I.[object_id]

    AND I.[index_id] = IUS.[index_id]

    INNER JOIN sys.partitions SP ON IUS.[object_id] = SP.[object_id]

    AND SP.[index_id] = IUS.[index_id]

    INNER JOIN sys.objects O ON IUS.[object_id] = O.[object_id]

    INNER JOIN sys.sysusers SU ON O.[schema_id] = SU.[UID]

    WHERE IUS.[database_id] = DB_ID() -- current database only

    AND OBJECTPROPERTY(IUS.[object_id], 'IsUserTable') = 1

    AND IUS.[index_id] > 0

    GROUP BY SU.[name], O.[name], I.[name], IUS.[user_seeks] + IUS.[user_scans] + IUS.[user_lookups], IUS.[user_updates]

    HAVING IUS.[user_seeks] + IUS.[user_scans] + IUS.[user_lookups] = 0

    ORDER BY IUS.[user_updates] DESC, SU.[name], O.[name], I.[name]

    Gave me 113 rows.

    Michael L John (7/17/2014)

    Probably not a good assumption. 13000+ may be normal for your system.

    The answer is, "It depends".

    What is the query doing? Can you post the execution plan?

    See the attachment please.

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • The execution plan tells it all. This query is performing a table scan. In order to get one row, the query optimizer needs to look at all of the rows.

    In this case, you probably NEED an new index.

    It's recommending a new index with a single field. This may be a good index for the entire system, it may only be a good index for this query.

    You need to dig into your system, and perform some analysis of the index usage.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael L John (7/17/2014)


    The execution plan tells it all. This query is performing a table scan. In order to get one row, the query optimizer needs to look at all of the rows.

    In this case, you probably NEED an new index.

    It's recommending a new index with a single field. This may be a good index for the entire system, it may only be a good index for this query.

    You need to dig into your system, and perform some analysis of the index usage.

    I created a nonclustered index and this is what I got.

    Please see the attachments;

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • There's no "right" number of indexes per table. It depends entirely on usage.

    You need to consider at least SQL's index usage stats and missing index stats. [There are some other things you can look at, esp. for large (2M+row) tables, but we can skip any of that for now.]

    After reviewing the code marked in bold, run the queries below to see missing index and index usage stats.

    USE [<your_db_name>]

    SET DEADLOCK_PRIORITY LOW --probably irrelevant, but just in case

    DECLARE @list_missing_indexes bit

    DECLARE @table_name_pattern sysname

    --NOTE: showing missing indexes can take some time; set to 0 if you don't want to wait.

    SET @list_missing_indexes = 1

    SET @table_name_pattern = '%' --'%'=all tables.

    --SET @table_name_pattern = '%'

    PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

    IF @list_missing_indexes = 1

    BEGIN

    SELECT

    GETDATE() AS capture_date,

    DB_NAME(mid.database_id) AS Db_Name,

    dps.row_count,

    OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,

    mid.equality_columns, mid.inequality_columns, mid.included_columns,

    user_seeks, user_scans, ca1.max_days_active, unique_compiles,

    last_user_seek, last_user_scan, avg_total_user_cost, avg_user_impact,

    system_seeks, system_scans, last_system_seek, last_system_scan, avg_total_system_cost, avg_system_impact,

    mid.statement, mid.object_id, mid.index_handle

    FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)

    CROSS APPLY (

    SELECT DATEDIFF(DAY, create_date, GETDATE()) AS max_days_active FROM sys.databases WHERE name = 'tempdb'

    ) AS ca1

    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON

    mig.index_handle = mid.index_handle

    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON

    migs.group_handle = mig.index_group_handle

    LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON

    dps.object_id = mid.object_id AND

    dps.index_id IN (0, 1)

    --order by

    --DB_NAME, Table_Name, equality_columns

    WHERE

    1 = 1

    AND mid.database_id = DB_ID() --only current db

    AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern

    --AND mid.object_id IN (OBJECT_ID('<table_name_1>'), OBJECT_ID('<table_name_2>'))

    ORDER BY

    --avg_total_user_cost * (user_seeks + user_scans) DESC,

    Db_Name, Table_Name, equality_columns, inequality_columns

    END --IF

    -- list index usage stats (seeks, scans, etc.)

    SELECT

    ius2.row_num, DB_NAME() AS db_name,

    CASE WHEN i.name LIKE ca2.table_name + '%'

    THEN '~' + SUBSTRING(i.name, LEN(ca2.table_name) + 1 +

    CASE WHEN SUBSTRING(i.name, LEN(ca2.table_name) + 1, 1) = '_' THEN

    CASE WHEN SUBSTRING(i.name, LEN(ca2.table_name) + 2, 1) = '_' THEN 2 ELSE 1 END

    ELSE 0 END, 200)

    ELSE i.name END AS index_name,

    CASE WHEN i.is_unique = 0 THEN 'N' ELSE 'Y' END + '.' +

    CASE WHEN i.is_primary_key = 0 AND i.is_unique_constraint = 0 THEN 'N' ELSE 'Y' END AS [uniq?],

    ca2.table_name,

    i.index_id, --ius.user_seeks + ius.user_scans AS total_reads,

    dps.row_count,

    SUBSTRING(key_cols, 3, 8000) AS key_cols, SUBSTRING(nonkey_cols, 3, 8000) AS nonkey_cols,

    ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,

    ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update,

    fk.Reference_Count AS fk_ref_count,

    DATEDIFF(DAY, CASE WHEN o.create_date > ca1.sql_startup_date THEN o.create_date

    ELSE ca1.sql_startup_date END, GETDATE()) AS max_days_active,

    FILEGROUP_NAME(i.data_space_id) AS filegroup_name,

    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,

    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update

    FROM sys.indexes i WITH (NOLOCK)

    INNER JOIN sys.objects o WITH (NOLOCK) ON

    o.object_id = i.object_id

    CROSS JOIN (

    SELECT create_date AS sql_startup_date FROM sys.databases WHERE name = 'tempdb'

    ) AS ca1

    CROSS APPLY (

    SELECT OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name

    ) AS ca2

    OUTER APPLY (

    SELECT

    ', ' + COL_NAME(object_id, ic.column_id)

    FROM sys.index_columns ic

    WHERE

    ic.key_ordinal > 0 AND

    ic.object_id = i.object_id AND

    ic.index_id = i.index_id

    ORDER BY

    ic.key_ordinal

    FOR XML PATH('')

    ) AS key_cols (key_cols)

    OUTER APPLY (

    SELECT

    ', ' + COL_NAME(object_id, ic.column_id)

    FROM sys.index_columns ic

    WHERE

    ic.key_ordinal = 0 AND

    ic.object_id = i.object_id AND

    ic.index_id = i.index_id

    ORDER BY

    COL_NAME(object_id, ic.column_id)

    FOR XML PATH('')

    ) AS nonkey_cols (nonkey_cols)

    LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON

    dps.object_id = i.object_id AND

    dps.index_id = i.index_id

    LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON

    ius.database_id = DB_ID() AND

    ius.object_id = i.object_id AND

    ius.index_id = i.index_id

    LEFT OUTER JOIN (

    SELECT

    database_id, object_id, MAX(user_scans) AS user_scans,

    ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans

    FROM sys.dm_db_index_usage_stats WITH (NOLOCK)

    WHERE

    database_id = DB_ID()

    --AND index_id > 0

    GROUP BY

    database_id, object_id

    ) AS ius2 ON

    ius2.database_id = DB_ID() AND

    ius2.object_id = i.object_id

    LEFT OUTER JOIN (

    SELECT

    referenced_object_id, COUNT(*) AS Reference_Count

    FROM sys.foreign_keys

    WHERE

    is_disabled = 0

    GROUP BY

    referenced_object_id

    ) AS fk ON

    fk.referenced_object_id = i.object_id

    WHERE

    i.object_id > 100 AND

    i.is_hypothetical = 0 AND

    i.type IN (0, 1, 2) AND

    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND

    (

    o.name LIKE @table_name_pattern AND

    o.name NOT LIKE 'dtprop%' AND

    o.name NOT LIKE 'filestream[_]' AND

    o.name NOT LIKE 'MSpeer%' AND

    o.name NOT LIKE 'MSpub%' AND

    --o.name NOT LIKE 'queue[_]%' AND

    o.name NOT LIKE 'sys%'

    )

    --AND OBJECT_NAME(i.object_id /*, DB_ID()*/) IN ('tbl1', 'tbl2', 'tbl3')

    ORDER BY

    --row_count DESC,

    --ius.user_scans DESC,

    --ius2.row_num, --user_scans&|user_seeks

    -- list clustered index first, if any, then other index(es)

    db_name, table_name,

    CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END,

    key_cols

    PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 8 posts - 1 through 7 (of 7 total)

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