索引调优

 

索引调优

 

 

1 如何查看索引数据的存储是否连续？

 

USE Credit

IF EXISTS (SELECT name FROM Sys.indexes WHERE name = 'idx_LastName')

   DROP INDEX idx_LastName ON Member

 

CREATE INDEX idx_LastName ON Member(LastName)

GO

 

--使用sys.indexs 查看取得该索引原来的FillFactor 设置

select total_pages,data_pages,used_pages,fill_factor,a.* from sys.indexes i

join sys.partitions p on p.object_id=i.object_id and p.index_id=i.index_id

join sys.system_internals_allocation_units a on container_id=hobt_id

where Name= 'idx_LastName'

 

--查看索引数据的存储是否连续

DBCC SHOWCONTIG('Member','idx_LastName')

 

 

--sys.dm_db_index_physical_stats

avg_fragmentation_in_percent

 逻辑碎片（索引中的无序页）的百分比。

fragment_count

 索引中的碎片（物理上连续的叶页）数量。

avg_fragment_size_in_pages

 索引中一个碎片的平均页数。

 avg_fragmentation_in_percent 值 修复语句

< = 30% ALTER INDEX REORGANIZE

> 30%   ALTER INDEX REBUILD WITH (ONLINE = ON)*

 

 

 

 

SELECT a.index_id, name, avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),

     NULL, NULL, NULL) AS a

    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

 

 

 

select index_id,object_id

from sys.indexes

where Name= 'idx_LastName'

 

select hobt_id from sys.partitions

where index_id = '5' and object_id = '2121058592'

 

select total_pages,data_pages,used_pages from  sys.system_internals_allocation_units

where container_id = '72057594040811520'

 

2 主键和聚集索引

主键主要是关乎数据的完整性和正确性；

聚集索引是从数据的运行效率出发的；

主键：唯一、最小、不可为NULL、容易获得、不常变更；

聚集索引：数据格式为整型、唯一、不可为NULL、键值要小；

非聚集索引，当符合条件的记录占总数的比例很大时，其效率很低；

 

 

3 是否值得创建索引

（）选择性

选择性=符合条件的记录数目/总记录条数

其值越小越适合采用索引

（）数据密度

数据密度= 1/键值唯一的记录数

其值越小越适合采用索引

（）数据分布

 

 

 

--用with，强迫使用特定索引

select * from Member

with (Index(idx_LastName)) where Lastname between 'Marti' and 'Rudd'

 

 

--用drop_existing重建聚集索引效果更好

create clustered  index idx_LastName on member(lastname) with drop_existing

 

--建立排序索引

create clustered  index idx_LastName on member(lastname asc,firstname desc) with drop_existing

 

3 与索引有关的系统视图

select * from sys.indexes

 

select * from sys.partitions

 

select * from sys.allocation_units

 

select * from sys.system_internals_allocation_units

 

--通过以下DBCC命令可以查看内部结构

DBCC TRACEON(3604)

DBCC PAGE(DB,1,115,3)

DBCC EXTENTINFO(DB,T)

DBCC IND(DB,T,-1)

 

--通过系统查看数据库内表使用的硬盘空间分配

SELECT a3.name AS [Schema 名称],

    a2.name AS [表名称],

    a1.rows as 记录条数,

    (a1.reserved + ISNULL(a4.reserved,0))* 8 AS [保留空间(K)],

    a1.data * 8 AS [数据使用空间(k)],

    (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data

    THEN (a1.used + ISNULL(a4.used,0)) - a1.data

    ELSE 0 END) * 8 AS [索引使用空间(k)],

    (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used

    THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used

    ELSE 0 END) * 8 AS [未用空间(k)],

    a1.data * 8*1024/(CASE WHEN a1.Rows=0 THEN 1 ELSE a1.Rows END) 平均每条记录长度

    FROM

       (

       SELECT

       ps.object_id,

       SUM (

       CASE

       WHEN (ps.index_id < 2) THEN row_count

       ELSE 0

       END

       ) AS [rows],

       SUM (ps.reserved_page_count) AS reserved,

       SUM (

       CASE

       WHEN (ps.index_id < 2) THEN

       (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)

       ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)

       END

       ) AS data,

       SUM (ps.used_page_count) AS used

       FROM sys.dm_db_partition_stats ps

       GROUP BY ps.object_id) AS a1

       LEFT OUTER JOIN

           (

           SELECT

           it.parent_id,

           SUM(ps.reserved_page_count) AS reserved,

           SUM(ps.used_page_count) AS used

           FROM sys.dm_db_partition_stats ps

           INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)

           WHERE it.internal_type IN (202,204)

           GROUP BY it.parent_id

           ) AS a4 ON (a4.parent_id = a1.object_id)

    INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )

    INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)

    WHERE a2.type <> N'S' and a2.type <> N'IT'

    ORDER BY [保留空间(K)] DESC

   

4 观察索引使用

select * from sys.dm_db_missing_index_groups

select * from sys.dm_db_missing_index_group_stats

select * from sys.dm_db_missing_index_details

 

SELECT mig.*, statement AS table_name,

    column_id, column_name, column_usage

FROM sys.dm_db_missing_index_details AS mid

CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)

INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle

ORDER BY mig.index_group_handle, mig.index_handle, column_id;

 

5 通过停用聚簇索引来停用某个表

Use Tempdb

--如果Clustered Index 被Disable，则整个表都不可用

CREATE TABLE tblT1(C1 INT)

CREATE CLUSTERED INDEX idxC1 ON tblT1(C1)

ALTER INDEX idxC1 ON tblT1 DISABLE

 

--使用时，会有以下的错误

--信息8655，层级16，状态1，行1

--查看处理器无法产生计划，因为表或查看'tblT1' 上的索引'idxC1' 已停用。

SELECT * FROM tblT1

INSERT tblT1 VALUES(1)

ALTER INDEX idxC1 ON tblT1 REBUILD

DROP TABLE tblT1

 

 

6 通过SET STATISTICS查看查询语句使用的资源

 

SET STATISTICS TIME ON

GO

SELECT *

FROM Production.ProductCostHistory

WHERE StandardCost < 500.00;

GO

SET STATISTICS TIME OFF;

GO

 

SET STATISTICS IO ON;

GO

SELECT *

FROM Production.ProductCostHistory

WHERE StandardCost < 500.00;

GO

SET STATISTICS IO OFF;

GO

 

-- 创建统计

CREATE STATISTICS ContactMail1

    ON Person.Contact (ContactID, EmailAddress)

 

--高速缓存击中率

高速缓存击中率=（Logical reads - Physical Reads）/Logical reads

 

7 索引级数

通常索引只有级（几千行），对于大表，包含级（行）或级（行）

 

8 索引访问方法

（）表扫描/无序聚集索引扫描

（）无序覆盖非聚集索引扫描

（）有序聚集索引扫描

（）有序覆盖非聚集索引扫描

（）非聚集索引查找+有序局部扫描+lookups

（）无序非聚集扫描+lookups

（）聚集索引查找+有序局部扫描

（）覆盖非聚集索引查找+有序局部扫描

 

9 索引优化等级

表扫描/无序聚集索引扫描和无序覆盖非聚集索引扫描与选择性无关；

无序非聚集扫描+lookups 、非聚集索引查找+有序局部扫描+lookups、

聚集索引查找+有序局部扫描、覆盖非聚集索引查找+有序局部扫描选择性越低速度越快。

 

 

 

SELECT *

FROM sys.dm_db_index_operational_stats(

  DB_ID('Performance'), null, null, null);

 

  SELECT *

FROM sys.dm_db_index_usage_stats;

user_updates 计数器指示由基础表或视图上的插入、更新或删除操作所引起的索引维护级别。可使用此视图确定应用程序只是少量使用的索引，或根本未使用的索引。还可以使用此视图确定引发维护开销的索引。

您可能要删除引发维护开销但不用于查询或只是偶尔用于查询的索引。

 

 

10 数据准备抽样

SELECT *

FROM dbo.Orders TABLESAMPLE SYSTEM (1000 ROWS);

 

SELECT *

FROM dbo.Orders TABLESAMPLE (0.1 PERCENT);

 

SELECT TOP(1000) *

FROM dbo.Orders TABLESAMPLE (2000 ROWS);

 

11 查询索引大小

 

SELECT object_name(object_id) AS name,

    partition_id, partition_number AS pnum, rows,

    allocation_unit_id AS au_id, type_desc as page_type_desc,

    total_pages AS pages

FROM sys.partitions p JOIN sys.allocation_units a

     ON p.partition_id = a.container_id

WHERE object_id=object_id('dbo.hugerows_with_text ');

 

--查看空间

SELECT object_name(object_id) AS name,

    partition_id, partition_number AS pnum, rows,

    allocation_unit_id AS au_id, type_desc as page_type_desc,

    total_pages AS pages

FROM sys.partitions p JOIN sys.allocation_units a

   ON p.partition_id = a.container_id

WHERE object_id=object_id('dbo.employee')