December 29, 2003 at 8:42 am
So, I figure I'll rebuild indexes based on some criteria such as:
AvgPageDensity < 80 OR
ScanDensity < 80 OR
logicalfrag > 10
After rebuilding some of them the logical frag # got higher instead of lower
I'm planning on defragging the drives soon as well .... does defragging the drives have any effect on the indexes ?
Any tips on tweaking performance and best practices for database cleanup would be appreciated.
December 29, 2003 at 8:47 am
Profiler may help..to tell if you also need indexing other columns
Mike
December 29, 2003 at 10:34 am
defragging should help performance, but as far as I know, it doesn't change the fragmentation internally. Your extents will still be where they are relative to each other.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 30, 2003 at 1:11 am
keep in mind that rebuild index will not affect your datapages, unless you have specified a clustered index. (there should be one "by default dba behaviour" for every table )
Also run DBCC UPDATEUSAGE to be sure the statistics are acurate. (check BOL)
maybe this helps a bit :
- http://www.sqlservercentral.com/columnists/chedgate/clusterthatindex_printversion.asp
- http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
- http://www.sql-server-performance.com/rebuilding_indexes.asp
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 30, 2003 at 9:46 am
Rebuilding the clustered index with Sort_In_Tempdb has improved the
logical contiguity tremendously for me. Be sure to do the Drop Existing
if you have any non clustered indexes. Search BOL for "tempdb and Index Creation"
this article has valuable information.
Hope it helps.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply