pages

Data and other stuff is store in 8K pages in groups of 8 extents.  The 8K page is the smallest unit that the server will cache.

A datapage can hold as many records as will fit on page, given the fill factor and additional metadata, but only one object (one index, CI, NCI)  at a time.

Data from a single table or index.  If there is no clustered index, the data is in a heap and arranged at no particular order on the page.  If there is a clustered index on the records, the data is arranged across pages in order of the index.  The data on a PAGE is not required to be in the sort order, and the order is held in the …. which is part of the meta data on each page.  finally, a non-clustered index is pages of data which is a subset of the original (only included or covered fields) and the key with a pointer to the original table (CI or heap).

For a CI, the page is the all data.  for a Heap, also.  For an index (NCI), it is another page of data referencing the CI or Heap.

For an object (heap, CI or NCI) we can determine the number of 8K pages from the  sys.dm_db_index_physical_stats Dynamic Management Function (DMF) or sp_spaceused stored procedure.

Organization of the data (not necessarily the tables) is in a b plus-tree, which contains at least a root (index rows) and a leaf (more intermediate pages if there is more data).  In a clustered index object, the leaf is the data (or parts of it); in aNCI object, the leaf is a pointer to the page of the CI or of the heap.

Indexes have key columns (on which the index is based) and in a NCI there also may be non-key columns which are “included” in the index.

Data selected from a heap is by table scan unless there is a NCI on one or more columns which are the arguments in the select.  Data from a CI that is not the key column is also a table scan unless there is a NCI on a column that is the selection criteria.  A index scan on a CI will return all the data for that records, an index scan on a heap will return a pointer to the record, an index scan on a heap where the index has all the needed data (or has an include that covers the request) will not need to return the heap data.

If a table is small, the additional cost of indexes (especially NCI) make them too expensive.  With a NCI this also means additional pages that have to be maintained and possibly cached.

Advertisements