October 1, 2009 at 11:55 am
I would like to know what is the best procedure to defrag
*Data
*Index
*Files
in a SQL Server 2005 box.
For Index, I use the code from
http://technet.microsoft.com/en-us/library/bb838727.aspx (Rebuild & Reorg), but seems like it does not bring the fragmentation down much.
I do not do anything for data or files.
I would like to start doing maintenance jobs (automated) every weekend or so.
Any thoughts will be helpful.
Dan
October 1, 2009 at 12:03 pm
The simplest way is to add a rebuild task to a maintenance plan that you run weekly.
Anything more complex than that, you'll need to decide what rules it needs to follow.
It's pretty common to run something nightly that reorgs the indexes if they are less than 20% fragmented, and rebuilds above that. You can ignore indexes with too few pages for it to matter.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 1, 2009 at 12:15 pm
GSquared
When you say the rebuild task - it is the Alter Index Rebuild and Reorg, right? that is what I was hoping the code from MS will do for me (The link I had sent). This should fix the .ndf, right?
What about the fragmentation in the data (the .mdf) and the files(Windows Server 2003 has a built-in defragmentor; is that the best to use?)
Dan
October 1, 2009 at 3:57 pm
right? Yes rebuild task mean Alter Index Rebuild and Reorg.
DBCC DBREINDEX is for rebuild index and DBCC INDEXDEFRAG is for reorg.
.ndf and .mdf both are data file,the difference is only .mdf is primary file and when you add another files they are .ndf files and log files are .ldf files extention.
Windows Server 2003 has a built-in defragmentor: I believe it would be for physical hard disk drives to defragment but it will not defragment the sql server data files.
Thanks
Irfan
October 1, 2009 at 4:04 pm
you can use a Maintanence plan too - it is good for beginners 😉
good luck!
October 1, 2009 at 6:55 pm
1)The index files are all chosen to be seperate from the data file and is saved as .ndf. I had mistaken that dm_db_index_physical_stats checks only the fragmentation in the index (ndf) files.
Is dm_db_index_physical_stats the only way to find/know the fragmentation in the mdf and ndf files?
2)
Could you suggest why the code in http://technet.microsoft.com/en-us/library/bb838727.aspx is not working for me. What may I be doing wrong? The fragmentation level still remains high on some indexes, even after running the code.
Is DBCC DBREINDEX & DBCC INDEXDEFRAG for SS 2000? or 2005 also?.
3)
The SQL Server Data files are also windows files, anyways, right? As SQL data can be fragmented, windows files are also fragmented. Hence my question on defraging the windows drives where the mdf and ndf and ldf files sit. I can use the Windows Server 2003 built-in defragmentor. Has anyone had good performance improvement for SQL Database because of defraging the data disk drives.
Thanks
Dan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply