Index Scanning

Please read following statements. statement 1 runs very faster and uses index scan. The statement 2 runs very slow uses table scan.

The Following things are already done.

DBCC DBREINDEX
Sp_updatestats
update statistics

It is only affecting one column (date_added) and also other statistic name start with _WA_SYS_* but date column starts as statistic_date_added. This has happened only for the last two days. All the production store procedure accessing date_columns now running longer.

Is there anybod can explain this and please post a solution. ?

Raj Sankar

Statement 1.
select * from rx_control where store_id = @.store_id and date_added between '08/01/02' and '08/20/02'
------------------------
statement 2.
------------
declare @.bdate as datetime
declare @.edate as datetime
declare @.rxid as int

set @.store_id = '52'
set @.rxid = '158315'
set @.bdate = '08/01/02'
set @.edate = '08/10/02'

select * from rx_control where store_id = @.store_id and date_added between @.bdate and @.edate
------------------------check with dbcc showcontig report and depending on this u should go for dbcc indexfrag on respective fields.

if still the problem persists send me the showcontig report for the respectve table if u can.|||I had problem like this or very similar.
Query optimizer creates an execution plan based on conditions. For the first statement optimizer knows date range exactly and creates the best execution plan.
The second statement is using local variables and optimizer creates common execution plan (without using particular conditions).
How to resolve this problem? In my situation I created additional table where parameters were saved and I was using join. It looks like a stupid decision but it works.
You can try to use this idea.

Good luck

index defrag

Dear friends,
I just defrag my index, this is the results.
Before defrag
--
DBCC SHOWCONTIG scanning 'tbSOTransDetail' table...
Table: 'tbSOTransDetail' (1765581328); index ID: 3,
database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 151
- Extents Scanned.......................: 20
- Extent Switches.......................: 147
- Avg. Pages per Extent..................: 7.5
- Scan Density [Best Count:Actual Count]......: 12.84%
[19:148]
- Logical Scan Fragmentation ..............: 49.01%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 2748.8
- Avg. Page Density (full)................: 66.04%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
page scanned pages moved pages removed
146 104 40
After defrag
--
DBCC SHOWCONTIG scanning 'tbSOTransDetail' table...
Table: 'tbSOTransDetail' (1765581328); index ID: 3,
database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 111
- Extents Scanned.......................: 16
- Extent Switches.......................: 22
- Avg. Pages per Extent..................: 6.9
- Scan Density [Best Count:Actual Count]......: 60.87%
[14:23]
- Logical Scan Fragmentation ..............: 4.50%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 821.8
- Avg. Page Density (full)................: 89.85%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
My questions are,
1. can we make logical scan fragmentation equal with 0 ?
2. I got 75% extent scan fragmentation , is it bad ? why
is the number of extent scan fragmentation still 75 after
I defrag it ?
3. Can we make scan density value [Best Count:Actual
Count] equal with 100 % ?
Thanks.Hi Kresna
DBCC INDEXDEFRAG will only remove logical scan fragmentation and getting it
down to 4% is pretty good. The only way to get scan density up to 100% and
extent scan fragmentation down is to actually rebuild the clustered index.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kresna rudy kurniawan" <kresna_rk@.hotmail.com> wrote in message
news:034a01c39c3a$939c5c00$a601280a@.phx.gbl...
> Dear friends,
> I just defrag my index, this is the results.
> Before defrag
> --
> DBCC SHOWCONTIG scanning 'tbSOTransDetail' table...
> Table: 'tbSOTransDetail' (1765581328); index ID: 3,
> database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 151
> - Extents Scanned.......................: 20
> - Extent Switches.......................: 147
> - Avg. Pages per Extent..................: 7.5
> - Scan Density [Best Count:Actual Count]......: 12.84%
> [19:148]
> - Logical Scan Fragmentation ..............: 49.01%
> - Extent Scan Fragmentation ...............: 75.00%
> - Avg. Bytes Free per Page................: 2748.8
> - Avg. Page Density (full)................: 66.04%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
>
> page scanned pages moved pages removed
> 146 104 40
> After defrag
> --
> DBCC SHOWCONTIG scanning 'tbSOTransDetail' table...
> Table: 'tbSOTransDetail' (1765581328); index ID: 3,
> database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 111
> - Extents Scanned.......................: 16
> - Extent Switches.......................: 22
> - Avg. Pages per Extent..................: 6.9
> - Scan Density [Best Count:Actual Count]......: 60.87%
> [14:23]
> - Logical Scan Fragmentation ..............: 4.50%
> - Extent Scan Fragmentation ...............: 75.00%
> - Avg. Bytes Free per Page................: 821.8
> - Avg. Page Density (full)................: 89.85%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
>
> My questions are,
> 1. can we make logical scan fragmentation equal with 0 ?
> 2. I got 75% extent scan fragmentation , is it bad ? why
> is the number of extent scan fragmentation still 75 after
> I defrag it ?
> 3. Can we make scan density value [Best Count:Actual
> Count] equal with 100 % ?
>
> Thanks.
>
>|||You should read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
For a table with only 151 pages I wouldn't even bother worrying about
fragmentation.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ueKQctEnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Hi Kresna
> DBCC INDEXDEFRAG will only remove logical scan fragmentation and getting
it
> down to 4% is pretty good. The only way to get scan density up to 100% and
> extent scan fragmentation down is to actually rebuild the clustered index.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kresna rudy kurniawan" <kresna_rk@.hotmail.com> wrote in message
> news:034a01c39c3a$939c5c00$a601280a@.phx.gbl...
> > Dear friends,
> >
> > I just defrag my index, this is the results.
> >
> > Before defrag
> > --
> > DBCC SHOWCONTIG scanning 'tbSOTransDetail' table...
> > Table: 'tbSOTransDetail' (1765581328); index ID: 3,
> > database ID: 7
> > LEAF level scan performed.
> > - Pages Scanned........................: 151
> > - Extents Scanned.......................: 20
> > - Extent Switches.......................: 147
> > - Avg. Pages per Extent..................: 7.5
> > - Scan Density [Best Count:Actual Count]......: 12.84%
> > [19:148]
> > - Logical Scan Fragmentation ..............: 49.01%
> > - Extent Scan Fragmentation ...............: 75.00%
> > - Avg. Bytes Free per Page................: 2748.8
> > - Avg. Page Density (full)................: 66.04%
> > DBCC execution completed. If DBCC printed error messages,
> > contact your system administrator.
> >
> >
> >
> > page scanned pages moved pages removed
> >
> > 146 104 40
> >
> > After defrag
> > --
> >
> > DBCC SHOWCONTIG scanning 'tbSOTransDetail' table...
> > Table: 'tbSOTransDetail' (1765581328); index ID: 3,
> > database ID: 7
> > LEAF level scan performed.
> > - Pages Scanned........................: 111
> > - Extents Scanned.......................: 16
> > - Extent Switches.......................: 22
> > - Avg. Pages per Extent..................: 6.9
> > - Scan Density [Best Count:Actual Count]......: 60.87%
> > [14:23]
> > - Logical Scan Fragmentation ..............: 4.50%
> > - Extent Scan Fragmentation ...............: 75.00%
> > - Avg. Bytes Free per Page................: 821.8
> > - Avg. Page Density (full)................: 89.85%
> > DBCC execution completed. If DBCC printed error messages,
> > contact your system administrator.
> >
> >
> > My questions are,
> > 1. can we make logical scan fragmentation equal with 0 ?
> > 2. I got 75% extent scan fragmentation , is it bad ? why
> > is the number of extent scan fragmentation still 75 after
> > I defrag it ?
> > 3. Can we make scan density value [Best Count:Actual
> > Count] equal with 100 % ?
> >
> >
> > Thanks.
> >
> >
> >
>