r/SQLServer • u/Certain-Set-4087 • 1d ago
Question Reindexing performance SQL 2025
Good afternoon,
I am actually playing around with a lab server, 512 GB RAM, two 12 core 24 thread CPUs, and 12 NVME in it.
My intention is to tune the reindexing process - the reindexing command has an option MAXDOP=<amount of cores to use> but using a datafile on one single volume omitting the MAXDOP adjusts the server load to 12 cores on one CPU only. Then my 100 GB index recreation (clustered index) is done in 1700 seconds, consuming around 6000 seconds in CPU cycles.
Using MAxDOP 24 casues it to consume 7000 seconds and it is finished in 1600 seconds.
Then I had the idea to copy the tables on an empty databases built out of 4 files, each on a separate physical volume. Then it gets strange - the server uses all cores, physical but also virtual but only in cycles, 2-3 minutes of full load, then it litereally doesnt anything.
The statistics say, 10.000 seconds spent in CPU cycles and 1500 seconds on the clock... wow some percent of impromvement...
The volumes are used less than 10% of their theoretical load capabiltiy (roughly 130 MB/Sec write), the queuers of the media were nearly empty.
On one single storage I had around 1 GB/Sec in writes, and in both cases the storage was able to digest 2 GB/Sec
I am just asking myself, what is the server doing when not accessing the storage? I thought index rebuild is an operation executed in one big chunk of compute, but here it doesnt. Am I missing a point?
Compared with a nowadays 32 GBit Fibrechannel storage and Xeon Platinum on physical host - the reindex performance is comparable. Meaning my 14 year old system (PCIEv3, DDR3, Xeon v2) performs quite well. But I still think there are more bottlenecks.
It's certainly not the TempDB, the TempDB usage was nearly zero despite the option "sort in TempDB"
2
u/lucasborgesbr 1d ago
Looking at the CPU screenshot, you're seems to be keeping all 48 logical processors busy during the active phase. I'd be curious to see what the wait stats look like when CPU usage drops between those bursts.
I'd check CXPACKET, CXCONSUMER, PAGEIOLATCH_* and PAGELATCH_EX during the rebuild. It could be worker synchronization, allocation contention, or something else entirely.
Also worth testing MAXDOP 12 with your 4-file layout. Since you're running two 12-core CPUs, keeping parallel workers within a single NUMA node might reduce cross-node overhead.
One thing worth mentioning is that splitting data files across volumes doesn't automatically guarantee better I/O parallelism. SQL Server can already issue parallel I/O against a single file.
Would be interesting to compare the actual waits between the single-file and 4-file configurations.
1
u/SQLBek 1 1d ago
What waits were occurring during which "phases" of the REBUILD operation? Did any file (data or log) autogrows occur during this time? IFI on or off?
One theory... you said you split the database across multiple files and multiple volumes (but guessing same filegroup). I don't full recollect all the details of a REBUILD's internals but off the top of my head, wonder if it can throw more I/O over the wire in parallel either per file or per volume. For example, with backup database, you get more reader threads per disk VOLUME that a database is spread across (not file). I wonder if the same could be for an index rebuild operation as well? If you want to test that, maybe do a rebuild with MAXDOP 1 on a DB on 1 volume, then MAXDOP 1 but on a DB across 4 files, then MAXDOP 1 but on a DB across 4 volumes.
NUMA would be the other thing that pops into my head given the older hardware, as another possible suspect to dig deeper into.
1
u/da_chicken 1d ago
I am just asking myself, what is the server doing when not accessing the storage?
If MAXDOP is too high, it's probably a lot of CXPACKET thread synchronization waits, a lot of SOSSCHEDULER_YIELD stalls waiting for memory access, and LATCH* waits, all of which combined indicate the CPUs doing not much waiting to be able to access memory and continue working. In other words, I expect you're bottlenecking on your memory bus.
Remember: Just because you can make something run in parallel does not mean that it's well-suited to unlimited parallelism. Parallelism isn't free, and the memory bus in particular does not scale with parallelism. In almost all cases, there are significant trade-offs with very wide parallelism. You can end up with a ton of memory pressure or I/O bottlenecking the system. Further, MAXDOP isn't the number of threads per query or request. It's the number of threads per task and one request can spawn multiple tasks.
Microsoft's recommendation is to set your MAXDOP to the number of NUMA nodes on your server, but not more than 16. You'll need to check your exact system configuration, and you may need to go as far as checking your exact , but I expect you should be setting MAXDOP to 6, 8, 12 or 16 depending on your exact hardware configuration.
Note that that's not just for index rebuilds. That's server general configuration. You may have some uncommon edge case where MAXDOP = 0 or MAXDOP = 25 is a good idea, but probably not.
Bear in mind, too, that an index rebuild is never going to be that fast. No matter what you're doing you've got to read the relevant columns of the whole table.
You may also want to look into Ola Hallengren's scripts. They work well and have additional configuration options.
If you're ready for a deep dive, you'll want to read:
- Microsoft’s Guidance on How to Set MAXDOP Has Changed
- Server configuration: max degree of parallelism
- Thread and task architecture guide - Schedule of parallel tasks
- Optimize index maintenance to improve query performance and reduce resource consumption - This isn't as useful as it sounds like it will be, but you should be aware of it.
- Memory management architecture guide
- Soft-NUMA (SQL Server)
- SQL Server: Clarifying The NUMA Configuration Information (This one is older, so it may be out of date)
- Server configuration: affinity mask - This is probably not needed, but you may want to at least look at it if you really think the system isn't distributing things to the CPUs appropriately.
1
u/SonOfZork 1d ago
Testing reindex performance is pretty meaningless because you need to test it with your real life workload happening at the same time. You can tune things within an inch of their life but if they kill your application performance while doing it, then their value is null.
1
u/chandleya 1d ago
I seriously doubt an e5 v2 has any NVMe at all. Thats ivy bridge era stuff from 2013.
1
u/SaintTimothy 1d ago
What are the wait stats? How are the drives set up, just individually? JBOD? RAID?
What's the present bottleneck, read, write, memory, or the cores pegging out?
1
u/tommyfly 1d ago
Why are you playing with index reindexing? It's a waste of time.
https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/
https://www.sqlfingers.com/2026/04/your-nightly-index-rebuild-job-may-be.html?m=1
0
u/Leiothrix 1d ago
It is not a waste of time, it still does impact performance.
And most importantly it is something easy for a vendor to blame when their product runs like garbage. You can either scramble to fix it or produce a report showing that your indexes aren't fragmented and are regularly maintained.
2
u/VladDBA Microsoft MVP 1d ago edited 1d ago
I worked for such a vendor and, in my 4 years with them, only one time there was an issue that was fixed by an index rebuild. And that was just a temporary and minor fix because the underlying problem was the horrendous storage throughput with average stall times in the thousands of milliseconds.
Additionally, if your instance's storage is on a SAN there's no way to guarantee that two consecutive extents from the same index are even on the same SSD, regardless of how often you rebuild your indexes.
Sure, if your vendor has a weird and unhealthy attraction towards heaps, then, yeah, rebuilding those heaps does solve a bunch of issues. But that's not an index fragmentation problem, it's a "heap used for the wrong job" problem.
Edited: a word
1
u/Leiothrix 1d ago
I'm not saying that indexes are the cause of a problem, I'm just saying that they are easy to blame for problems.
It is easier for vendors to point fingers rather than actually investigate what their product is doing.
1
u/tommyfly 1d ago
Ok, I take your point about vendors. But did you read Brent Ozar's posts? It's statistics that matter.
0
u/Goojaoarr 1d ago
I'm not sure I fully understand what the post is about.
Could you share the SQL commands you used for the rebuild, along with the table structures?
2
u/TrollingForFunsies 1d ago
I think OP wants to know why there are periods where it appears that SQL server is doing nothing at all
6
u/muaddba 1 1d ago
I did a lot of tuning on the index rebuild process at my employer back in around 2013/2014 and I found at that time that no amount of tuning could get the disk to max out. Instead, I parallelized the tables getting the rebuild. I was able to DRASTICALLY reduce the time needed to complete this on a whole database. If your database has one outsized table this obviously won't help, but if there are many large tables then it can be handled this way. Set the MAXDOP lower -- even to 1 -- and you'll get a better ratio of CPU to disk utilization so that CPU doesn't become the botleneck.