| Author |
Topic |
|
Pace
Constraint Violating Yak Guru
264 Posts |
 Posted - 2007-10-30 : 04:47:33
|
I feel I have a REAL question this time  I have been trying to up my sql knowledge and have been reading the following; [url]http://www.sql-server-performance.com/articles/dba/dt_dbcc_showcontig_p1.aspx[/url]]Now I think I understand it fully but I have a question about this. I understand Scan density, logical and extent scan fragmentation and I also now understand page density. My question. How do you know depending on your scenario how much to leave in your pages? Would you reindex heavily read tables to be of high scan density with low logical and extent fragmentation and aim for as much page density as possible? Well what if you have a heavily read table that also has a lot of inserts on it daily. What percentage of page density would you tell dbcc reindex to leave in your pages in order to avoid page splits? Or rather, would you allow them to occur and just have dbcc reindex run often? As im migrating from sql 2000 this is the first sql 2005 function I have started to look at and I already have so many questions..."Impossible is Nothing"  |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-30 : 07:55:55
|
We just have a threshold and rebuild the index if it exceeds that. We rebuild indexes every night so it is not long before an out-of-shape-index gets rebuilt, and equally the rebuild doesn't run for very long (compared to doing it, say, once a week or once a month )We also monitor the elapsed time for our Sprocs, and worry when their run-time increases significantly (we automatically recompile Sprocs which are detected as being "slow-running")We Defrag any index that is "large" (I forget the threshold for that, I think its 1,000 pages, but obviously that's something you could "tune"), and Reindex anything smaller. (Different terminology in SQL 2005, but I expect you get my drift ...)Kristen |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-30 : 23:13:18
|
| >> im migrating from sql 2000 this is the first sql 2005 function I have started to look at Sql2k works same way. To avoid page split, you have to find out how many new rows will be added to the table between reindex, then set fill factor properly. But leaving too much free space in page means more disk i/o since need read more pages to fetch certain amout of rows, that may affect performance. |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2007-10-31 : 12:41:50
|
| Can you tell us what your thresholds are for your DBREINDEX or INDEXDEFRAG choices, Kristen? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-31 : 14:13:01
|
| well if you're interested ours is 15% for reindex and 10% for defragbut we also have guid's as clustered index everywhere... (no commments about that please )_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2007-10-31 : 14:23:15
|
| Do you take into consideration # of pages and extent switches as well? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-31 : 14:58:02
|
| no._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-01 : 07:54:01
|
| "Can you tell us what your thresholds are for your DBREINDEX or INDEXDEFRAG choices"From memory its > 1,000 pages = DEFRAG. Below that we REINDEXI think I based it on how long it took. I suppose I assumed that a few seconds would not kill anything else! But that was all under SQL 2000, so different criteria may apply in SQL 2005Kristen |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2007-11-01 : 09:40:23
|
quote:
Originally posted by Kristen
"Can you tell us what your thresholds are for your DBREINDEX or INDEXDEFRAG choices"From memory its > 1,000 pages = DEFRAG. Below that we REINDEXI think I based it on how long it took. I suppose I assumed that a few seconds would not kill anything else! But that was all under SQL 2000, so different criteria may apply in SQL 2005Kristen
Thanks Kristen.I'm suprised that the decision switch for DBREINDEX vs INDEXDEFRAG is so simple for you guys. I was expecting some fairly complicated method for determining size/pages/extents.I'm working up a 'better' defrag script than ones that I've done previously for 2000. I'm pretty well set & satisfied with my 2005 YADS... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-01 : 10:12:55
|
| "I'm suprised that the decision switch for DBREINDEX vs INDEXDEFRAG is so simple for you guys"For me there are only two criteria I can think of:1) How long will it block users2) How much disk space might it add to MDF fileboth those are covered by a simple "index size" value.In SQL 2005 the REINDEX is much MUCH faster than SQL 2000 ... so the Blocking time is less. I haven't looked into the MDF file growth aspect though.Kristen |
|
|
|
|
DBCC SHOWCONTIG()