Solved

Regarding fragmentation

Posted on 2008-10-22
8
246 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
contig requires no downtime
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now