• Aranacak içerik:
29Nis
SQL Server Tüm İndexler Rebuild

Veritabanında bulunan indexleri aşağıdaki script ile kolayca rebuild yapabilirsiniz

USE [VeritabanıAdı];
GO

DECLARE @TableName NVARCHAR(255);
DECLARE table_cursor CURSOR FOR 
SELECT name FROM sys.tables;

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @TableName;

WHILE @@FETCH_STATUS = 0  
BEGIN  
       EXEC ('ALTER INDEX ALL ON ' + @TableName + ' REBUILD;');
       FETCH NEXT FROM table_cursor INTO @TableName; 
END;

CLOSE table_cursor;  
DEALLOCATE table_cursor; 
GO
27Ara
MSSQL Rebuild Index , Sql Server Index Yenileme

MsSql deki index lerimizi zaman zaman rebuild yapıp indexleri yenilememiz gerekir bunuda aşağıdaki script ile gerçekleştirebilir siniz

USE [Database]
GO
ALTER INDEX [Index_Adı] ON [dbo].[Tablo Adı]
REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 10)
GO
28Ağu
Missing Index SQL

Otomatik Index Üreten SQL Scripti

Çalışan database de script çalıştırılınca işe yarar boş database de yada aktif olarak çalışmayan database de boş döner

SELECT    
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
Telif hakkı © 2024 Ahmet AYDINLIK | Yazılım Geliştirici