SQL Server Performance

database index analysis

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by dba_boston, Mar 19, 2010.

  1. dba_boston Member

    I would like to do a index analysis on a database.I have some basis idea of steps:1, re-index 2, run a report of non using index3, run a report of table without any index –is anyone know how to do this?4, run a db index fragmentation report5, check long running query using profiler Any other idea?Thanks.
  2. FrankKalis Moderator

    - experiment with different clustered indexes
    - experiment with indexes with INCLUDE () clauses
    - test the bulk load performance (if applicable to your environment)
    - test single vs. composite indexes. also in terms of disk utilization
  3. satya Moderator

  4. I would also check against sys.dm_db_index_usage_stats to see how frequently each index is used. If you see more inserts than Selects on an index, you may need to revisit the indexing startegy and check the usage and how critical is the usage.
    Also check sys.dm_db_index_operational_stats for current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.
    Check how much is index space being used against the data.
    You can run a query against all the query plans and see if any query plan does index scan.3, run a report of table without any index –is anyone know how to do this?
    Join sys.objects table with sysindexes and look for a table with no index.

Share This Page