There are several options available to help reduce the problems you're seeing:
/* set the LOCK_TIMEOUT value to the maximum number of milliseconds
you want to wait for the ALTER INDEX REBUILD to start
*/
SET LOCK_TIMEOUT 100;
/* this tells SQL Server our ALTER INDEX REBUILD statement should
be the deadlock victim
*/
SET DEADLOCK_PRIORITY LOW;
/* Rebuild the index */
ALTER INDEX MyIndex
ON MySchema.MyTable
REBUILD WITH (
ONLINE = ON
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
, MAXDOP = 1
);
I know you have SQL Server 2012, however with SQL Server 2014+, you can use the WAIT_AT_LOW_PRIORITY
option to tell SQL Server to allow other sessions to proceed while the online index build operation is waiting to obtain its schema modification locks.
ALTER INDEX MyIndex
ON MySchema.MyTable
REBUILD WITH (
ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF))
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
, MAXDOP = 1
);
From the MSDN SQL Server Books Online:
In order to execute the DDL statement for an online index rebuild, all active blocking transactions running on a particular table must be completed. When the online index rebuild executes, it blocks all new transactions that are ready to start execution on this table. Although the duration of the lock for online index rebuild is very short, waiting for all open transactions on a given table to complete and blocking the new transactions to start, might significantly affect the throughput, causing a workload slow down or timeout, and significantly limit access to the underlying table. The WAIT_AT_LOW_PRIORITY option allows DBA's to manage the S-lock and Sch-M locks required for online index rebuilds and allows them to select one of 3 options. In all 3 cases, if during the wait time ( (MAX_DURATION = n [minutes]) ) there are no blocking activities, the online index rebuild is executed immediately without waiting and the DDL statement is completed.
Best Answer
For bash: (the standard shell in Ubuntu)
Enter the following command in a terminal to print all the environment variables:
For further information about this command, read the
printenv
man page.To show a list including the "shell variables" you can enter the next command:
This will show you not only the shell variables, but the environment variables too.
For more information related with this topic read:
For zsh: (an advanced shell)
Use the following command:
For more information about ZSH options, see
zshoptions
man page.