axStart: Cluster index

[Blog bot]

Источник: http://www.axstart.com/cluster-index...=cluster-index
==============

These days we have the surrogate key in AX 2012. In general it is a recid Index that acts as primary and cluster index. Theoretically this looks perfect as long as we develop in the AX 2012 style. But there is a catch to it. First you may think this index can’t get fragmented. This is true only when one AOS is running; when multiple AOSs are running, they all have their recid pool; so getting the latest record should not be “order by Recid DESC” but “order by createdDatetime” DESC.

What is the most common search query on the table? In case your code uses the recid in the find method, it is OK. Personally I can’t predict the most common search query on any table, especially when code is not live for a while and upgrade or conversion code is running.

So, how to solve this riddle; Well I found this blog post http://sqlserverplanet.com/indexes/c...lustered-index

It is a clever query

DECLARE @NonClusteredSeekPct float
DECLARE @ClusteredLookupFromNCPct float
SET @NonClusteredSeekPct = 1.50 — 150%
SET @ClusteredLookupFromNCPct = .75 — 75%

SELECT
    TableName                   = object_name(idx.object_id)
    ,NonUsefulClusteredIndex    = idx.NAME
    ,ShouldBeClustered          = nc.NonClusteredName
    ,Clustered_User_Seeks       = c.user_seeks
    ,NonClustered_User_Seeks    = nc.user_seeks
    ,Clustered_User_Lookups     = c.user_lookups
    ,DatabaseName               = db_name(c.database_id)
FROM sys.indexes idx
LEFT JOIN sys.dm_db_index_usage_stats c
ON idx.object_id = c.object_id
AND idx.index_id = c.index_id
JOIN (
    SELECT idx.object_id,nonclusteredname = idx.NAME,ius.user_seeks
    FROM sys.indexes idx
    JOIN sys.dm_db_index_usage_stats ius
    ON idx.object_id = ius.object_id AND idx.index_id = ius.index_id
    WHERE idx.type_desc = ‘nonclustered’ AND ius.user_seeks =
    (
        SELECT MAX(user_seeks) FROM sys.dm_db_index_usage_stats
        WHERE object_id = ius.object_id AND type_desc = ‘nonclustered’
    )
    GROUP BY idx.object_id,idx.NAME,ius.user_seeks
) nc
ON nc.object_id = idx.object_id
WHERE idx.type_desc IN (‘clustered’,'heap’)
AND nc.user_seeks > (c.user_seeks * @NonClusteredSeekPct)
AND nc.user_seeks >= (c.user_lookups * @ClusteredLookupFromNCPct)
ORDER BY TableName

 As you can see we still have a long way to go (it is default AX)





Источник: http://www.axstart.com/cluster-index...=cluster-index