I read through Microsoft's "Editions and supported features of SQL Server 2017" doc to compare the feature differences between Enterprise and Standard edition.
Two things not available in Standard that caught my eye were:
-
Online indexing
-
Online schema change
Does this literally mean you can't create and modify indexes or tables (and other objects?) without taking the database offline first, or does Microsoft mean there are only specific operations you can't do and if so where can I learn more about which operations require taking the database offline?
Best Answer
The confusion seems to be with the verbiage - online/offline is sort of confusing in this context.
It refers to whether the operations being performed (a reindex or a table alter) are performed online or not, and not whether the state of the object is online or offline. Online means that the object does not have an exclusive lock on it, and is possibly accessible to other queries/operations as a result. Offline means it is locked by the operation and can't be touched by other queries no matter what.
The benefit for Enterprise is that I can do my schema changes or index maintenance and not (potentially) adversely impact other queries running. That doesn't mean that other, less restrictive, locks won't be taken (row level, page level), but it does mean that I'm not totally boxed out of the object. It does not guarantee things will keep running the same, though, as many other factors play into this. Check out the individual commands you're interested in running to see the myriad options that may effect locking behaviors.
For example, I've seen an online index reorg end up effectively locking the table from all other
SELECTstatements until I tuned theMAXDOPit was using, even though it wasn't actually taking a lock on the whole table.An object can't be taken online or offline, although a database can (not relevant to this topic though).
Resources: