Index Seek 和Index Scan的性能差别

最近碰到几个年轻的程序员，惊讶的发现，很多人对数据库的认识只停留在对表的select，update，insert和delete，连最最基本的聚集索引和非聚集索引都搞不清！

所以今天通过一个案例先来讲讲非聚集索引的seek和scan的性能差别。

1.问题描述

A服务器在工作时间段整体CPU较基线偏高，且有越来越高的趋势，虽然暂时在可接受范围内，但本着防患于未然的原则，应该引起重视。

2.发现问题

首先我们要找出开销较大的sql语句，看看是否有空间优化。

方法很多种，最简单的是通过DMV的sys.dm_exec_query_stats。笔者这里提供了一段脚本，供大家参考。

SELECT TOP 20
[DatabaseName] = DB_NAME(qt.dbid)
,[Individual Query] = SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1)
,[Parent Query] = qt.text 
, qp.query_plan
, qs.plan_generation_num
, qs.creation_time
, qs.last_execution_time
,[Total Elapsed Duration (s)] = CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
--,qs.total_elapsed_time
,[Execution count] = qs.execution_count
,[Average Duration (s)] = CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count AS DECIMAL(28, 2))
,[Total CPU time (s)] = CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2))
,[CPU time average (s)] =  CAST((qs.total_worker_time) / 1000000.0 / qs.execution_count AS DECIMAL(28, 2))
,[% CPU] = CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28, 2))
,[% Waiting] = CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / qs.total_elapsed_time AS DECIMAL(28, 2))
,[Total time blocked (s)] = CAST((qs.total_elapsed_time - qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2))
,[Blocking average (s)] = CAST((qs.total_elapsed_time - qs.total_worker_time) / 1000000.0 / qs.execution_count AS DECIMAL(28, 2))
,[Total IO] = (qs.total_logical_reads + qs.total_logical_writes)
,[Average IO] = (qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_elapsed_time<> 0
ORDER BY [Total CPU time (s)] DESC

以下是找出的cpu开销很大的语句。

select sn,CallID,SensitiveString,RPT_Type from dbo.User_PWd where callid=N'00001073sdfadfas0218905'

该语句有明显的索引缺失问题，单次运行成本不是最优

 

3.分析问题

该语句比较简单，表User_PWd没有合适的索引，导致tablescan

逻辑读消耗很高：

    Table 'User_PWd'. Scan count 1, logical reads 2613, physical reads 22, read-ahead reads 2613, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

4.解决问题

对User_PWd增加索引：

CREATE INDEX idx_CallID ON [dbo].[User_PWd] ([CallID]) INCLUDE ([SN],[SensitiveString],[RPT_Type]) with (online = on)

 

逻辑读只有3

Table 'User_PWd'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

cpu成本由0.34减小到0.00328。

之后观察CTI服务器的CPU，已经基本在到基线水平以下。