Some queries are very slow after index rebuild

Question

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?

Answer 1

Fragmentation on its own is not necessarily the evil demon it is made out to be. If your database files are stored on a SAN, or some types of RAID array, or perhaps on SSDs, index defragmentation will make very little difference since pages will be spread all over the disks no matter if they are fragmented or not. See this answer for a discussion of that: Is there any benefit to defragmenting SQL indexes in a SAN environment?

When you rebuild indexes, you should not indiscriminately set FILL_FACTOR = 80 - that setting needs to be evaluated on a table-by-table basis. Assuming the tables were at FILL_FACTOR = 100 prior to your rebuild, you have effectively increased the storage required for each table by 20%. That may be a good part of the reason why things are slower now.

Using OPTION (MAXDOP 1) on ALL queries is a bad idea as well. You should allow the query engine to decide whether to execute a parallel query or not. Set the appropriate MAXDOP option at the server level using EXEC sp_configure 'max degree of parallelism', X where X is the maximum number of cores to use for parallel queries. See this question for a discussion on MAXDOP: What is a good, repeatable way to calculate MAXDOP on SQL Server?

Adding a non-clustered index on a field (or fields) will certainly make the query run faster if the database engine decides to use the index.

You may want to ensure the statistics on the affected tables are up to date. See http://technet.microsoft.com/en-us/library/ms187348.aspx for the Books Online description of how to do that. SQLSkills also has an excellent article on statistics, here: http://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/