SQLServer高级进阶之索引优化查询

database

1.1、查找缺失索引

SELECT A.USER_SEEKS 查找次数,A.USER_SCANS 扫描次数,

ROUND(A.AVG_TOTAL_USER_COST,2) 减少的用户查询的平均成本,A.AVG_USER_IMPACT 可能获得的平均百分比收益,

ROUND((A.USER_SEEKS+A.USER_SCANS)*A.AVG_TOTAL_USER_COST*A.AVG_USER_IMPACT/100,2) 可能的改进优势,

A.LAST_USER_SEEK 最近查找时间,A.LAST_USER_SCAN 最近扫描时间,C.[STATEMENT] 表名,

"CREATE INDEX [IDX_"

+CONVERT(VARCHAR,A.GROUP_HANDLE)+"_"+CONVERT(VARCHAR,C.INDEX_HANDLE)+"_"+REPLACE(REPLACE(REPLACE(C.[STATEMENT],"]",""),"[",""),".","")

+"]"+" ON "+C.[STATEMENT]+" ("

+ISNULL(C.EQUALITY_COLUMNS,"")

+CASEWHENNOT C.EQUALITY_COLUMNS ISNULLANDNOT C.INEQUALITY_COLUMNS ISNULLTHEN","ELSE""END

+ISNULL(C.INEQUALITY_COLUMNS,"")

+")"

+ISNULL(" INCLUDE ("+C.INCLUDED_COLUMNS+")","") "创建语句"

FROM sys.dm_db_missing_index_group_stats A INNERJOIN sys.dm_db_missing_index_groups B ON A.GROUP_HANDLE=B.INDEX_GROUP_HANDLE

INNERJOIN sys.dm_db_missing_index_details C ON B.INDEX_HANDLE=C.INDEX_HANDLE

WHERE C.DATABASE_ID=DB_ID() --默认当前数据库,若指定数据库则使用DB_ID(["DB_NAME"])

ORDERBYROUND(A.USER_SEEKS*A.AVG_TOTAL_USER_COST*A.AVG_USER_IMPACT/100,2) DESC

1.2、查找未使用索引

SELECT C.NAME 表名,B.INDEX_ID 索引ID,B.NAME 索引名,

A.USER_SEEKS 搜索次数,A.USER_SCANS 扫描次数,A.USER_LOOKUPS 查找次数,

A.USER_UPDATES 更新次数,E.TABLEROWS 表行数,

"DROP INDEX "+QUOTENAME(B.NAME)+" ON "+QUOTENAME(D.NAME)+"."+QUOTENAME(OBJECT_NAME(A.OBJECT_ID)) "删除语句"

FROM sys.dm_db_index_usage_stats A INNERJOIN sys.indexes B ON A.INDEX_ID=B.INDEX_ID AND A.OBJECT_ID=B.OBJECT_ID

INNERJOIN sys.objects C ON A.OBJECT_ID=C.OBJECT_ID

INNERJOIN sys.schemas D ON C.schema_id=D.schema_id

INNERJOIN

(

SELECT INDEX_ID,OBJECT_ID,SUM(ROWS) TABLEROWS

FROM sys.partitions

GROUPBY INDEX_ID,OBJECT_ID

) E ON A.INDEX_ID=E.INDEX_ID AND A.OBJECT_ID=E.OBJECT_ID

WHEREOBJECTPROPERTY(A.OBJECT_ID,"IsUserTable")=1AND A.DATABASE_ID=DB_ID()

AND B.TYPE_DESC="NONCLUSTERED"AND B.IS_PRIMARY_KEY=0AND B.IS_UNIQUE_CONSTRAINT=0

--AND C.NAME="INVMB" --根据实际修改表名

ORDERBY (A.USER_SEEKS+A.USER_SCANS+A.USER_LOOKUPS) ASC

当更新次数很大而搜索次数及扫描次数很小或为0时,说明该索引一直在更新但基本不被使用,因而也未对查询提供多少帮助,所以可以考虑删除。

1.3、查看索引使用情况

SELECTOBJECT_NAME(A.[OBJECT_ID]) 表名,B.INDEX_ID 索引ID,B.[NAME] 索引名称,B.[TYPE_DESC] 索引类型,

A.USER_SEEKS+A.USER_SCANS+A.USER_LOOKUPS 读,A.USER_UPDATES 写,B.FILL_FACTOR 填充因子

FROM sys.dm_db_index_usage_stats A INNERJOIN sys.indexes B ON A.[OBJECT_ID]=B.[OBJECT_ID]AND A.INDEX_ID=B.INDEX_ID

WHEREOBJECTPROPERTY(A.[OBJECT_ID],"ISUSERTABLE")=1

AND A.DATABASE_ID=DB_ID() --默认当前数据库,若指定数据库则使用DB_ID(["DB_NAME"])

ORDERBYOBJECT_NAME(A.[OBJECT_ID]),A.USER_UPDATES DESC,A.USER_SEEKS+A.USER_SCANS+A.USER_LOOKUPS DESC

 

以上是 SQLServer高级进阶之索引优化查询 的全部内容, 来源链接: utcz.com/z/535974.html

回到顶部