BLOG

For Turkish version, please click here.

When a table gets insert/update/delete, indexes are constantly fragmented. If the fragmentation pct. is high, indexes can reduce query performance. One of the important part of database maintenance is detecting fragmentation.

We can list the fragmentations by using Dynamic management views and functions on SQL Server. If the percentage is above 30%, rebuild the index (alter index indexName on tableName rebuild), if the percentage is between 10 and 30, reorganize the index (alter index indexName on tableName reorganize).

Don’t forget to rebuild the index with ONLINE option which makes the related table accessible while index maintenance process is being held on it ( …rebuild WITH (ONLINE = ON) ).

SELECT  
OBJECT_NAME(i.OBJECT_ID) AS [Table Name], 
i.name AS [Index Name], 
ips.index_type_desc AS [Index Type],
ips.page_count AS [Page Count],
ips.avg_fragmentation_in_percent AS [Fragmentation Pct]

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ips
INNER JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id

where — you can comment out all 3 filters
i.is_disabled = 0 AND –only active indexes 
ips.index_type_desc != ‘HEAP’ AND –only tables which have clustered index
ips.page_count > 40 –don’t include indexes which have small page count

ORDER BY [Fragmentation Pct] DESC

Yazının İngilizcesi için lütfen tıklayınız.

Bir tablo insert/update/delete aldığında, o tablodaki index’ler sürekli bozulur. Eğer bozulma yüzdesi yüksekse, index’ler sorgu performansını düşürmeye başlar. Veri tabanı bakımının önemli adımlarından bir tanesi, index’lere rebuild/reorganize işlemlerini uygulamadan önce index’lerin bozulma oranlarını tespit etmektir.

Microsoft SQL Server üzerinde yer alan dynamic system view ve function’lar kullanılarak index’lerin bozulma oranlarını listelemek mümkündür. Bu sorguyu aşağıda bulabilirsiniz.

Bir index için bozulma oranı %30’un üzerinde ise rebuild (alter index indexName on tableName rebuild); bozulma oranı %10 ile %30 arasında ise reorganize edilir (alter index indexName on tableName reorganize).

Bir index’in rebuild işlemi devam ederken üzerinde bulunduğu tablonun diğer sorgulara cevap vermeye devam etmesini isterseniz, ONLINE opsiyonunu kullanmayı unutmayın (…rebuild WITH (ONLINE = ON) )

SELECT  
OBJECT_NAME(i.OBJECT_ID) AS [Tablo Adı], 
i.name AS [Index Adı], 
ips.index_type_desc AS [Index Tipi],
ips.page_count AS [Indexin Sayfa Sayısı],
ips.avg_fragmentation_in_percent AS [Bozulma Yüzdesi]

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ips
INNER JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id

where 
i.is_disabled = 0 AND –sadece aktif indexler
ips.index_type_desc != ‘HEAP’ AND –sadece clustered index’li tablolar
ips.page_count > 40 –küçük sayfa sayılı index’ler dahil edilmeyebilir

ORDER BY [Bozulma Yüzdesi] DES

For Turkish version, please click here.

It is important that maintain your indexes on tables and views in Microsoft SQL Server to get maximum performance from your database.

We recommend using Ola Hallengren’s SQL Server Maintenance Solution for rebuilding and reorganizing indexes and updating statistics by setting up an SQL Server Agent Job to excute this script periodically. It is supported on SQL Server 2008 and later versions along with Azure SQL Database and Azure SQL Database Managed Instance.

You can reach the script here : https://lnkd.in/djVAJS9

Yazının İngilizcesi için lütfen tıklayınız.

Veri tabanınızdan maksimum performans almaya devam etmek için tablo ve view’lerinizde yer alan index’lerin bakımlarını yapmamız önemlidir.

Bu işe yönelik bir SQL Server Agent Job kurarak düzenli olarak çalıştırılabilecek, index’leri reorganize ve rebuild yapıp istatistikleri güncellemesi için Ola Hallengren’in hazırladığı bakım çözümünü kullanmanızı öneririz. Bu çözüm SQL Server 2008 ve sonrasındaki bütün versiyonlarla birlikte Azure SQL ve Azure SQL Managed Instance’ı da desteklemektedir.

Index bakım script’ine şuradan erişebilirsiniz :
https://lnkd.in/djVAJS9

For Turkish version, please click here.

We sometimes need to disable an index; either before a bulk and large insert/update or we won’t use that index anymore according to the system statistics. To disable an index on a table, we use DISABLE keyword.

——————————————————————————
USE EpicZoneDatabase
GO

ALTER INDEX [IX_Production_CategoryId] ON [dbo.Production] DISABLE
GO
——————————————————————————

To enable the same index, I’ve seen a developer last week using the ENABLE keyword, which results in error.

Because an index cannot be only enabled after it is disabled; we must rebuild it. The following syntax will enable and rebuild the index.

——————————————————————————
USE EpicZoneDatabase
GO

ALTER INDEX [IX_Production_CategoryId] ON [dbo.Production] REBUILD WITH (ONLINE = ON);
GO
——————————————————————————

As an important side note, we better use ONLINE keyword while rebuilding an index, if it is an active hour for your system. ONLINE option allows other users can continue to update and query the data. If we perform rebuild offline, it holds exclusive locks on the table data and related indexes. This prevents modifications and queries to the underlying data until the index operation is complete; so you may encounter deadlocks on your SQL Server

Yazının İngilizcesi için lütfen tıklayınız.

Bazen büyük bir insert ya da update sorgusu çalıştırmadan önce ya da sistem istatistiklerine bakarak artık ihtiyaç olmadığına karar verdiğimizde bir index’i pasife çekmek isteriz. Bir index’i pasife çekmek için DISABLE anahtar kelimesi kullanırız.

——————————————————————————
USE EpicZoneDatabase
GO

ALTER INDEX [IX_Production_CategoryId] ON [dbo.Production] DISABLE
GO
——————————————————————————

Geçen hafta yaptığımız bir danışmanlık çalışması sırasında bir geliştirici, aynı index’i aktifleştirmek için ENABLE anahtar kelimesinin neden hata verdiğini ve bunun yerine ne kullanmak gerektiğini sordu.

Çünkü Microsoft SQL Server üzerinde bir index tek başına aktifleştirilemez; o index’i rebuild etmemiz gerekir. Aşağıda sorgu, ilgili index’i rebuild ederek yeniden aktifleştirecektir.

——————————————————————————
USE EpicZoneDatabase
GO

ALTER INDEX [IX_Production_CategoryId] ON [dbo.Production] 
REBUILD WITH (ONLINE = ON);
GO
——————————————————————————

Önemli bir ek not olarak eklemek isteriz ki, eğer sisteminiz için aktif saatlerde bu işlemi yapıyorsanız, index’i rebuild ederken ONLINE opsiyonunu eklemek iyi olacaktır. ONLINE opsiyonu, diğer kullanıcıların veriyi güncellemesine ve erişmesine izin verir. Eğer rebuild işlemini (daha az disk yeri kaplayarak ve işlemin daha hızlı tamamlanması için) offline yapmayı tercih ederseniz, tablodaki veriye ve ilgili index’e özel kilit koyulur. Bu durum, index işlemi tamamlanana kadar ilgili veride güncelleme yapılmasını (update) ve verinin elde edilmesinin (select) önüne geçer; nihayetinde de SQL Server üzerinde deadlock’lar ile karşılaşabiliriz.