索引执行的搜索次数和扫描次数有什么区别? RRS feed

  • 问题

  • 如题,请各位尽可能详细的解释一下。



      select as '索引名称',
     user_seeks as '通过用户查询执行的搜索次数',--索引列作为搜索条件的使用次数,如:where pk_column=3
     user_scans as '通过用户查询执行的扫描次数',--用非索引列作为查询条件进行查询时,扫描索引列的次数(是查询计划执行时的扫描次数?还是查询过程中执行的扫描次数?)
     last_user_seek as '用户上次执行搜索的时间',
     last_user_scan as '用户上次执行扫描的时间'
      from sys.dm_db_index_usage_stats s
      inner join
      sys.indexes I
      I.object_id = s.object_id and
      I.index_id = s.index_id
     database_id = db_id(N'DB_name') and
     s.object_id  = object_id(N'tb_name');

    • 已编辑 Ronins 2014年6月20日 8:06
    2014年6月19日 7:46


  • 关于 SCAN COUNT meaning in SET STATISTICS IO output:

    Thumb rules

    1. Scan count is 0 if the index that you are using is a unique index or clustered index on a primary key and you are seeking for only one value. Eg. WHERE Primary_Key_Column = <value>
    2. Scant count is 1 when you are searching for one value using a non-unique clustered index which is defined on a non-primary key column. This is done to check for duplicate values for the key value that you are searching for. Eg. WHERE Clustered_Index_Key_Column = <value>
    3. Scan count is N when N is the number of different seek/scan started towards the left or right side at the leaf level after locating a key value using the index key.

    Please Mark As Answer if it is helpful.

    • 已标记为答案 Ronins 2014年6月20日 8:01
    2014年6月20日 1:21