Table Scan, Index Scan, Index Seek
来源:互联网 发布:爱名网备案域名 编辑:程序博客网 时间:2024/04/28 13:08
A table scan is where the table is processed row by row from beginning to end.
An index scan is where the index is processed row by row from beginning to end.
If the index is a clustered index then an index scan is really a table scan .
When you see an index scan on a clustered index that is actually a table scan; since the root of the clustered index is the table.
Scan means it has to run the entire object, seeks will make specific hits into the index/table.
What is the difference between a table and index scan in an execution plan?
Question
When I create a graphical query execution plan of a query, I notice that there are two types of scans: Table Scans and Index Scans. How are these different?
Answer
When the Query Optimizer is asked to optimize a query and create an execution plan for it, it tries its best to use an Index Seek. An Index Seek means that the Query Optimizer was able to find a useful index in order to locate the appropriate records. As you probably know, indexes make data retrieval in SQL Server very fast.
But when the Query Optimizer is not able to perform an Index Seek, either because there is no indexes or no useful indexes available, then SQL Server has to scan all the records, looking for all the records that meet the requirements of the query.
There are two types of scans the SQL Server can perform. When a Table Scan is performed, all the records in a table are examined, one by one. For large tables, this can take a long time. But for very small tables, a table scan can actually be faster than an Index Seek . So if you see that SQL Server has performed a Table Scan, take a note of how many rows are in the table. If there aren't many, then in this case, a Table Scan is a good thing.
When an Index Scan is performed, all the rows in the leaf level of the index are scanned. What does this mean? Essentially, this means that all of the rows of the table or the index are examined instead of the table directly. Sometimes, the Query Optimizer determines that an Index Scan is more efficient than a Table Scan, so one is performed, although the performance difference between them is generally not much.
You might ask that if there is an index available, why can't an Index Seek be performed? In some cases, such as if a huge quantity of rows need to be returned, it is faster to do an Index Scan than an Index Seek . Or it may be because the index is not selective enough. In any case, the Query Optimizer doesn't think the available index is useful, other than for performing an Index Scan.
So what does all this mean from an analysis standpoint? Generally speaking, an Index Scan or an Index Seek is almost the same thing, from a performance perspective. If you see any one of these in a query execution plan, the first thing you need to do is to see if there are few rows in the table. If so, then a scan is OK. Or, if many rows are being returned, then a scan is often faster than an Index Seek, and the Query Optimizer made the correct choice of selecting a scan . The only way to speed up this particular situation would be to find a way to rewrite the query in order to return fewer rows, assuming this is possible.
If the above two reasons don't apply, then your next step would be to try to identify useable indexes to help speed the performance of the query, assuming that the current performance of the query is unacceptable, so that an Index Seek is performed instead of an Index or Table Scan.
- Table Scan, Index Scan, Index Seek
- Table Scan, Index Scan, Index Seek
- Table Scan, Index Scan, Index Seek
- Index Seek和Index Scan
- index seek与index scan
- index seek与index scan
- index seek与index scan .
- Sql Server中的表访问方式Table Scan, Index Scan, Index Seek
- Sql Server中的表访问方式Table Scan, Index Scan, Index Seek
- Sql Server中的表访问方式Table Scan, Index Scan, Index Seek
- sql server中clustered index scan,table scan,index scan
- Index Seek和Index Scan的区别以及适用情况
- [Index]Index Scan
- index range scan,index fast full scan,index skip scan
- index unique scan vs index full scan
- INDEX SKIP SCAN和INDEX FULL SCAN
- PostgreSQL index scan,bitmap index scan区别
- INDEX SKIP SCAN 和 INDEX RANGE SCAN
- 鸟哥的Linux私房菜
- Hello World
- HBase无法连接ZooKeeper问题
- MyEclipse下载,安装,激活和简单使用
- 多线程
- Table Scan, Index Scan, Index Seek
- 如何利用 JConsole观察分析Java程序的运行,进行排错调优
- 影响网页渲染的关键!
- 本文介绍十五道关于Hibernate的面试题及答案
- extern "C" 详解
- Java教程(一)----HelloWorld,注释
- DHCP服务器的搭建
- 设计模式
- atitit.高级编程语言的特性 and 未来趋势与进化.doc