Hello friends,
I want to share my experience with MS SQL maintenance and performance with regards to nonclustered Indexes and Fragmentation.
If you do delete unnecessary rows in tables from time to time then you definitely need to maintenance fragmented indexes.
When you delete a row in a table SQL Server modifies indexes and it leads to fragmentation that leads to slowing down query performances on that table.
To avoid any bottlenecks and be sure your fragmentation is minimal and performance is maximum you need to reorganize indexes or rebuild them.
I use the following piece of stored procedure code whenever I do any clean on a table:
StoredProcedure
....
-- Reorganize the NCIX_MyTable
-- index on the dbo.MyTable table.
SELECT @frag = avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'my_database'),
OBJECT_ID(N'dbo.MyTable'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id and [name] = N'NCIX_MyTable';
if @frag>=50
begin
--To rebuild a fragmented index
ALTER INDEX NCIX_MyTable
ON dbo.MyTable
REBUILD WITH (FILLFACTOR = 50);
end;
if @frag>=25
begin
ALTER INDEX NCIX_MyTable
ON dbo.MyTable
REORGANIZE;
end;
....
Conclusion - it keeps your DB size to minimum, removes unnecessary data and keeps sql performance to maximum.
Thank you and see you ;)
1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y