SQL Server Performance

Monitoring table usage

Discussion in 'Performance Tuning for Hardware Configurations' started by Cohen, May 24, 2004.

  1. Cohen New Member

    Hi,

    We have a 8GB database that makes use of 2 filegroups on RAID 1+0 arrays. Performance Monitor shows that the disk that is used for the primary filegroup is used far more than the disk that has the secondary filegroup and the everage queue length on the first disk is too high as well. Therefore we think that reassing tables/indexes to filegroups could improve performance. I have app. 15 - 20 large tables that are frequently used.

    My question is "is there a way of monitoring the usage of the tables/indexes for some time (say a week) to get an idea of the tables/indexes that should be moved from the primary to the secondary filegroup. (like 'set statistics IO on' would do for one query or batch)

    Thanks
  2. satya Moderator

    PROFILER is the tool to monitor the usage of indexes and other queries against the database, by submitting the trace to index tuning wizard will give you more information on index recommendations too.

    Ensure to maintain and schedule the db maintenance & dbcc checks on the database.

    http://www.winnetmag.com/SQLServer/Article/ArticleID/38789/SQLServer_38789.html for tracking index usage.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. Luis Martin Moderator

    After read link, I run Profiler with ObjectID,DatabaseId and IndexID.
    Profiler show ObjectId, DatabaseId but IndexId is allways blank.
    I'm missing something?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  4. satya Moderator

  5. Luis Martin Moderator

    IndexId only work with Objects Event and not with others events in trace.
    Even when I check all under Objects Event and use appropiate columns (indexId, objectId, objectype,etc), never show somethink in IndexId trace.

    The only way I found to know what indexs are used is via hard work I've post in Tips & scripts.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  6. satya Moderator

  7. Cohen New Member

    I 've the same problem as Luis: relevant trace columns are always blank when trying to see what indexes are used.
  8. satya Moderator

    Fyi, you can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.

    http://www.winnetmag.com/SQLServer/Article/ArticleID/38789/SQLServer_38789.html article about tracking index usage.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page