SQLServer性能调优3之索引（Index）的维护 - 数据库编程

e ALTER TABLE SalesOrderDetailDemo ADD [NetPrice] AS [dbo].[UDFTotalAmount] ( OrderQty*UnitPrice,5) GO 3. 建立一个聚集索引，打开性能指标开关，并执行一条查询（注意此时我们还没有在计算字段上建立索引！）

CREATE Clustered Index idx_SalesOrderID_SalesOrderDetailID_
SalesOrderDetailDemo
ON SalesOrderDetailDemo(SalesOrderID,SalesOrderDetailID)
GO
--checking SalesOrderDetailDemo with statistics option ON to
--measure performance
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
--checking SELECT statement without having Index on Computed
Column
SELECT * FROM SalesOrderDetailDemo WHERE NetPrice>5000
GO

输出的性能结果如下：

SQL Server parse and compile time:
CPU time = 650 ms, elapsed time = 650 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(3864 row(s) affected)

Table 'SalesOrderDetailDemo'. Scan count 1, logical reads 757,
physical reads 0, read-ahead reads 0, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 562 ms, elapsed time = 678 ms.

4. 在计算字段上建立索引之前，可以用以下的脚本确认是否满足之前提到的创建要求：（返回值：0不满足，1满足）

SELECT
COLUMNPROPERTY( OBJECT_ID('SalesOrderDetailDemo'),'NetPrice','IsIndexable') AS 'Indexable '
,COLUMNPROPERTY( OBJECT_ID('SalesOrderDetailDemo'),'NetPrice','IsDeterministic') AS 'Deterministic '
,OBJECTPROPERTY(OBJECT_ID('UDFTotalAmount'),'IsDeterministic')'UDFDeterministic '
,COLUMNPROPERTY(OBJECT_ID('SalesOrderDetailDemo'),'NetPrice','IsPrecise') AS 'Precise '

5. 满足要求的情况下建立索引，并再次执行先前的查询语句

CREATE INDEX idx_SalesOrderDetailDemo_NetPrice
ON SalesOrderDetailDemo
(
NetPrice
)
GO

SELECT * FROM SalesOrderDetailDemo WHERE NetPrice>5000
GO

这次的性能结果如下：

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(3864 row(s) affected)

Table 'SalesOrderDetailDemo'. Scan count 1, logical reads 757,
physical reads 0, read-ahead reads 0, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 546 ms, elapsed time = 622 ms.

确认索引占用的磁盘空间

SELECT
  CASE index_id
    WHEN 0 THEN 'HEAP'
    WHEN 1 THEN 'Clustered Index'
    ELSE 'Non-Clustered Index'
  END AS Index_Type,
  SUM(CASE
    WHEN FilledPage > PageToDeduct THEN (FilledPage-PageToDeduct)
    ELSE
      0
  END )* 8 Index_Size
FROM
(
  SELECT
    partition_id,
    index_id,
    SUM (used_page_count) AS FilledPage,
    SUM (
      CASE
        WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
        ELSE
          lob_used_page_count + row_overflow_used_page_count
      END
    ) AS PageToDeduct
  FROM
    sys.dm_db_partition_stats
  GROUP BY
    partition_id,index_id
) AS InnerTable
GROUP BY
  index_id
GO

PS: 输出结果的单位为KB

SQLServer性能调优3之索引（Index）的维护(四)