Recently my SQL Server query got slower and slower performance with big dataset in database table

Monday, November 12, 2018 / mcchu28


Possibly save 4 hours of your time: Recently, I realize every time I ran a table related query, the performance gets slower and slower. This table has close to 6GB data.

Symptoms:
I have to load these data from a source sometimes. Each time I will have to clear previous data. This causes a fragmentation issue with the index. You can run the following query to determine how fragment is each index. You need to replace with your db name and table name.

-- Find the average fragmentation percentage of all indexes in the table.
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'YOUR_DB_NAME'),
OBJECT_ID(N'dbo.YOUR_TABLE_NAME'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO

In my case I need to reorganize the index to defragment the sql server database index because the fragmentation was over 99%.

Solution:
Under the table name in SQL server management studio, you will find the indexes folder. You can right click on an index and choose Rebuild. After this, the fragmentation is less than 1% and my query runs super fast again.


You can also rebuild index using a script:

ALTER INDEX ALL ON YourSchema.YourTableName
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

Resource: