Finding table/index scans in execution plan and fixing them

In most cases, especially while working with small amount of data from big tables, table scan/index scan should not be the desired way to go for. It becomes mandatory to find and resolve it in order to improve the performance, because scanning process goes through each and every row available in table/index, looks for match with the criteria provided, and returns the result set. This is really a time and resource consuming, heavy process. While working on performance tuning, people are afraid of several major bottleneck issues, mentioned as follows:

• CPU

• Network

• Disk I/O

Table/index scan creates all three types of bottleneck. Scanning every row of a table/index creates a lot of disk I/O due to heavy CPU usage. As it is scanning the whole table/index and preparing a big dataset, it takes heavy network resources and/or bandwidth to deliver the big dataset.

Getting ready

We are going to create two tables to see different effects of physical...