sqlserver索引优化扫描

SELECT name AS stats_name,

    STATS_DATE(object_ID,stats_ID) AS statistics_update_date

FROM sys.stats

WHERE object_ID = OBJECT_ID('dbo.table_1');

GO

--扫描数据库testdb的缺失索引及参考指标

use testdb

SELECT

    c.name AS databasename,

    c.equality_columns,

    c.inequality_columns,

    c.included_columns,

    c.statement AS tablename,

    c.avg_total_user_cost AS ReducingTheAverageCost,

    c.avg_user_impact AS PercentageOfRevenue,

    c.last_user_seek AS TheLastTimeTheEffectAfterUse,

    c.unique_compiles

FROM (SELECT

    a.name,

    b.*

FROM   (SELECT

           d.*,

           s.avg_total_user_cost,

           s.avg_user_impact,

           s.last_user_seek,

           s.unique_compiles

       FROM   sys.dm_db_missing_index_group_stats s,

              sys.dm_db_missing_index_groups g,

              sys.dm_db_missing_index_details d

       WHERE s.group_handle = g.index_group_handle

       AND d.index_handle = g.index_handle

       AND s.avg_user_impact > 90

       --AND s.unique_compiles > 10

       --order by s.avg_user_impact desc

       ) b,

       sys.databases a

WHERE a.database_ID = b.database_ID) c

WHERE c.name = 'testdb'

ORDER BY PercentageOfRevenue DESC,unique_compiles DESC

USE [ydt2012sjk]

GO

CREATE NONCLUSTERED INDEX  indexname

ON [dbo].[ CustomsProducts] ([Status],[ Proname],[ HSCode])

INCLUDE ([ID],[ GuestID])

With (online=on)

GO

--indexname 需要自己起一个没有用户过的索引名称

-- With (online=on)在线增加索引，减少因加索引而影响业务。

use databasename

--没有使用的索引

SELECT  ind.index_ID,

        obj.name AS tablename,

        ind.name AS Indexname,

        ind.type_desc,

        indUsage.user_seeks,

        indUsage.user_scans,

        indUsage.user_lookups,

        indUsage.user_updates,

        indUsage.last_system_seek,

        indUsage.last_user_scan,

        'drop index [' + ind.name + '] ON [' + obj.name + ']' AS DropIndexCommand

FROM    sys.indexes AS ind

        INNER JOIN sys.objects AS obj ON ind.object_ID = obj.object_ID

        left JOIN sys.dm_db_index_usage_stats indUsage ON ind.object_ID = indUsage.object_ID

                                                          AND ind.index_ID = indUsage.index_ID

WHERE   ind.type_desc <> 'HEAP'

        AND obj.type <> 'S'

        AND obj.create_date < '2014-4-5'

        And ind.name not like 'PK_%'

        And ind.name not like 'AK_%'

        And ind.name not like 'UK%'

        AND OBJECTPROPERTY(obj.object_ID,'isusertable') = 1

        AND ( ISNulL(indUsage.user_seeks,0) = 0

              AND ISNulL(indUsage.user_scans,0) = 0

              AND ISNulL(indUsage.user_lookups,0) = 0

            )

        AND user_updates > 10

ORDER BY   ind.name

GO

-- 更新次数太多，而使用次数很少,下面的3000和100 这个两个数值，是定义使用少的标准，100是用户使用索引进行的扫描、查找的总次数，这个数值越小说明使用的次数越少。而3000是指为数据发生改变需要维护索引，对索引数据修改次数。用户可以定义自己的标准。来描述使用少的标准。

SELECT  ind.index_ID,

        'drop index [' + ind.name + '] ON [' + obj.name + ']' AS DropIndexCommand

FROM    sys.indexes AS ind

        INNER JOIN sys.objects AS obj ON ind.object_ID = obj.object_ID

        left JOIN sys.dm_db_index_usage_stats indUsage ON ind.object_ID = indUsage.object_ID

                                                          AND ind.index_ID = indUsage.index_ID

WHERE   ind.type_desc <> 'HEAP'

        AND obj.type <> 'S'

        And ind.name not like 'PK_%'

        And ind.name not like 'AK_%'

        And ind.name not like 'UK%'

        AND OBJECTPROPERTY(obj.object_ID,'isusertable') = 1

        AND user_scans + user_seeks + user_lookups < indUsage.user_updates

        AND user_scans + user_seeks + user_lookups < 100

        AND indUsage.user_updates > 3000

ORDER BY indUsage.user_updates desc

GO