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:

        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)

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?

  • 3
    You know what MAXDOP does right? You may want to start with some reading. – Zane Nov 7 '13 at 20:03
  • 2
    I often find myself turning my MAXimum Degree Of Performance up to 11 to really put my performance over the top, you know? – billinkc Nov 7 '13 at 20:07
  • 5
    MAXDOP was covering for the fact that your query is probably going massively parallel due to the fact that your indexing strategy is way off, or your stats are way off. once you rebuilt your indexes, removing MAXDOP allowed the optimizer to get a better estimate and it initiated multiple parallel scans against the index(es) in question. however, we can't really do anything for you without a query plan. – swasheck Nov 7 '13 at 20:08
  • I used MAXDOP because all queries were running VERY slow. This post explains what I experienced: sqlservercentral.com/Forums/Topic1345557-391-1.aspx. I too migrated all my sp's, queries etc.. from a old slow server to a fast track server with 16 cpu's! Suddenly all queries were very slow UNLESS MAXDOP 1 was used. Maybe everything is 'used' to the new server so I can remove it now. – tutu Nov 7 '13 at 20:17
  • @swasheck Yes, I know it's wrong but it just worked. Thanks for the reading material and your comments. Updating statistics on tables involved fixed the speed of the query. – tutu Nov 7 '13 at 20:25

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/

| improve this answer | |
  • 1
    I tried updating the statistics for the three tables involved. Now the problem is fixed, even without the unclustered index it runs fast. Thank you for the info. – tutu Nov 7 '13 at 20:24
  • 1
    You need to start reading up on a database maintenance plans or hire a DBA if you're having these problems. – Zane Nov 7 '13 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.