Find Not used Sift levels

JedrzejT
JedrzejT Member Posts: 267
edited 2008-01-16 in SQL Performance
Hello,

I try to unmaitain sift levels that are unused.

I used query (show me unused indexes. Thats great for UnMiantainSQLIndex)

select a.*,b.name as idxname,c.name as objname
from sys.dm_db_index_usage_stats a,alfa.[700].sys.indexes b,alfa.[700].sys.all_objects c
where
a.user_seeks=0 and
a.user_scans=0 and
a.user_lookups=0 and
a.system_seeks=0 and
-- a.system_scans=0 and
a.system_lookups=0 and
a.database_id=9 and
b.object_id=a.object_id and
b.index_id=a.index_id and
c.object_id=a.object_id

order by a.object_id, a.index_id

For "noramal tables" this show me for example:

ObjName - CompanyName&Work calendar (table name)
IdxName - $2

Then I know the index "2" is not used and I can not maintainSqlIndex



The problem is:

This query show me Sift tables indexes too, but i don't understand it

For example

ObjName CompanyName&379$2 and
idxName CompanyName$379$2_hlp_idx


Problem is .. the index "2_hlp" don't exist in database, but i think something is wrong with it (not used). Can somebody explain me what is the "hlp" keyword in name of index?

Few recorde fom query show me unused sift table index without "hlp" keyword.. then i know what to do with it.

I try to find fast solution to find sift level that i can not maintain. Maybe somebody know the other solution?

Regards

Comments

  • stryk
    stryk Member Posts: 645
    Well, several things ...

    1. Have in mind that the Index Usage Statistics are reset when you restart the service. To get relyable figures about index usage you have to save the statistics periodically.

    2. The Index Usage Statistics cannot give you information about which "bucket" is used, as all active buckets are stored in the same table - thus using the same index. The index usage indirectly hints if SIFT Index is used or not ...

    3. The ...hlp_idx are Non-Clustered Indexes on SIFT Tables, created by C/SIDE (I still have no clue why they're created).

    Once I wrote some BLOG about SIFT & Co.:http://dynamicsuser.net/blogs/stryk/archive/2007/10.aspx
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • JedrzejT
    JedrzejT Member Posts: 267
    Thanks for reply,

    great blog - i try to test method with "covering index".
    I have company with speed problem and try to do something with this.

    One more question,

    "Index usage statistics" table are Update/change when i execute "updatestatistics"?

    Regards
  • stryk
    stryk Member Posts: 645
    JedrzejT wrote:
    "Index usage statistics" table are Update/change when i execute "updatestatistics"?

    No. When updating the statistics (sp_updatestats or sp_createstats) the the Index Statistics are maintained (density, selectivity).
    The Index Usage Statistics (sys.dm_db_index_usage_stats) are not Index Statistics, this is a statistic about how often and in which way an index is used with the quries ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • bonehead
    bonehead Member Posts: 22
    Hi

    I think the hlp_idx are indexes created by the database optimisation tool - they are not real indexes and you can just detete the optimisation anaysis that you've ran and they will disappear.

    Don't use SQL database tuner to sugges indexes - it's no good!!!

    Also on the SIFT side of things - if you write a profiler to record all select sum statements from the database and parsce them with a curser over time you will find out the buckets -

    I could not come up with any other way of analysis on SIFT - I would love a tool to do this, it would make my life very easy as we have huge sifts have come from a 3.6 database

    dave
  • stryk
    stryk Member Posts: 645
    bonehead wrote:
    I think the hlp_idx are indexes created by the database optimisation tool - they are not real indexes and you can just detete the optimisation anaysis that you've ran and they will disappear.
    No, these indexes are created by NAV "out-of-the-box" ... I have no clue, which is the basis for C/SIDE's "desicion" to generate them ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Nobody
    Nobody Member Posts: 93
    You could do something like this, but in the end it still comes down to a judgement call.

    If a SIFT table has no user seeks or no user scans or has vry few of each and the last user seek and the last user scan was days or months ago is it really necessary?

    All is could be a moot point because the data is only tracked from the last time the server service was restarted or the server rebooted. So the longer the server have been up an running the more accurate the data

    ---
    Select
    dmv.object_id, so.name as table_name,
    si.name as index_name, si.type_desc as Index_Type,
    dmv.index_id, dmv.user_seeks, dmv.user_scans,
    dmv.last_user_seek, dmv.last_user_scan,
    dmv.last_user_update, rowcnt = 0
    Into test
    From
    sys.dm_db_index_usage_stats dmv, sys.objects so,
    sys.indexes si
    Where
    dmv.object_id = so.object_id
    and dmv.index_id = si.index_id
    and so.object_id = si.object_id
    and database_id = '5' --database ID
    Order by so.name

    select table_name, sum(user_seeks) user_seeks, sum(user_scans) user_scans,
    max(last_user_seek) last_user_seek, max(last_user_scan) last_user_scan
    from test
    WHERE table_name like '%$%$%'
    Group by table_name

Categories