After finding out almost all databases on my SQL Server had fragmentation over 40%, I decided to do an index rebuild on all tables using a fill factor of 80.
After rebuilding all indexes, some queries seem to take forever for at least two queries/tables.
Here's one of those slow queries:
SELECT a.FileID, a.EventID, MAX(b.cyNumber) AS cyNumber, MAX(b.skNumber) AS skNumber, MAX(b.cyFormat) AS cyFormat, MAX(b.Cost) AS Cost, MAX(b.PackageRef) AS PackageRef, MAX(CASE WHEN b.BMUpdatedON = '1900-01-01 00:00:00.000' THEN NULL ELSE b.BMUpdatedON END) AS BMUpdatedON, MAX(b.RunID) AS RunID FROM DB.dbo.[File] a INNER JOIN DB.dbo.bicy b ON a.InnerFileID = b.InnerFileID WHERE a.FileID NOT IN (SELECT FileID FROM DB.dbo.Event_bicy) GROUP BY a.FileID, a.EventID OPTION (MAXDOP 1);
I always used
OPTION (MAXDOP 1) on all queries because when I didn't use it the query would run VERY slowly. Now, after the index rebuild, the opposite seem to happen. If I remove
MAXDOP 1 or if I remove the
where clause the query runs fast, which I find strange.
Solution: when I create a non-clustered index on
DB.dbo.bicy.[InnerFileID] the query runs fast.
Question: why do I need to create a non-clustered index for the query to run fast when before the index rebuild the query ran just fine?