November 7, 2014 at 2:49 pm
This is very puzzling:
- Same server (SQL 2014 Standard Edition, 64GB RAM, 32 cores, SSD storage attached via iSCSI).
- Same database (upwards of 300GB).
- Same table (very large, many millions of rows, all columns are either INT or DATETIME).
- Same index (non-clustered, 2 INT columns).
- Same fragmentation (about 23-25%, give or take).
- Same concurrent workload - that is to say, none. During index optimization, nothing accesses the server other than Red Gate SQL Monitor.
The script for the index itself:
ALTER TABLE [dbo].[MyHugeTable] ADD CONSTRAINT [MyHugeTable_pk] PRIMARY KEY NONCLUSTERED
(
[IntColumn1] ASC,
[IntColumn2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
ALTER INDEX REORGANIZE takes 3 hours and change. I tried different options, but it always takes just about that long. Not compacting large objects, just straight reorg.
ALTER INDEX REBUILD takes 1.5 hours. Again, tried different options and no significant change in performance.
Why is this happening? I feel like an idiot asking this question, because "REORGANIZE is faster than REBUILD" is something that you learn in MSSQL DBA 101. I feel like I'm looking at some sort of odd outlier case, but it shouldn't be - the index looks very straightforward, and neither it nor the table it is defined on has any unusual data types.
Any ideas?
November 7, 2014 at 4:54 pm
Interesting :unsure:
Are your drives separated by
LOG
DATA
If so, could it be possible the LOG drive is slower than DATA drive?
I know that for rebuild and reorg there is log growth, but maybe there is a lot more log activity causing slow downs?
Are you able setup perfmon to monitor 'log' related sql counters and disk counters on your server to compare 'REBUILD' vs 'REORG'?
This might give some clues.
(I dont know for sure but it's my guess maybe there's more log activity with a reorg ... I have never 'tested' the perf counters to compare rebuild/reorg .. maybe is should!)
http://msdn.microsoft.com/en-us/library/ms179542.aspx
Disk space is an important consideration when you create, rebuild, or drop indexes. Inadequate disk space can degrade performance or even cause the index operation to fail. This topic provides general information that can help you determine the amount of disk space required for index data definition language (DDL) operations.
ALTER INDEX REORGANIZE; however, log space is required.
...
...
All other index DDL operations require additional temporary disk space to use during the operation, and permanent disk space to store the new index structure or structures.
When a new index structure is created, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
November 7, 2014 at 8:40 pm
- DATA and LOG are separated, but only logically. They are on two drives allocated on the same large SSD volume attached via iSCSI.
- When REBUILD is run, I see WRITELOG waits appear periodically, but they are very brief.
- TempDB has about 250GB allocated to it and has another 250GB to grow, but I haven't seen it grow during REBUILD or REORGANIZE.
- There is also ample free space inside the database on which I am running this, and in either case I did not observe much growth of the log files.
Outside of this, both the server and this individual DB are performing adequately, so I don't think this is a global performance issue, though I'm open to checking anything I could have missed.
November 7, 2014 at 9:07 pm
REORGANIZE is done within the confines of what is currently in the indexes insofar as pages goes. That means that it sometime has to contend with blocking because it's sharing the same pages that users might be using.
REBUILD creates a separate index if it's over 128 Extents (8MB) which means there's very little contention, which can sometimes make it faster the REORGANIZE. Both are fully logged in the FULL RECOVERY mode. REORGANIZE is always fully logged.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply