SQL Index

 1 Index Overview 1

2 Terms 2

2.1 SYS.DM_DB_INDEX_PHYSICAL_STATS 2

3 What is index fragmentation? 2

4 Types of Fragmentation 2

4.1 INTERNAL FRAGMENTATION 2

4.2 External Fragmentation: 2

5 How to measure fragmentation? 2

5.1 To Find out fragmentation level of a given database 2

6 How to address fragmentation issues with indexes? 3

6.1 OPTION 1: REACTIVE APPROACH 3

6.1.1 INDEX REBUILD - TO RECREATE ALL INDEX PAGES 3

6.1.2 INDEX REORGANIZE - TO REARRANGE THE FRAGMENTED INDEX PAGES 3

6.2 OPTION 2: PROACTIVE APPROACH 3

7 Index Management Example 4

8 Page Density 5

1. Index Overview 

Applies to:  SQL Server  Azure SQL Database  Azure SQL Managed Instance  Analytics Platform System (PDW)

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16

•  Optimize index maintenance to improve query performance and reduce resource consumption.

• The Database Engine automatically modifies indexes whenever insert, update, or delete operations are made to the underlying data

• heavily fragmented indexes can degrade query performance because additional I/O may be required to read the data required by the query

2. Terms 

1. SYS.DM_DB_INDEX_PHYSICAL_STATS

3. What is index fragmentation?

• A phenomena in which the data pages are not in continuation with relevant index pages.

• Means, index pages and data pages are not in sync.

• Impact:

• Index scan (reads) and index seek (searches) takes more time to execute. 

• Adverse impact on query performance. Means, the queries run slow.

4. Types of Fragmentation 

1. INTERNAL FRAGMENTATION

• When records are stored non-contiguously inside the page,  then it is called internal fragmentation. In other words, internal fragmentation is said to occur if there is unused space between records in a page. 

• This fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) that are made against the table  and therefore, to the indexes defined on the table.

2. External Fragmentation:

• External Fragmentation: When on disk, the physical storage of pages and extents is not contiguous. When the extents of a table are not physically stored  contiguously on disk, switching from one extent to another  (EXTENT SWITCH) causes higher disk rotations, and this is called Extent Fragmentation.

5. How to measure fragmentation?

SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (null, null, null, null, null)

1ST PARAMETER: DATABASE ID. OR NULL   : MEANS ALL DATABASES

2ND PARAMETER: OBJECT ID. OR NULL   : MEANS ALL TABLES & VIEWS

3RD PARAMETER: INDEX ID. OR NULL   : MEANS ALL INDEXES

4TH PARAMETER: PARTITION NUMBER. OR NULL   : MEANS ALL PARTITIONS

5TH PARAMETER: NULL  (SAMPLED ** / DETAILED)

select * from sys.dm_db_index_physical_stats(db_id(N'AdventureWorks2022'), NULL, NULL, NULL , 'DETAILED')

1. To Find out fragmentation level of a given database

--This query will give DETAILED information

--CAUTION : It may take very long time, depending on the number of tables in the DB

USE AdventureWorks

GO

SELECT object_name(IPS.object_id) AS [TableName], 

   SI.name AS [IndexName], 

   IPS.Index_type_desc, 

   IPS.avg_fragmentation_in_percent, 

   IPS.avg_fragment_size_in_pages, 

   IPS.avg_page_space_used_in_percent, 

   IPS.record_count, 

   IPS.ghost_record_count,

   IPS.fragment_count, 

   IPS.avg_fragment_size_in_pages

FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks'), NULL, NULL, NULL , 'DETAILED') IPS

   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id

   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id

WHERE ST.is_ms_shipped = 0

ORDER BY 1,5

GO

6. How to address fragmentation issues with indexes?

1. OPTION 1: REACTIVE APPROACH

1. INDEX REBUILD - TO RECREATE ALL INDEX PAGES

• APPLICABLE FOR HIGHER FRAGMENTATION LEVELS. ABOVE 30% 

• SYNTAX:  ALTER INDEX <INDEXNAME> ON <TABLENAME>  REBUILD

2. INDEX REORGANIZE - TO REARRANGE THE FRAGMENTED INDEX PAGES

• APPLICABLE FOR LOWER FRAGMENTATION LEVELS. BETWEEN 15% AND 30%

• SYNTAX: ALTER INDEX <INDEXNAME> ON <TABLENAME>  REORGANIZE

2. OPTION 2: PROACTIVE APPROACH

• ENSURE AUTOMATED, SCHEDULED INDEX REORGANIZATION @ "DATABASE  INTENANCE PLANS".  THIS PREVENTS FRAGMENTATION.

STEPS TO CONFIGURE DATABASE MAINTENANCE PLANS :

STEP 1: LAUNCH SSMS >> CONNECT TO SERVER 

STEP 2: RIGHT CLICK AGENT > START

STEP 3: FROM SERVER >> MANAGEMENT >> MAINTENANCE PLANS >> MAINTENANCE PLAN WIZARD >> 

SPECIFY A NAME > SPECIFY A SCHEDULE [EVERYDAY @ 12 AM SERVER TIME] > 

SELECT BELOW ITEMS:

a. INDEX REORGANIZE

b. UPDATE STATISTICS

SPECIFY THE ORDER AS LISTED ABOVE. 

STEP 4: INDEX REROGANIZE:: SELECT ALL DATABASES

COMPRESS LARGE OBJECTS 

SCAN TYPE (FAST, SAMPLED, DETAILED *)

OPTIMIZATION OPTIONS:

FRAGMENATION CONDITION   > 15%

PAGE COUNT CONDITION > 1000

USAGE CONDITION 7 DAYS

STEP 5: UPDATE STATISTICS::SELECT ALL DATABASES

FULL SCAN

STEP 6: WRITE REPORT TO A TXT FILE [LOG]  > FINISH > CLOSE. 

STEP 7: OPTIONAL.

RIGHT CLICK MAINTENANCE PLAN > EXECUTE

7. Index Management Example

SYS.DM_DB_INDEX_PHYSICAL_STATS -REBUILD / REORGANIZE

USE MASTER 

GO

-- STEP 1: REPORT FRAGMENTATION FOR ALL INDEXES ACROSS ALL DATABASES?

SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(null, null, null, null, NULL)

-- STEP 2: REPORT HEAVILY FRAGMENTED INDEXES ACROSS ALL DATABASES?

SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(null, null, null, null, NULL)

WHERE avg_fragmentation_in_percent >  80

-- STEP 3: HOW TO FIND FRAGMENTAITON DETAILS FOR A GIVEN DATABASE & OBJECT?

SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(6, 309576141, 1, 1, NULL)

-- STEP 4: IDENTIFY DATABASE NAME

SELECT DB_NAME(6)  -- SSASSBS

-- STEP 5: CONNECT TO ABOVE DATABASE, IDENTIFY THE TABLE NAME

USE SSASSBS

GO

SELECT OBJECT_NAME(309576141)  -- FactInternetSales  

-- STEP 7: IDENTIFY THE INDEX NAME FOR ABOVE TABLE

SELECT * FROM SYS.INDEXES WHERE OBJECT_ID = 309576141  -- PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber

 

-- STEP 8: FOR HIGHER FRAGMENTATION VALUES, WE NEED TO REBUILD THE INDEX

ALTER INDEX PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber

ON FactInternetSales

REBUILD 

-- OR

-- STEP 8: FOR MODERATE FRAGMENTATION VALUES, WE NEED TO REORGANISE THE INDEX

ALTER INDEX PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber

ON FactInternetSales

REORGANIZE 

-- STEP 9: VERIFY THE FRAGMENTATION LEVELS AFTER INDEX REBUILD OR REORGANIZE

SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(6, 309576141, 1, 1, NULL)

8. Page Density

• Each page in the database can contain a variable number of rows. If rows take all space on a page, page density is 100%. If a page is empty, page density is 0%.

• When page density is low, more pages are required to store the same amount of data. This means that more I/O is necessary to read and write this data, and more memory is necessary to cache this data. When memory is limited, fewer pages required by a query are cached, causing even more disk I/O. Consequently, low page density negatively impacts performance.

• To avoid lowering page density unnecessarily, Microsoft does not recommend setting fill factor to values other than 100 or 0, 

Statistics

 

1. Terms & Applies To

• Applies to:  SQL Server 2022 (16.x)  Azure SQL Database  Azure SQL Managed Instance

• https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16 

• 
  

2. Statistics - SYS.STATS Overview

• The Query Optimizer uses statistics to create query plans that improve query performance. For most queries, the Query Optimizer already generates the necessary statistics for a high-quality query plan; in some cases, you need to create additional statistics or modify the query design for best results. 

• Statistics are database objects defined on table / view columns and indexes to control the column address location.

• These statistics are auto created by database engine service in below scenarios:

• 1. Whenever we create indexes 

• 2. We use columns in query conditions

Purpose of statistics: 

• To identify / locate address information of table / view columns easily.

• This means to index the address of table column in the metadata page = master page.

• 
  

How to verify the list of statistics in a database?

• Select * from sys.stats 

• 
  

Advantage of statistics:

• The stats which are auto created as per above scenarios would be auto reused 

• For faster access of column data.  

• We can also update statistics manually. 

• Or

• We can schedule auto update of statistics automatically in Database maintenance plans.

To determine when statistics were last updated, use the sys.dm_db_stats_properties or STATS_DATE functions.

3. When we use Statistics?

1. Query execution times are slow

2. Insert operations occur on ascending or descending key columns

3. After maintenance operations

4. a

4. Statistics options

1. AUTO_CREATE_STATISTICS 

• When the Query Optimizer creates statistics as a result of using the AUTO_CREATE_STATISTICS option, the statistics name starts with _WA.

SELECT OBJECT_NAME(s.object_id) AS object_name,

    COL_NAME(sc.object_id, sc.column_id) AS column_name,

    s.name AS statistics_name

FROM sys.stats AS s

INNER JOIN sys.stats_columns AS sc

    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id

WHERE s.name like '_WA%'

ORDER BY s.name;

2. AUTO_UPDATE_STATISTICS 

• the Query Optimizer determines when statistics might be out-of-date and then updates them when they are used by a query. This action is also known as statistics recompilation. Statistics become out-of-date after modifications from insert, update, delete, or merge operations change the data distribution in the table or indexed view.

3. AUTO_UPDATE_STATISTICS_ASYNC

4. AUTO_DROP

5. INCREMENTAL

SQL Temporal Table

 Terms 

1. Inserts – Updates – Deletes - Merge

1. Overview

• https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16

• Temporal tables (also known as system-versioned temporal tables) are a database feature that brings built-in support for providing information about data stored in the table at any point in time, rather than only the data that is correct at the current moment in time

• Temporal tables are an advanced upgradation to the concept of "cdc : change data capture in older versions"

• A mechanism to create additional history tracking tables based on row updates & deletes on the base table.

• Whenever we perform any update / delete to the base table - Such operations are auto auditted to additional [auto created] "temporal table"

• This temporal table is used for tracking of historical data by dbas

• This temporal table is used for incremental data loads (etl in dwh db design) by bi developers.

• This temporal table is used for faster data recovery incase of accidental updates & deletes.

• Temporal tables are new feature from sql server 2017.

3. Usage 

1. Create Table

CREATE TABLE dbo.Employee (

    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,

    [Name] NVARCHAR(100) NOT NULL,

    [Position] VARCHAR(100) NOT NULL,

    [Department] VARCHAR(100) NOT NULL,

    [Address] NVARCHAR(1024) NOT NULL,

    [AnnualSalary] DECIMAL(10, 2) NOT NULL,

    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START,

    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,

    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)

)

WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

2. Select Statement

SELECT * FROM Employee

    FOR SYSTEM_TIME

        BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'

            WHERE EmployeeID = 1000 ORDER BY ValidFrom;

3. History Table

UPDATE EmployeeHistory  SET EMP_NAME = ' ' -- CANNOT UPDATE HISTORY TABLE

DELETE FROM  EmployeeHistory -- CANNOT DELETE  HISTORY TABLE

DROP TABLE  EmployeeHistory -- CANNOT DROP  HISTORY TABLE