When you rebuild indexes, you should not indiscriminately set FILL_FACTOR = 80 - that setting needs to be evaluated on a table-by-table basis. See this answer for a discussion of that: Is there any benefit to defragmenting SQL indexes in a SAN environment? 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. 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?įragmentation on its own is not necessarily the evil demon it is made out to be. Solution: when I create a non-clustered index on DB.dbo.bicy. If I remove MAXDOP 1 or if I remove the where clause the query runs fast, which I find strange. Now, after the index rebuild, the opposite seem to happen. I always used OPTION (MAXDOP 1) on all queries because when I didn't use it the query would run VERY slowly. WHERE a.FileID NOT IN (SELECT FileID FROM DB.dbo.Event_bicy) INNER JOIN DB.dbo.bicy b ON a.InnerFileID = b.InnerFileID MAX(CASE WHEN b.BMUpdatedON = ' 00:00:00.000' THEN NULL ELSE b.BMUpdatedON END) AS BMUpdatedON, ![]() ![]() Here's one of those slow queries: SELECT a.FileID, 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.Īfter rebuilding all indexes, some queries seem to take forever for at least two queries/tables.
0 Comments
Leave a Reply. |