No Lies
When you create an index, there are a lot of options. Recently while working with nice people who pay me, I’ve gotten the same question a few times.
I like to hand off index change scripts to people to help them drop unused indexes, merge duplicative indexes, and add in helpful missing indexes. When I do, I always specify some options along with them to help the create process along, like MAXDOP, ONLINE, and SORT_IN_TEMPDB.
The thing is, those settings aren’t ones that kick in automatically next time you rebuild the index or something; you have to specify them each time.
Here are index creation options:
- SORT_IN_TEMPDB
- DROP_EXISTING
- ONLINE
- RESUMABLE
- MAX_DURATION
- MAXDOP
Here are stored index options:
- PAD_INDEX
- FILLFACTOR
- IGNORE_DUP_KEY
- STATISTICS_NORECOMPUTE
- STATISTICS_INCREMENTAL
- ALLOW_ROW_LOCKS
- ALLOW_PAGE_LOCKS
- OPTIMIZE_FOR_SEQUENTIAL_KEY
- DATA_COMPRESSION
Roll Out
Where you use these options is situational.
For example, only Enterprise Edition can create indexes online, or using a parallel plan. You can sort in tempdb for any of them, though. I might use different DOPs depending on the size of the server, and of course if I’m creating a column store index (for those, DOP 1 is sometimes a good idea).
For the stored options, I leave most of them alone. I always start Fill Factor off at 100, and with page compression turned on. Those are both things you can adjust or remove later if they turn out to not be ideal, but I love testing them out.
Data compression is especially useful on Standard Edition servers with a limited buffer pool (128GB) and large data sets, but can be just as useful on Enterprise Edition when that antichrist VM admin refuses to part with a single more GB of memory.
And hey, maybe in the future as more workloads move to 2019+, I’ll get to spend more time optimizing for sequential keys.
Maybe.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.