Solved

Regarding fragmentation

Posted on 2008-10-22
8
248 Views
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.
frag.txt
0
Comment
Question by:Sandeepiii
  • 2
  • 2
  • 2
8 Comments
 
LVL 37

Expert Comment

by:momi_sabag
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
0
 

Author Comment

by:Sandeepiii
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
0
 
LVL 37

Accepted Solution

by:
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)
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 18

Assisted Solution

by:jmoss111
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 sysinternal.com. Its a free download from M$ and  allows you to make contiguous a file.
0
 

Author Comment

by:Sandeepiii
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.
http://technet.microsoft.com/en-us/library/ms188388(SQL.90).aspx
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 ......rebuild.it is sql server2005 enterprise edition.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22775981
contig requires no downtime
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

777 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