Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Maintain indexes in a SQL Server 2005 database

By Exforsys | on December 20, 2005 |
SQL Server 2005

Maintain indexes in a SQL Server 2005 database

In this tutorial you will learn about maintaining Indexes in a SQL Server 2005 database – Dealing with Fragmented indexes, Reorganizing an Index, Rebuilding an Index, Disabling Non-clustered Indexes to Reduce Disk Space During Rebuild Operations, Rebuilding Large Indexes, Setting Index Options, Page Splits and Performance Considerations, Max Degree of Parallelism, Online Index Operations, Partition Index Operations, Statistical Information, Asynchronous Statistics Updates, Disabling Automatic Statistics, Statistics after Upgrading a Database to SQL Server 2005, Bulk copy options and Index operation logging.

The DBA has to ensure optimal performance of the database. One of the key elements in database maintenance is to ensure that minimum disk I/O is performed in queries. Making good indexes is a means to this end. The activities a database administrator performs to maintain his indexes are:

  1. Reorganizing and rebuilding indexes. This process involves defragmenting of indexes and optimization of disk space.
  2. Using the Fill factor option to fine tune the index data storage and performance.
  3. Performing index operations online by using the LINE index option to provide user access to data during index operations
  4. Configuring parallel Index operations by using the max degree of parallelism option.
  5. Using the Query optimizer to develop optimal query plans based on statistics.
  6. Improving the performance of bulk copy operations on tables with clustered and non clustered indexes.
  7. Selecting suitable Recovery model of the database for index operations and listing the types of logging available.

Dealing with Fragmented indexes

Though the SQL Server 2005 data base engine automatically maintains indexes whatever the operations made on the database, the modifications can cause a degradation of the index over time. This is turn will degrade query performance. To help the DBA over come the problems of fragmented indexes, SQL Server 2005 provides an option of reorganizing or rebuilding the index. This option can be used on whole indexes or partitioned indexes.

Fragmentation of an index can be recognized by a process of analysis using the sys.dm_db_index_physical_stats function. This function detects fragmentation of a particular index or all indexes in the database. For partitioned indexes, the information is provided for each partition. The results of this operation are displayed in the AvgFragmentation column. The AvgFragmentSize column describes the average number of pages in one fragment in an index. Once the fragmentation size and value is known it has to be evaluated against the parameters for correction provided with SQL Server 2005. If the AvgFragmentation value is <=30% the corrective would be to ALTER INDEX REORGANIZE else the corrective would be to ALTER INDEX REBUILD WITH (ONLINE=ON). The first option is always done online while the second option can be used online or offline. The syntax would be as under:

USE Exforsys;
GO
SELECT IndexName, AvgFragmentation FROM sys.dm_db_index_physical_stats(N’Employeetransact.EmployeeID’, DEFAULT, DEFAULT, N’Detailed’);
GO

Reorganizing an Index

In SQL Server 2005 the ALTER INDEX REORGANIZE statement has replaced the DBCC INDEXDEFRAG statement. A single partition of a partitioned index can be reorganized using this statement.

When an index is reorganized the leaf level of the clustered and non clustered indexes on tables and views are reorganized and reordered to match the logical order—i.e. left to right of the leaf nodes. The index is organized within the allocated pages and if they span more than one file they are reorganized one at a time. No pages are migrated between files. Moreover, pages are compacted and empty pages created as a consequence are removed and the disk space released. The compaction is determined by the fill factor value in sys.indexes catalog view. Large object data types contained in clustered index or underlying tables will also be compacted by default if the LOB clause is present..

The good news is that the reorganize process is economical on the system resources and is automatically performed online. There are no long term blocking locks which jam up the works!

DBAs are advised to reorganize the index when it is minimally fragmented. Heavily fragmented indexes will require rebuilding.

Rebuilding an Index

When an index is rebuilt, it is dropped and a new one is created. In the process fragmentation is removed and disk space is reclaimed. The fill factor setting is used to reorder the pages after compacting in a sequential order. Performance is improved and number of page reads is reduced. The following methods are used to drop and rebuild the index.

1. ALTER INDEX with the REBUILD clause.
2. CREATE INDEX with the DROP_EXISTING clause.

Each of these functions have their own advantages and disadvantages.

Disabling Non-clustered Indexes to Reduce Disk Space During Rebuild Operations

When a rebuilding operation is performed it is a best practice to disable the non-clustered indexes. Diabling the non-clustered index implies that the data rows are deleted but the definition is retained in metadata. The index is enabled after it is rebuilt.

Rebuilding Large Indexes

Indexes which have more than 128 extents must be rebuilt in two phases—the logical and the physical. The logical phase the allocation units of the index are marked for deallocation, the data rows are copied and sorted before they are moved to new allocation units in the rebuilt index. The physical phase involves dropping of the allocation units marked for deallocation in short transaction without locks.

Setting Index Options

When reorganizing an index, the index options cannot be specified as a rule. However, the ALTER INDEX REBUILD and CREATE INDEX WITH DROP_EXISTING allows users set the options such as PAD_INDEX, FILLFACTOR, SORT_IN_TEMPDB, IGONORE_DUP_KEY and STATISTICS_NORECOMPUT etc. Additionally the ALTER INDEX statement allows the specification of ALLOW_PAGE_LOCKS and ALLOW_ROW_LOCKS

Page Splits and Performance Considerations

A page split is a process by which a new row added to a table is some rows in the table are moved to a new page to make room for the new records. This happens during reorganization at the risk of being resource intensive and causing fragmentation. To reduce the risk the fill factor has to be correctly selected. Else the index will have to be frequently rebuilt.

Max Degree of Parallelism

When several processors are used to perform the scan and sort operations on indexes, the number of processors to be used is to be specified. This can be specified in the configuration option max degree of parallelism and also by the current workload. This option limits the number of processors that can be used in parallel. This option is available only for the Enterprise version of the edition.

When the DBA wants to manually configure the number of processors that can be used to run the index statement the MAXDOP index statement is used. This limits the number of processors to be used during an index operation of the query optimizer. This option overrides the max degree of parallelism option for the query. The MAXDOP index option cannot be specified for the ALTER INDEX REORGANIZE statement.

Online Index Operations

Concurrent index operations can be performed during Online index operations. The MAXDOP operation can be used ot control the number of processors dedicated ot online index operations.

Partition Index Operations

Partition index operations can be memory resource intensive if the query optimizer applies degrees of parallelism.

Statistical Information

Statistical information can be created about the distribution of values in a column. This is used by the query optimizer to determine the optimal query plan by estimating the cost of using an index to evaluate a query. These values are sorted by the database engine on which the statistics is being built and a histogram is created for a maximum of 200 values separated by intervals. Additional information is introduced on statistics created on char, varchar, varchar(max), nchar, nvarchar(max), text and ntext columns. This is known as string summary. The string summary helps the query optimizer estimate the selectivity of query predicates on string patterns. This makes for accurate estimates of result set sizes and frequently better query plans.

When the query optimizer is configured to automatically store statistical information about indexed columns, statistics on the columns are automatically generated without indexes that are used in a predicate.

Asynchronous Statistics Updates

The AUTO_UPDATE_STATISTICS_ASYNC option can be used to ensure that the query optimizer is prevented from returning a result set while it waits for the out of date statistics to be updated and compiled. The out of date statistics are put on queue for updating by a worker thread in a background process and the query and the concurrent queries compile immediately. This option is to be set at database level and determines the update method all statistics at the database.

Disabling Automatic Statistics

Automatic statistics can be disabled for a particular column or index by using the sp_autostats system stored procedure or the STATISTICS_NORECOMPUTE clause of the CREATE INDEX statement. There are other clauses also which can be used with the update statement etc to prevent automatic generation of statistics. However, the statistics can be updated manually using the sp_createstats system stored procedure.

Statistics after Upgrading a Database to SQL Server 2005

When the user upgrades the version of SQL server the statistics of the earlier version is treated as out of date. On first use, the statistics will have to be updated using the AUTO_UPDATE_STATISTICS database option.

Bulk copy options and Index operation logging.

Bulk copy options are useful to copy data into a table without a non clustered index. Logging the index operations minimally during this process makes it more efficient and reduces the possibility of the index operation filling the log. However the option depends on whether the table is indexed or not and whether the table is empty or not. If the table is empty, both data and index pages are minimally logged. If the table has no clustered index, data pages are always minimally logged. If the table is empty, the index pages are minimally logged. In non empty tables index pages are fully logged.

In the next section we shall look at the use of the Database Tuning Advisor.

« « SQL Server 2005 – Database Backup
SQL Server 2005 – Defining Indexes » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 0 Followers
  • Popular
  • Recent
  • SQL Server 2005 Installation

    November 23, 2005 - 0 Comment
  • Getting started with SQL Server 2005

    August 9, 2005 - 0 Comment
  • Using SQL Sever Management Studio – Part 1

    December 15, 2005 - 0 Comment
  • Native HTTP Support in SQL Server 2005

    November 1, 2005 - 0 Comment
  • SQL Server 2005 – Managing Permissions

    December 26, 2005 - 0 Comment
  • Planning to Install SQL Server 2005

    November 29, 2005 - 0 Comment
  • Overview of SQL Server 2005 for the Database Developer

    September 8, 2005 - 0 Comment
  • Using SQL Sever Management Studio – Part 2

    December 15, 2005 - 0 Comment
  • NET CLR in SQL Server 2005

    November 1, 2005 - 0 Comment
  • SQL Server 2005 – Managing Certificates

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Configuring Replication

    January 11, 2006 - 0 Comment
  • SQL Server 2005 Replication Enhancements

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Mirror Server

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Introduction to Data Availability

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Backing up a Database

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Using Database Snapshots

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Disaster Recovery

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Certificates

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Permissions

    December 26, 2005 - 0 Comment
  • Managing SQL Server 2005 Security

    December 24, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • SQL Server 2005 – Configuring Replication
  • SQL Server 2005 Replication Enhancements
  • SQL Server 2005 – Mirror Server
  • SQL Server 2005 – Introduction to Data Availability
  • SQL Server 2005 – Backing up a Database

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT