Home » Indexes

Indexes

sql server DBA 999

SQL Server 2008/2005 – Comparing Index rebuild/Reorganize and increase in Transaction Log file

Comparing ALTER INDEX options Index Rebuild and Reorganize:

1.Index Rebuild requires building the new index before dropping the old one. This means there has to be enough free space in the database to accommodate the new index; otherwise the database will grow to provide the required free space. This can be problematic for large indexes. Index Reorganize only requires 8KB of additional space in the database.
2.Index Rebuild can use multiple CPUs so the operation runs faster. Index Reorganize is always single-threaded.
3.Index Rebuild may require long-term locks on the table that can limit concurrent operations. Index REORGANIZE doesn’t hold blocking locks and it’s always been an online operation.
4.Index … Read the rest

sql server DBA 999

SQL Server 2008 Indexes: Types, Performance gain, Lookup and more…

Indexes are secret for faster query execution when designed properly.
Types of indexes in SQL Server :

1. Clustered Indexes: A clustered index determines the physical order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name.
2.Non-Clustered Indexes: Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:
• The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.
• The leaf layer of a nonclustered index is made up of index pages instead of data pages.

Nonclustered indexes can be defined on a table or view with a clustered index or a heap. … Read the rest

sql server DBA 999

SQL Server 2008 Index Maintenance Rebuild vs Reorganize

Today, let us discuss on Index Maintenance on SQL Server. Indexes tend to get fragmented as the data gets updated. If the update is under the free space on page which was specified using Fill Factor of index creation or Rebuild then it would not be fragmented. The clustered index is the index by which the Leaf Nodes are Stored and thus does not require additional space.

ALTER INDEX ... REORGANIZE
ALTER INDEX .... REBUILD

Here are some Pros and Cons between the two:
1) Index rebuild works by re-creating the index internally and when that completes, it drops the existing index where as index reorganize is the process of physically re-organizing the leaf nodes of the index.
2) During … Read the rest