Total Page Preview:   000000005962

SQL Server - Fragmentation on azure sql database

In this article we will learn fragmentation on azure SQL database or SQL server database.

Index Fragmentation In SQLDatabase
 
 

SELECT TOP 100
        OBJECT_NAME(ps.object_id) AS TableName
       ,i.name AS IndexName
       ,ips.index_type_desc
       ,index_level
       ,ips.avg_fragmentation_in_percent
       ,ips.avg_page_space_used_in_percent
       ,ips.page_count
 
FROM   sys.dm_db_partition_stats ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'DETAILED') ips
ORDER BY ips.avg_fragmentation_in_percent DESC

Result :

Index Fragmentation In SQLDatabase
 
 What is avg_fragmentation_in_percent and avg_page_space_used_in_percent:
 
  • avg_fragmentation_in_percent: avg_fragmentation_in_percent is a percentage value that represents external fragmentation. For a clustered table and leaf level of index pages, this is a Logical fragmentation, while for heap, this is Extent fragmentation. The minimum value is better. If this value is more than 10%, than some corrective action should be taken.

 

  • avg_page_space_used_in_percent: avg_page_space_used_in_percent is an average percentage use of pages that represents to internal fragmentation. Higher the value, the better it is. If this value is lower than 75%, than some corrective action should be taken.
 
Reducing fragmentation(Suggestion):
  • Reducing Fragmentation in Heap: Create a clustered index on the table. when Creating the clustered index, rearrange the records in an order, and then place the pages contiguously on disk.

 

  • Reducing Fragmentation in Index: if avg_fragmentation_in_percent between 5% to 30%, then use ALTER INDEX REORGANIZE: This statement is replacement for DBCC INDEXDEFRAG to reorder the leaf level pages of the index in a logical order. As this is an online operation, the index is available while the statement is running.

 

  • Reducing Fragmentation in Index: if avg_fragmentation_in_percent between 30% to 100% then use ALTER INDEX REBUILD: This is replacement for DBCC DBREINDEX to rebuild the index online or offline. also use the drop and re-create index method.

 

  • Note:  I will strongly  recommend Please don't drop and re-create cluster index.

 

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com



Comments


                           
                           

                           

                           

Recent Posts

How to get the alternate rows or records from table in sql server
07-Jun-2017
Data Warehouse Three Tier Architecture
20-Aug-2017
SQL Server - Fragmentation on azure sql database
07-Oct-2018
Limitations of Azure SQL Database
19-Sep-2019
PIVOT and UNPIVOT in SQL Server with Example
29-Jun-2017
SQL Server - Find all index with key columns on Azure SQL Database
02-Oct-2018
SQL Server - Find unused indexes on azure SQL database scripts
02-Oct-2018
Find Resource usage in Azure SQL Database using script or T-SQL
07-Oct-2018
How to Set client IP Address using the firewall security in Azure SQL Using Portal
29-Jun-2017
What is a Trigger and type of trigger in SQL server?
10-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
What are the different types of Error Handling in SQL Server?
11-Sep-2021
What are the differences between OLTP and OLAP?
07-Sep-2021
How many types of dimension tables available in data warehouse?
16-Sep-2021
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: