sql-server-2017

About fragmentation in SQL Server

- Extent = 8 pages = 64K.
- On a page split, SQL Server generally moves half the total number of rows in the original page to the new page.
- If new rows are added in the order of the clustered index, then the index rows will be always added at the trailing end of the index, preventing the page splits otherwise caused by the INSERT statements.
- For queries that don’t have to traverse a series of pages to retrieve the data, fragmentation can have minimal impact.
- For a table with a clustered index, the fragmentation of the clustered index is the same with the fragmentation of the data pages, since the leaf pages of the clustered index and data pages are the same.
- A small table (or index) with fewer than eight pages is simply unlikely to benefit from efforts to remove the fragmentation because it will be stored on mixed extents.

Internal fragmentation
- When data is fragmented within the same extent (= 8 pages).
- A little internal fragmentation can be beneficial, because it allows you to perform INSERT and UPDATE queries without causing page splits.

External fragmentation
- When data is fragmented over 2 extents.
- A range scan on an index will need more switches between the corresponding extents than ideally required. A range scan on an index will be unable to benefit from read-ahead operations performed on the disk.
- For better performance, it is preferable to use sequential I/O, since this can read a whole extent (8 x 8KB) in a single disk I/O operation. By contrast, a noncontiguous layout of pages requires nonsequential or random I/O operations to retrieve the index pages from the disk, and a random I/O operation can read only 8KB of data in a single disk operation (this may be acceptable, however, if you are retrieving only one.

Columnstore indexes vs indexed views

Columnstore indexes Indexed views
It didn't require Enterprise Edition in SQL Server 2016 SP1+ Limited usage in non-Enterprise Editions
No session setting requirements Requires certain session settings set on
A lot less storage required More storage required
Less administrative overhead / maintenance More administrative overhead / maintenance
Not able to to inserts/updates prior to SQL Server 2014 Most costly to maintain during inserts/updates

Using computed columns in SQL Server

In a database we need some values that were often calculated while generating several reports.

Assuming we have the following table:

CREATE TABLE InvoiceLine
AS
(
       InvoiceLineID int
       , InvoiceID int
       , NumberOfItems int
       , Amount money
)

And we want to find the total amount of an invoice line. We can achieve this by addding a new computed column:

CREATE TABLE InvoiceLine
AS
(
       InvoiceLineID int
       , InvoiceID int
       , NumberOfItems int
       , Amount money
       , TotalAmount AS (Amount*NumberOfItems) PERSISTED
)

Subscribe to RSS - sql-server-2017