Hi All,

if someone can point me to documentation on this I would appreciate it.....

If there isn't any....

I am wondering about the behavior of SQL Server for table scans. In other databases tables scans are not really table scans, they are scans of the underlying tablespace for all the rows that are in the table.....and if many tables are placed into the same tablespace then the obvious slowdown occurs as rows are scanned that are not in the table.

This used to be the case in server 7......but is it still the case in 2005 that if the explain says 'table scan' it will in fact scan the filegroup the table is in

Some other databases also have a map of the row numbers and the table it is in and the optimiser decides whether to scan the data itself or to navigate through the map and fetch a row at a time depending on the stats....

It seems that the grahical explain does not tell me more than 'table scan'. Is there any way to see down to the physical level of what the optimiser is going to do

Thank You in Advance


Re: Behavior of SQL Server on table scans

Peter Byrne MSFT

SQL Server will only scan the pages that belong to the table being scanned, though read ahead may be issued that causes some amount of data from other tables to be read in as well depending on the scan. Those pages will not actually be looked at.

Re: Behavior of SQL Server on table scans


Hi Peter,

thanks for your there any good document to read on this I am happy to do my 'homework'.

I'd be interested to know how it can only read pages with rows from the table as my understanding is that when tables cohabit a file group, such as primary, that rows from different tables can co-habit the same pages and therefore I would think it will read at least every page on which a row from that table is present.

We are testing to see if we can see any performance differences by placing large tables in their own file groups.

As a broader comment/question.....and I guess to other people here too....I am not sure if it is just me not able to find details on the optimiser and how it is working in a BI environment or if there is just not a great level of detail around. I've read all that people have pointed to but I still feel 'short on hard information' as to what the optimiser does.

We are working on building up documentation for our team on the optimiser and how it works and we have cut/paste all we can find.....but we are still struggling to influence the optimiser....are we missing something Is there any one web page or any one document out of MSFT that is the 'bible' on optimsing dimensional models on SQL Server I would have thought that by now someone would have sat down and written the definitive guide on large dimensional models and the 2005 optimser.

As background.....

I have been doing BI for 15 years and done many projects on DB2, Oracle, Sybase IQ as well as a few others. one on SQL Server 7 some 7 years ago. I used to be something of an expert with optimisers........We are doing our first large scale testing on a new product we have developed which has a dimensional model for the DW at it's base. We have read presentations on 'What We learned in the first year' which references very large DWs on SQL Server. We have also read details of Project we read about all these 'large scale DWs' on SQL Server 2005 but I am surprised at not being able to find more information about influencing the optimiser or the setting up of file groups, indexes etc for a dimensional model.....hence my posts here and hence us looking around.....

So anyone who can point us to more information as to how the optimiser works, the assistance would be greatly appreciated..

Best Regards