There is possibility of fragmentation issue with the index.
Check possibility of index fragmentation -
Here is a simple query to check fragmentation on your existing indexes:
DECLARE @DBNAME VARCHAR(130);
SET @DBNAME = 'MYDBNAME';
DECLARE @DBID INT;
SET @DBID = DB_ID(@DBNAME);
OBJECT_ID AS objectID
, index_id AS indexID
, avg_fragmentation_in_percent AS fragmentation
(@DBID, NULL, NULL , NULL, N'Limited')
index_id > 0
OPTION (MaxDop 1);
i.[name] as indexname,
INNER JOIN sys.indexes i
ON d.objectid = i.object_id
DROP TABLE #indexDefragList
This will return a list of all indexes in your current DB with their fragmentation in %.
Rebuilding of indexes will solve the problem.
Rebuilding of indexes is not supported automatically in any version of Microsoft SQL Server - the reason being is that rebuilding indexes can potentially be very expensive operations, and so need careful scheduling and planning.
In many environments special scripts will be written to handle this, for example:
Note that whilst SQL can automatically update statistics for you in many cases there is a performance gain to be had by managing these more carefully as well.
You can easily build a script to automatically rebuild or reorganise them. There is a great article from SQLFool on this including a complete pre-made script.