在本文中,我们解决了快速的内幕on Disk space requirements which are essential for performing Index DDL actions.
Emphasizing on the disk space requirements is one of the most crucial things to do when creating, dropping or rebuilding indexes in SQL Server. Insufficient disk space will not only lead to poor performance but might also lead to the index operation failing completely. To make sure that your task of creating indexes happens smoothly you should keep in mind the amount of disk space available to you and what are the requirements. In this article, we will be focusing on using Disk space requirements while performing Index DDL actions.
Index Operations That does not Require any Additional Disk Space
These are the following index operations which do not require additional disk space:
• ALTER INDEX REORGANIZE, But the user needs to use log space.
• DROP INDEX only when the user drops a non-clustered index.
• DROP INDEX when the user drops a clustered index with offline setting without specifying a MOVE TO clause and there are no non-clustered indexes in the file.
• CREATE TABLE (using UNIQUE or PRIMARY KEY constraints)
Additional Disk Space is required by the following Index Operations
Note that Index operation needs disk space while operating, and also permanent disk space for storing new index structures. In SQL, when a user creates an index structure, disk space of the source (old) and the target (new) structure is required in order to access their appropriate files and folders. Also note that the SQL doesn’t relocate the old structured until the new index creation transaction is committed properly.
These are the index DDL operations which are required for creating new index structures in an additional disk space:
• CREATE INDEX
• CREATE INDEX WITH the DROP_EXISTING
• ALTER INDEX REBUILD
• ALTER TABLE & ADD CONSTRAINT (UNIQUE or PRIMARY KEY)
• ALTER TABLE & DROP CONSTRAINT (UNIQUE or PRIMARY KEY) whenever the constraint of the DDL index is based on or is a clustered index.
• DROP INDEX & MOVE TO (Note that this operation only Applies on clustered indexes.)
Sorting Temporary Disk Space
Apart from the disk space which is required for the creation and storage of target and source structures, SQL also requires disk space for sorting, unless query optimizer can offer a better plan of execution which doesn’t require sorting.
In case the SORT_IN_TEMPDB option is turned ON, then largest index should fit into tempdb. This option does increase the utilization of temporary disk space which is being used for creating an index; it might help in reducing the time required for creating an index provided that tempdb is applied on a set of disks that are different from the selected user’s database.
If SORT_IN_TEMPDB is turned OFF (by default) in each index, even including the partitioned indexes, are sorted in their destination disk space; so, they only require disk space for new index structures only.
Temporary Disk Space used by Online Index Operations
In order to perform index operation online, users require temporary disk space. If a user creates, rebuilt or drops a clustered index online, it automatically creates a temporary non-clustered index on the map of the old and the new bookmarks.
以防SORT_IN_TEMPDB选项打开,它creates this temporary index in tempdb. If SORT_IN_TEMPDB is turned OFF, then the similar partition scheme is used as the target index.
At times if you encounter a data error while working on MS SQL Server, immediately take help from a specializedSQL Server recoverytool. Such a tool can go a long way in protecting your data and avoiding any data loss.
Author Introduction:
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, includingaccess recoveryand sql recovery software products. For more information visitwww.circareview.com
Leave a Reply