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)
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

726 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