Databases Reference
In-Depth Information
F
To reorganize one index of a table using
DBCC
INDEXDEFRAG
, use the
following query:
--reorganizing idx_refno index of ordDemo table
--in AdventureWorks2012 database
DBCC INDEXDEFRAG ('AdventureWorks2012','ordDemo','idx_refno')
GO
The
DBCC
INDEXDEFRAG
command will be deprecated from the future
version of SQL Server, so it is best practice to avoid that command and
use the alternate commands given in the previous list.
How it works...
Index reorganization, also known as defragmentation, occurs serially. This means that
the operation on a single index is performed using a single thread. No parallelism occurs.
Also, operations on multiple indexes from the same
REORGANIZE
or
DBCC
INDEXDEFRAG
statement are performed on one index at a time.
Reorganizing is simply rearranging data pages to match the physical sorting order of data
pages with the logical sorting order in index leaf node. Unlike
REBUILD
index,
REORGANIZE
will not add new pages to match the Fill Factor defined, but it compacts the pages. If any page
gets empty during the compacting process, it gets removed, so the Fill Factor option is not
supported.
REORGANIZE
will not lock objects for a long time and hence the Online option is
also not supported, as it is always in Online mode, by default.
There's more...
The user should be the owner of the table or a member of the
sysadmin
fixed server role, the
db_owner
fixed database role, or the
db_ddladmin
fixed database role, in order to perform
REORGANIZE
or
DBCC
INDEXDEFRAG
.
How to find missing indexes
By now, we hope that you have understood the requirement of the index in performance.
While developing a database table, initially it is not always possible for us to predict the right
column as an index. So, as per our prediction, we used to generate an index that might be
helpful; sometimes it would not even be used, and sometimes, we would need other indexes
as well, apart from the initial index we had created. So, now the question arises as to how to
find the indexes that are not even generated. How can we predict which indexes are missing
and which we need to create?
Search WWH ::
Custom Search