MSSQL Index Fragmentation

Identificarea indecsilor cu fragmentare mai mare de 80% (TOP 10 – primele 10 inregistrari):

SELECT TOP 10 OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName, indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
WHERE indexstats.avg_fragmentation_in_percent > 80 AND i.index_id = indexstats.index_id;

Rebuild indexes:

ALTER INDEX "nume_index" ON nume_tabela REBUILD WITH (ONLINE=ON);

Reorganize/defragment indexes (ALL = all indexes associated with the table):

ALTER INDEX ALL ON nume_tabela REORGANIZE;

Alte informatii:
https://msdn.microsoft.com/en-us/library/ms189858.aspx
https://support.microsoft.com/en-us/kb/2755960

Top 10 Best Practices for SQL Server Maintenance for SAP:
https://technet.microsoft.com/en-us/library/cc966447.aspx

ALTER INDEX:
https://msdn.microsoft.com/en-us/library/ms188388.aspx

DBCC:
https://msdn.microsoft.com/en-us/library/ms188796.aspx

Nivelul de incarcare al fisierelor LOG:
DBCC SQLPERF (LOGSPACE);

Manage the Size of the Transaction Log File:
https://msdn.microsoft.com/en-us/library/ms365418.aspx

Troubleshoot a Full Transaction Log (SQL Server Error 9002):
https://msdn.microsoft.com/en-us/library/ms175495.aspx

Back Up a Transaction Log (SQL Server):
https://msdn.microsoft.com/en-us/library/ms179478.aspx

How to shrink the size of a transaction log (.LDF) in SQL:
https://support.software.dell.com/kb/63465