30

It is simply a remnant of olden times, when it was used in contrast to batch processing. "Online" here means "interactive", that is, requests to the database are processed as they come and responses are given more or less immediately, or at least as soon as they are available. Batch processing would collect requests into, well, batches, and execute them on ...


14

Yes, there are cases when you may specify COPY, but it would be for other reasons than performance. It is important to understand that MySQL introduced new feature - Online DLL processing in version 5.6. It did not remove offline processing. So there is a need to differentiate between these 2 modes: Some operations still work in Offline mode only. See ...


10

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 ...


9

Good news: 150,000 forwarded records isn't actually that bad, depending on what kind of time span we're talking about. Forwarded records are tracked as long as the server is up (with some gotchas around a bug in specific 2012/2014 builds.) Even when it does work online, your users can notice it depending on your IO throughput, size of the table, number of ...


8

Here is how I would do it. First, create a couple of spare schemas: CREATE SCHEMA HoldingTank AUTHORIZATION dbo; CREATE SCHEMA Swapper AUTHORIZATION dbo; Now, when it's time to refresh the view and change the filter predicate, create it in the HoldingTank schema: CREATE VIEW HoldingTank.MyIndexedView WITH SCHEMABINDING AS SELECT ... GO CREATE UNIQUE ...


6

No, you don't need to take your database offline, or even take some object offline (how?) manually before altering a table or creating an index. On the Standard version, SQL Server will lock your object (i.e. table) until the create index or alter table is complete. If you have the Enterprise Edition, then you could specify the WITH (ONLINE=ON) option to ...


4

The docs say this about NOT VALID ADD table_constraint [ NOT VALID ] This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the ...


4

The best thing I have found to help in big index creation is to (1) have enough RAM, (2) have a slow time before the index creation starts, and (3) perform a SELECT...INTO of the index fields into a temp table with an ORDER BY of the desired index order right before creating the index. This can speed the process by up to 75% in some cases (which I have not ...


3

The only idea I can think of is if I maintained a copy of the database on a separate server where I can make DDL changes, then re-point my applications to that server. Instead, consider just building a new table, and loading it incrementally from your existing table (things like Change Tracking or even Triggers can help here). Then during your short ...


3

The old index stays in place, is maintained and being used by queries. Only at the end of the index build does SQL Server alter the metadata in such a way that the old index disappears and the new one is set in. There is not a single millisecond without an index. (But there is a short-lived Sch-M lock twice during the online build.)


3

This is by no means a full answer but may move things along a bit if you were to try something similar and report your results. I couldn't reproduce them. With the following test table CREATE TABLE [dbo].[Table] ( Col BIGINT ) CREATE NONCLUSTERED INDEX IX ON [dbo].[Table](Col) INSERT INTO [dbo].[Table] SELECT top 12000 ROW_NUMBER() OVER (ORDER BY @@SPID) ...


2

@Stoleg probably has the best answer, but here is another one. It's an educated guess that the developers left =COPY in as an escape hatch in case there was a serious bug in =INLINE. This would let users continue to use ALTER even if the new feature is broken. I have seen things like this (in flags, sql_mode, my.cnf settings, etc) over the years. The ...


2

This is working as intended. The indexes are objects written to the disk. The index creation is also written to the online logs. Creating indexes is expected to take up disk space and cause I/O operations.


1

Generally there are three different ways SQL Server can proceed when it comes to table alteration. 1. Metadata only changes examples - dropping a column, changing a not nullable column to nullable one or adding a nullable column to the table. 2. Alteration requires changing the metadata only, but SQL Server needs to scan the table to make sure it confirms ...


1

For 5.6: pt-online-schema-change is probably your best bet. It creates a new table with the change, then gradually copies rows over. Meanwhile, it uses a TRIGGER to keep the new copy updated. For 8.0: ADD COLUMN now has ALGORITHM=INSTANT. Would you like to show us SHOW CREATE TABLE; there may be some improvements that would decrease the problem.


1

That happens when InnoDB compressed tables with large amount of rows are being updated (no matter if the algorithm is in place). Large operations for ALTER tables, will use the "online log", which is expected to consume space. You can check this using SHOW ENGINE INNODB STATUS in the History Length line. This happens always that you have large operations ...


Only top voted, non community-wiki answers of a minimum length are eligible