I shudder whenever I see comments that full tablescans are bad and index access is good. Full table scans, index range scans, fast full index scans, nested loops, merge join, hash joins etc. are simply access mechanisms that must be understood by the analyst and combined with a knowledge of the database structure and the … Read more
An index scan is where SQL server reads the whole of the index looking for matches – the time this takes is proportional to the size of the index. An index seek is where SQL server uses the b-tree structure of the index to seek directly to matching records (see http://mattfleming.com/node/192 for an idea on … Read more
The best explanation comes from Tom Lane, which is the algorithm’s author unless I’m mistaking. See also the wikipedia article. In short, it’s a bit like a seq scan. The difference is that, rather than visiting every disk page, a bitmap index scan ANDs and ORs applicable indexes together, and only visits the disk pages … Read more
There are a number of methods of obtaining an execution plan, which one to use will depend on your circumstances. Usually you can use SQL Server Management Studio to get a plan, however if for some reason you can’t run your query in SQL Server Management Studio then you might find it helpful to be … Read more