Find Not used Sift levels

JedrzejT

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

stryk

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

JedrzejT

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

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 ...

bonehead

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

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 ...

Nobody

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

---