Rebuild Index helps temporarily

I have a query that grinds to a halt after a heavy load. I have
discovered, through many variations and trials, that if I rebuild the
index of a certain table, my performance improves incredibly. Once the
load increases, the performance degrades. We rebuild our indexes daily
(overkill, but we are not 24 x 7). If you look at the table's indexes
the fragmentation is about 30%. It does NOT change after a rebuild
even though the performance does. Any ideas? (It is not a huge table
~ 5000 rows. Medium on inserts and updates)
These are the results of the SHOWCONTIG sproc
DBCC SHOWCONTIG scanning 'InventoryCount' table...
Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned........................: 28
- Extents Scanned.......................: 10
- Extent Switches.......................: 9
- Avg. Pages per Extent..................: 2.8
- Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 70.00%
- Avg. Bytes Free per Page................: 2499.0
- Avg. Page Density (full)................: 69.13%What is the fill factor of the rebuild command you are using and how does th
e
query looks like?
"magkip@.hotmail.com" wrote:

> I have a query that grinds to a halt after a heavy load. I have
> discovered, through many variations and trials, that if I rebuild the
> index of a certain table, my performance improves incredibly. Once the
> load increases, the performance degrades. We rebuild our indexes daily
> (overkill, but we are not 24 x 7). If you look at the table's indexes
> the fragmentation is about 30%. It does NOT change after a rebuild
> even though the performance does. Any ideas? (It is not a huge table
> ~ 5000 rows. Medium on inserts and updates)
> These are the results of the SHOWCONTIG sproc
> DBCC SHOWCONTIG scanning 'InventoryCount' table...
> Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> TABLE level scan performed.
> - Pages Scanned........................: 28
> - Extents Scanned.......................: 10
> - Extent Switches.......................: 9
> - Avg. Pages per Extent..................: 2.8
> - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> - Logical Scan Fragmentation ..............: 25.00%
> - Extent Scan Fragmentation ...............: 70.00%
> - Avg. Bytes Free per Page................: 2499.0
> - Avg. Page Density (full)................: 69.13%
>|||The query is long and complicated. We are optimizing it now. It can
run in <2 seconds after the rebuild.
I set the fill factor to 80%.
Edgardo wrote:[vbcol=seagreen]
> What is the fill factor of the rebuild command you are using and how does
the
> query looks like?
> "magkip@.hotmail.com" wrote:
>|||Oops. I misunderstood. I just use 'Rebuild All indexes'.
mag...@.hotmail.com wrote:[vbcol=seagreen]
> The query is long and complicated. We are optimizing it now. It can
> run in <2 seconds after the rebuild.
> I set the fill factor to 80%.
> Edgardo wrote:|||Edgardo and Magkip,
I found this interesting and have tried to increase the fill factor on some
tables in the past. In my case however, I boosted it only slightly (from 10
percent to 15 percent). Magkip, you have boosted it to 80 percent.
Is there a method to determine just how much to change this fill amount?
For example, would a change to 80 percent mean that the table will grow by a
s
much as 80 percent of the original size each time it grows or is this more o
f
a way of forcing the disk allocation to be large enough to hold any temporar
y
space required during the growth? Or is it some of both?
--
Regards,
Jamie
"Edgardo Valdez, MCTS / MCITP" wrote:
[vbcol=seagreen]
> What is the fill factor of the rebuild command you are using and how does
the
> query looks like?
> "magkip@.hotmail.com" wrote:
>