Regarding fragmentation

Posted on 2008-10-22
Last Modified: 2012-05-05
Hi Experts i would be rebuilding the index inoder to get rid of framentation .
Im bit worried about the fill factor , non-clustered indexes, primary key and foreign key, any dependency with other table. Iam enclosing the query for your reference, i need to understand how to take care for fill factor or non clustered indexes and any other dependency , is it taken care by sql engine or by default it will take the fill factor which already exist.
Question by:Sandeepiii
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
LVL 37

Expert Comment

ID: 22775324
what do you mean by
is it taken care by sql engine or by default it will take the fill factor which already exist ?

if you don't specify a new fill factor, sql server will use the one that is already defined

Author Comment

ID: 22775560
what about the references like primary key , foreign key ,non clustered indexes for example
a table will have clustered index as well as non clustered indexes will non clustered indexes are also rebuild .thanks
LVL 37

Accepted Solution

momi_sabag earned 250 total points
ID: 22775626
depends on how you are going to rebuild but you can specify it in the rebuild command (if you use  DBCC DBREINDEX  for example)
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

LVL 18

Assisted Solution

jmoss111 earned 250 total points
ID: 22775752
If you have the luxury of time, you could drop and recreate your indexes. Also, one other thing to consider is total fragmentation on the database. A solution is to download contig.exe from Its a free download from M$ and  allows you to make contiguous a file.

Author Comment

ID: 22775897
thanks ok i will download the contig.exe file , in the meanwhile i have got a link from microsoft
which helps in fragmentation.
I don't have luxury of time as i have to do the activity online no downtime is given to us.
Iam using the code in the script which i have attached , alter index is sql server2005 enterprise edition.
LVL 18

Expert Comment

ID: 22775981
contig requires no downtime

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question