Index Statistics

I am toying around with the article below to try to get my brain around some
index tuning issues and understand how to apply the knowledge to our
database.
http://www.sql-server-performance.c..._statistics.asp
So I did a DBCC SHOW_STATISTICS on one of our larger indexes, and I don't
understand these results:
Range-Hi-Key Range-Rows Eq-Rows Distinct-Range-Rows Average-Range-Rows
BASFITSERVICES 664.69312 683.0 0
974.95349
If Range-Rows is how many items mapped to this index key and Eq-Rows
represents how many items are equal to this index key, then how can Eq-Rows
be greater, and why would the Average be even higher?
--
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b{2})" -- William ShakespeareHi Mike
I think you have not quite understood the values being returned!
From BOL:
RANGE_HI_KEY Upper bound value of a histogram step.
RANGE_ROWS Number of rows from the sample that fall within a histogram step,
excluding the upper bound.
EQ_ROWS Number of rows from the sample that are equal in value to the upper
bound of the histogram step.
DISTINCT_RANGE_ROWS Number of distinct values within a histogram step,
excluding the upper bound.
AVG_RANGE_ROWS Average number of duplicate values within a histogram step,
excluding the upper bound (RANGE_ROWS / DISTINCT_RANGE_ROWS for
DISTINCT_RANGE_ROWS > 0).
You may also want to look at the pages on this in "Inside SQL Server 2000"
by Kalen Delany ISBN 0-7356-0998-5
John
"Mike Labosh" wrote:

> I am toying around with the article below to try to get my brain around so
me
> index tuning issues and understand how to apply the knowledge to our
> database.
> http://www.sql-server-performance.c..._statistics.asp
> So I did a DBCC SHOW_STATISTICS on one of our larger indexes, and I don't
> understand these results:
> Range-Hi-Key Range-Rows Eq-Rows Distinct-Range-Rows Average-Range-Ro
ws
> BASFITSERVICES 664.69312 683.0 0
> 974.95349
> If Range-Rows is how many items mapped to this index key and Eq-Rows
> represents how many items are equal to this index key, then how can Eq-Row
s
> be greater, and why would the Average be even higher?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "(bb)|(^b{2})" -- William Shakespeare
>
>|||Thanks, John!
Mike, you might also want to look at this whitepaper:
http://msdn.microsoft.com/library/d...r />
query.asp
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:D09E0695-CBD6-4B33-BF3F-E5DCF6B4B294@.microsoft.com...
> Hi Mike
> I think you have not quite understood the values being returned!
> From BOL:
> RANGE_HI_KEY Upper bound value of a histogram step.
> RANGE_ROWS Number of rows from the sample that fall within a histogram
> step,
> excluding the upper bound.
> EQ_ROWS Number of rows from the sample that are equal in value to the
> upper
> bound of the histogram step.
> DISTINCT_RANGE_ROWS Number of distinct values within a histogram step,
> excluding the upper bound.
> AVG_RANGE_ROWS Average number of duplicate values within a histogram step,
> excluding the upper bound (RANGE_ROWS / DISTINCT_RANGE_ROWS for
> DISTINCT_RANGE_ROWS > 0).
> You may also want to look at the pages on this in "Inside SQL Server 2000"
> by Kalen Delany ISBN 0-7356-0998-5
> John
>
>
> "Mike Labosh" wrote:
>|||Of course if you are in the UK on 8th or 10th Feb and wish to hear the
master herself then check out http://www.sqlserverfaq.com/
John
:):)
Kalen Delaney wrote:
> Thanks, John!
> Mike, you might also want to look at this whitepaper:
>
>
http://msdn.microsoft.com/library/d...l/statquery.asp
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
> news:D09E0695-CBD6-4B33-BF3F-E5DCF6B4B294@.microsoft.com...
histogram
the
step,
histogram step,
Server 2000"
around
our
http://www.sql-server-performance.c..._statistics.asp
I don't
Eq-Rows
can|||And thanks again...:-)
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1106676698.587786.264330@.f14g2000cwb.googlegroups.com...
> Of course if you are in the UK on 8th or 10th Feb and wish to hear the
> master herself then check out http://www.sqlserverfaq.com/
> John
> :):)
>
> Kalen Delaney wrote:
> http://msdn.microsoft.com/library/d.../>
atquery.asp
> histogram
> the
> step,
> histogram step,
> Server 2000"
> around
> our
> http://www.sql-server-performance.c..._statistics.asp
> I don't
> Eq-Rows
> can
>

Index size.

Where can I get the size of the index to add to the below query.
select
t.name [Table],
i.Name [Index],
c.name [Column],
ic.key_ordinal [Key Ordinal],
is_included_column [Included],
is_descending_key [Descending key],
i.type_desc [Index Type],
i.is_unique [Unique Index],
i.is_disabled [Index disabled],
i.fill_factor [Fill Factor],
i.is_hypothetical [hypothetical]
from
sys.indexes i
inner join sys.index_columns ic
on i.index_id = ic.index_id
and i.object_id = ic.object_id
inner join sys.columns c
on c.column_id = ic.column_id
and c.object_id = ic.object_id
inner join sys.tables t
on i.object_id = t.object_id
where t.name='Orders'
order by
t.object_id,
i.index_id,
is_included_column,
c.column_id
Thanks
Shiju Samuel
Hi
SELECT object_name(a.[object_id]) as TableName,a.index_id,
isnull(b.name,'HEAP') as IndexName, sum(a.page_count) as
pages,sum(a.page_count)*1.0/1024 as Mb
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'DETAILED')
AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id =
b.index_id
group by a.[object_id],a.index_id, b.name
order by pages desc;
"Shiju Samuel" <shiju.samuel@.gmail.com> wrote in message
news:a3495e0c-cb67-4dfd-9eca-671e8bee8e0d@.y43g2000hsy.googlegroups.com...
> Where can I get the size of the index to add to the below query.
> select
> t.name [Table],
> i.Name [Index],
> c.name [Column],
> ic.key_ordinal [Key Ordinal],
> is_included_column [Included],
> is_descending_key [Descending key],
> i.type_desc [Index Type],
> i.is_unique [Unique Index],
> i.is_disabled [Index disabled],
> i.fill_factor [Fill Factor],
> i.is_hypothetical [hypothetical]
> from
> sys.indexes i
> inner join sys.index_columns ic
> on i.index_id = ic.index_id
> and i.object_id = ic.object_id
> inner join sys.columns c
> on c.column_id = ic.column_id
> and c.object_id = ic.object_id
> inner join sys.tables t
> on i.object_id = t.object_id
> where t.name='Orders'
> order by
> t.object_id,
> i.index_id,
> is_included_column,
> c.column_id
> Thanks
> Shiju Samuel
|||Thanks Uri.

Index size.

Where can I get the size of the index to add to the below query.
select
t.name [Table],
i.Name [Index],
c.name [Column],
ic.key_ordinal [Key Ordinal],
is_included_column [Included],
is_descending_key [Descending key],
i.type_desc [Index Type],
i.is_unique [Unique Index],
i.is_disabled [Index disabled],
i.fill_factor [Fill Factor],
i.is_hypothetical [hypothetical]
from
sys.indexes i
inner join sys.index_columns ic
on i.index_id = ic.index_id
and i.object_id = ic.object_id
inner join sys.columns c
on c.column_id = ic.column_id
and c.object_id = ic.object_id
inner join sys.tables t
on i.object_id = t.object_id
where t.name='Orders'
order by
t.object_id,
i.index_id,
is_included_column,
c.column_id
Thanks
Shiju SamuelHi
SELECT object_name(a.[object_id]) as TableName,a.index_id,
isnull(b.name,'HEAP') as IndexName, sum(a.page_count) as
pages,sum(a.page_count)*1.0/1024 as Mb
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'DETAILED')
AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id =
b.index_id
group by a.[object_id],a.index_id, b.name
order by pages desc;
"Shiju Samuel" <shiju.samuel@.gmail.com> wrote in message
news:a3495e0c-cb67-4dfd-9eca-671e8bee8e0d@.y43g2000hsy.googlegroups.com...
> Where can I get the size of the index to add to the below query.
> select
> t.name [Table],
> i.Name [Index],
> c.name [Column],
> ic.key_ordinal [Key Ordinal],
> is_included_column [Included],
> is_descending_key [Descending key],
> i.type_desc [Index Type],
> i.is_unique [Unique Index],
> i.is_disabled [Index disabled],
> i.fill_factor [Fill Factor],
> i.is_hypothetical [hypothetical]
> from
> sys.indexes i
> inner join sys.index_columns ic
> on i.index_id = ic.index_id
> and i.object_id = ic.object_id
> inner join sys.columns c
> on c.column_id = ic.column_id
> and c.object_id = ic.object_id
> inner join sys.tables t
> on i.object_id = t.object_id
> where t.name='Orders'
> order by
> t.object_id,
> i.index_id,
> is_included_column,
> c.column_id
> Thanks
> Shiju Samuel|||Thanks Uri.sql

Index size.

Where can I get the size of the index to add to the below query.
select
t.name [Table],
i.Name [Index],
c.name [Column],
ic.key_ordinal [Key Ordinal],
is_included_column [Included],
is_descending_key [Descending key],
i.type_desc [Index Type],
i.is_unique [Unique Index],
i.is_disabled [Index disabled],
i.fill_factor [Fill Factor],
i.is_hypothetical [hypothetical]
from
sys.indexes i
inner join sys.index_columns ic
on i.index_id = ic.index_id
and i.object_id = ic.object_id
inner join sys.columns c
on c.column_id = ic.column_id
and c.object_id = ic.object_id
inner join sys.tables t
on i.object_id = t.object_id
where t.name='Orders'
order by
t.object_id,
i.index_id,
is_included_column,
c.column_id
Thanks
Shiju SamuelHi
SELECT object_name(a.[object_id]) as TableName,a.index_id,
isnull(b.name,'HEAP') as IndexName, sum(a.page_count) as
pages,sum(a.page_count)*1.0/1024 as Mb
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'DETAILED')
AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id =b.index_id
group by a.[object_id],a.index_id, b.name
order by pages desc;
"Shiju Samuel" <shiju.samuel@.gmail.com> wrote in message
news:a3495e0c-cb67-4dfd-9eca-671e8bee8e0d@.y43g2000hsy.googlegroups.com...
> Where can I get the size of the index to add to the below query.
> select
> t.name [Table],
> i.Name [Index],
> c.name [Column],
> ic.key_ordinal [Key Ordinal],
> is_included_column [Included],
> is_descending_key [Descending key],
> i.type_desc [Index Type],
> i.is_unique [Unique Index],
> i.is_disabled [Index disabled],
> i.fill_factor [Fill Factor],
> i.is_hypothetical [hypothetical]
> from
> sys.indexes i
> inner join sys.index_columns ic
> on i.index_id = ic.index_id
> and i.object_id = ic.object_id
> inner join sys.columns c
> on c.column_id = ic.column_id
> and c.object_id = ic.object_id
> inner join sys.tables t
> on i.object_id = t.object_id
> where t.name='Orders'
> order by
> t.object_id,
> i.index_id,
> is_included_column,
> c.column_id
> Thanks
> Shiju Samuel|||Thanks Uri.

index question

Hi,
The below query is running slow in production.
SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
PHONE_ID (IX_CALL_TB)
The execution plan shows:
SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
|--Stream Aggregate(DEFINE[Expr1002]=MIN([CALL_TB].[STMT_DATE])))
|--Bookmark Lookup(BOOKMARK[Bmk1000]),
OBJECT[CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
|--Index Seek(OBJECT[CALLDB].[dbo].[CALL_TB].[IX_CALL_TB]),
SEEK[CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmark
Lookup takes about 9.82. Overall select statement takes estimated cost about
9.83
To reduce the estimated cost, I have modifed the non-clustered index on
PHONE_ID to include the column STMT_DATE. In order words, I made it as
composite index. First column in the index is PHONE_ID and second column is
STMT_DATE.
Since I made it as covering index, the query estimated cost took about
0.008, which is a significant improvement. I have a question here...Does
this change affects any other query to run slow? I mean, for example if there
is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
Thanks,
Ramu
Adding the second column to the index will not visibly change the
performance when only the first column is used. The index tree will
be a tiny bit deeper but that will not amount to anything worth
worrying about.
Roy Harvey
Beacon Falls, CT
On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:

>Hi,
>The below query is running slow in production.
>SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
>CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
>non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
>PHONE_ID (IX_CALL_TB)
>The execution plan shows:
>SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
> |--Stream Aggregate(DEFINE[Expr1002]=MIN([CALL_TB].[STMT_DATE])))
> |--Bookmark Lookup(BOOKMARK[Bmk1000]),
>OBJECT[CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
> |--Index Seek(OBJECT[CALLDB].[dbo].[CALL_TB].[IX_CALL_TB]),
>SEEK[CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
>
>Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmark
>Lookup takes about 9.82. Overall select statement takes estimated cost about
>9.83
>To reduce the estimated cost, I have modifed the non-clustered index on
>PHONE_ID to include the column STMT_DATE. In order words, I made it as
>composite index. First column in the index is PHONE_ID and second column is
>STMT_DATE.
>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if there
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
>Thanks,
>Ramu
|||On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:

>The below query is running slow in production.
What do you mean, "slow"?

>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if there
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
So it runs about 10% faster? OK.
But it was already pretty efficient, as I presume phone_id is already
highly selective. The covering index means it can all be done in the
index instead of scanning the data. This might be more significant as
your database size grows, if it's not already fully populated.
Yes, it will cause some other queries to run a percent or three slower
because a few more pages of index will be needed, with the fatter
two-field key.
J.

index question

Hi,
The below query is running slow in production.
SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
PHONE_ID (IX_CALL_TB)
The execution plan shows:
SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
|--Stream Aggregate(DEFINE[Expr1002]=MIN([CALL_TB].[STMT_DATE]
)))
|--Bookmark Lookup(BOOKMARK[Bmk1000]),
OBJECT[CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
|--Index Seek(OBJECT[CALLDB].[dbo].[CALL_TB].[IX_CALL_TB])
,
SEEK[CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmark
Lookup takes about 9.82. Overall select statement takes estimated cost about
9.83
To reduce the estimated cost, I have modifed the non-clustered index on
PHONE_ID to include the column STMT_DATE. In order words, I made it as
composite index. First column in the index is PHONE_ID and second column is
STMT_DATE.
Since I made it as covering index, the query estimated cost took about
0.008, which is a significant improvement. I have a question here...Does
this change affects any other query to run slow? I mean, for example if ther
e
is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
Thanks,
RamuAdding the second column to the index will not visibly change the
performance when only the first column is used. The index tree will
be a tiny bit deeper but that will not amount to anything worth
worrying about.
Roy Harvey
Beacon Falls, CT
On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:

>Hi,
>The below query is running slow in production.
>SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
>CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
>non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
>PHONE_ID (IX_CALL_TB)
>The execution plan shows:
>SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
> |--Stream Aggregate(DEFINE[Expr1002]=MIN([CALL_TB].[STMT_DA
TE])))
> |--Bookmark Lookup(BOOKMARK[Bmk1000]),
>OBJECT[CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
> |--Index Seek(OBJECT[CALLDB].[dbo].[CALL_TB].[
;IX_CALL_TB]),
>SEEK[CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
>
>Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmar
k
>Lookup takes about 9.82. Overall select statement takes estimated cost abou
t
>9.83
>To reduce the estimated cost, I have modifed the non-clustered index on
>PHONE_ID to include the column STMT_DATE. In order words, I made it as
>composite index. First column in the index is PHONE_ID and second column is
>STMT_DATE.
>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if the
re
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
>Thanks,
>Ramu|||On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:

>The below query is running slow in production.
What do you mean, "slow"?

>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if the
re
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
So it runs about 10% faster? OK.
But it was already pretty efficient, as I presume phone_id is already
highly selective. The covering index means it can all be done in the
index instead of scanning the data. This might be more significant as
your database size grows, if it's not already fully populated.
Yes, it will cause some other queries to run a percent or three slower
because a few more pages of index will be needed, with the fatter
two-field key.
J.

index question

Hi,
The below query is running slow in production.
SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
PHONE_ID (IX_CALL_TB)
The execution plan shows:
SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
|--Stream Aggregate(DEFINE:([Expr1002]=MIN([CALL_TB].[STMT_DATE])))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
|--Index Seek(OBJECT:([CALLDB].[dbo].[CALL_TB].[IX_CALL_TB]),
SEEK:([CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmark
Lookup takes about 9.82. Overall select statement takes estimated cost about
9.83
To reduce the estimated cost, I have modifed the non-clustered index on
PHONE_ID to include the column STMT_DATE. In order words, I made it as
composite index. First column in the index is PHONE_ID and second column is
STMT_DATE.
Since I made it as covering index, the query estimated cost took about
0.008, which is a significant improvement. I have a question here...Does
this change affects any other query to run slow? I mean, for example if there
is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
Thanks,
RamuAdding the second column to the index will not visibly change the
performance when only the first column is used. The index tree will
be a tiny bit deeper but that will not amount to anything worth
worrying about.
Roy Harvey
Beacon Falls, CT
On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:
>Hi,
>The below query is running slow in production.
>SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
>CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
>non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
>PHONE_ID (IX_CALL_TB)
>The execution plan shows:
>SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
> |--Stream Aggregate(DEFINE:([Expr1002]=MIN([CALL_TB].[STMT_DATE])))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
>OBJECT:([CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
> |--Index Seek(OBJECT:([CALLDB].[dbo].[CALL_TB].[IX_CALL_TB]),
>SEEK:([CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
>
>Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmark
>Lookup takes about 9.82. Overall select statement takes estimated cost about
>9.83
>To reduce the estimated cost, I have modifed the non-clustered index on
>PHONE_ID to include the column STMT_DATE. In order words, I made it as
>composite index. First column in the index is PHONE_ID and second column is
>STMT_DATE.
>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if there
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
>Thanks,
>Ramu|||On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:
>The below query is running slow in production.
What do you mean, "slow"?
>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if there
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
So it runs about 10% faster? OK.
But it was already pretty efficient, as I presume phone_id is already
highly selective. The covering index means it can all be done in the
index instead of scanning the data. This might be more significant as
your database size grows, if it's not already fully populated.
Yes, it will cause some other queries to run a percent or three slower
because a few more pages of index will be needed, with the fatter
two-field key.
J.

Index of length xx exceeds the maximum length

Hi,
my program inserts data into a table and it's throwing an exception (below).
I'm not a SQL Server bod so can anyone tell me if there is an alter statemen
t
to increase the size allocated to the index or otherwise what I should do.
The index entry of length 1235 bytes for the index 'ixPDM_ActionDescription'
exceeds the maximum length of 900 bytes.Nope, 900 is max, maybe you should look at Full Text Search if you need
bigger sizes than 900 bytes
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||The maximum length of any index is 900 bytes per row. There's no way around
that. When I say "no way", I'm not entirely truthful - in SQL 2005
'nonessential' columns (i.e. not part of a constraint enforced by the index)
that would cause the index to be larger than 900 bytes (and thus preventing
data from being inserted) can still be included in the index.
Look up INCLUDE in Books Online.
In SQL 2000 you'd have to re-think the fact that you need indexes on columns
larger than 900 bytes.
What version are you using? What's the table DDL? What's the business
requirement? Why do these large columns need to be indexed?
ML
http://milambda.blogspot.com/|||Thank you for your replies. I think I might just drop the index as to my
knowledge I don't think the field is used in a join anywhere just the creato
r
of the db went a bit over the top with indexing.
"ML" wrote:

> The maximum length of any index is 900 bytes per row. There's no way aroun
d
> that. When I say "no way", I'm not entirely truthful - in SQL 2005
> 'nonessential' columns (i.e. not part of a constraint enforced by the inde
x)
> that would cause the index to be larger than 900 bytes (and thus preventin
g
> data from being inserted) can still be included in the index.
> Look up INCLUDE in Books Online.
> In SQL 2000 you'd have to re-think the fact that you need indexes on colum
ns
> larger than 900 bytes.
> What version are you using? What's the table DDL? What's the business
> requirement? Why do these large columns need to be indexed?
>
> ML
> --
> http://milambda.blogspot.com/

index hints on deletes

Can I not use index hints on delete statements as below ? Using SQL 2005
delete
from dbo.table1 WITH (index(idx_test))
where col1 <= 5
Are you getting an error? If so it would be nice to know what.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
> Can I not use index hints on delete statements as below ? Using SQL 2005
> delete from dbo.table1 WITH (index(idx_test))
> where col1 <= 5
|||Msg 1069, Level 15, State 1, Line 3
Index hints are only allowed in a FROM clause.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Are you getting an error? If so it would be nice to know what.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>
|||How about this:
delete a
from dbo.table1 AS a WITH (index(idx_test))
where a.col1 <= 5
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> Msg 1069, Level 15, State 1, Line 3
> Index hints are only allowed in a FROM clause.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
>
|||or just
DELETE table1 from table1 with(index(idx_test))
WHERE col <= 5
But as far as I can tell from the BOL syntax description, the original
DELETE statement is perfectly legal. Is this some kind of bug?
Linchi
"Andrew J. Kelly" wrote:

> How about this:
> delete a
> from dbo.table1 AS a WITH (index(idx_test))
> where a.col1 <= 5
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>
|||What I'm reading is that DELETE only takes hints from a limited list
<table_hint_limited> and index hints are not in that list.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3E750688-DD9F-4567-BD65-515160B60119@.microsoft.com...[vbcol=seagreen]
> or just
> DELETE table1 from table1 with(index(idx_test))
> WHERE col <= 5
> But as far as I can tell from the BOL syntax description, the original
> DELETE statement is perfectly legal. Is this some kind of bug?
> Linchi
> "Andrew J. Kelly" wrote:

index hints on deletes

Can I not use index hints on delete statements as below ? Using SQL 2005
delete
from dbo.table1 WITH (index(idx_test))
where col1 <= 5Are you getting an error? If so it would be nice to know what.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
> Can I not use index hints on delete statements as below ? Using SQL 2005
> delete from dbo.table1 WITH (index(idx_test))
> where col1 <= 5|||Msg 1069, Level 15, State 1, Line 3
Index hints are only allowed in a FROM clause.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Are you getting an error? If so it would be nice to know what.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>> Can I not use index hints on delete statements as below ? Using SQL 2005
>> delete from dbo.table1 WITH (index(idx_test))
>> where col1 <= 5
>|||How about this:
delete a
from dbo.table1 AS a WITH (index(idx_test))
where a.col1 <= 5
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> Msg 1069, Level 15, State 1, Line 3
> Index hints are only allowed in a FROM clause.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> Are you getting an error? If so it would be nice to know what.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>> Can I not use index hints on delete statements as below ? Using SQL 2005
>> delete from dbo.table1 WITH (index(idx_test))
>> where col1 <= 5
>|||or just
DELETE table1 from table1 with(index(idx_test))
WHERE col <= 5
But as far as I can tell from the BOL syntax description, the original
DELETE statement is perfectly legal. Is this some kind of bug?
Linchi
"Andrew J. Kelly" wrote:
> How about this:
> delete a
> from dbo.table1 AS a WITH (index(idx_test))
> where a.col1 <= 5
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> > Msg 1069, Level 15, State 1, Line 3
> > Index hints are only allowed in a FROM clause.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> >> Are you getting an error? If so it would be nice to know what.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Hassan" <hassan@.test.com> wrote in message
> >> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
> >> Can I not use index hints on delete statements as below ? Using SQL 2005
> >>
> >> delete from dbo.table1 WITH (index(idx_test))
> >> where col1 <= 5
> >>
> >
>|||What I'm reading is that DELETE only takes hints from a limited list
<table_hint_limited> and index hints are not in that list.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3E750688-DD9F-4567-BD65-515160B60119@.microsoft.com...
> or just
> DELETE table1 from table1 with(index(idx_test))
> WHERE col <= 5
> But as far as I can tell from the BOL syntax description, the original
> DELETE statement is perfectly legal. Is this some kind of bug?
> Linchi
> "Andrew J. Kelly" wrote:
>> How about this:
>> delete a
>> from dbo.table1 AS a WITH (index(idx_test))
>> where a.col1 <= 5
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>> > Msg 1069, Level 15, State 1, Line 3
>> > Index hints are only allowed in a FROM clause.
>> >
>> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> > news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> >> Are you getting an error? If so it would be nice to know what.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >>
>> >>
>> >> "Hassan" <hassan@.test.com> wrote in message
>> >> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>> >> Can I not use index hints on delete statements as below ? Using SQL
>> >> 2005
>> >>
>> >> delete from dbo.table1 WITH (index(idx_test))
>> >> where col1 <= 5
>> >>
>> >
>>

index error?

Hi,
We had a query return the index entry error below.
Could not find the index entry for RID '36020100000200000100100007410400' in
index page (1:2223497), index ID 0, database 'dpSUM001'..
I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
planned to run a rebuild index on the table later that night to fix the
problem. Later in the afternoon I ran the query again and this time it
returned data and didn't have the index entry error. I ran the DBCC
CHECKTABLE and it reported no errors.
Does anyone know what fixed the index errors? The database has the auto
update/create stats and torn page detection turned on. Could one of these
option fixed the errors?
Thanks Richard
No idea. Thats some thing new and good for you. But from your side make sure
that you execute DBCC CHECKDB regularly and ensure that database
is in good shape. One more thing is talk to your hardware (DISK) vendor and
do some checks in DISK side during non peak time.
Thanks
Hari
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:789E3859-17AA-464A-B4FF-C30FDD6C3522@.microsoft.com...
> Hi,
> We had a query return the index entry error below.
> Could not find the index entry for RID '36020100000200000100100007410400'
> in
> index page (1:2223497), index ID 0, database 'dpSUM001'..
> I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
> planned to run a rebuild index on the table later that night to fix the
> problem. Later in the afternoon I ran the query again and this time it
> returned data and didn't have the index entry error. I ran the DBCC
> CHECKTABLE and it reported no errors.
> Does anyone know what fixed the index errors? The database has the auto
> update/create stats and torn page detection turned on. Could one of these
> option fixed the errors?
> Thanks Richard
>

index error?

Hi,
We had a query return the index entry error below.
Could not find the index entry for RID '36020100000200000100100007410400' in
index page (1:2223497), index ID 0, database 'dpSUM001'..
I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
planned to run a rebuild index on the table later that night to fix the
problem. Later in the afternoon I ran the query again and this time it
returned data and didn't have the index entry error. I ran the DBCC
CHECKTABLE and it reported no errors.
Does anyone know what fixed the index errors? The database has the auto
update/create stats and torn page detection turned on. Could one of these
option fixed the errors?
Thanks RichardNo idea. Thats some thing new and good for you. But from your side make sure
that you execute DBCC CHECKDB regularly and ensure that database
is in good shape. One more thing is talk to your hardware (DISK) vendor and
do some checks in DISK side during non peak time.
Thanks
Hari
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:789E3859-17AA-464A-B4FF-C30FDD6C3522@.microsoft.com...
> Hi,
> We had a query return the index entry error below.
> Could not find the index entry for RID '36020100000200000100100007410400'
> in
> index page (1:2223497), index ID 0, database 'dpSUM001'..
> I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
> planned to run a rebuild index on the table later that night to fix the
> problem. Later in the afternoon I ran the query again and this time it
> returned data and didn't have the index entry error. I ran the DBCC
> CHECKTABLE and it reported no errors.
> Does anyone know what fixed the index errors? The database has the auto
> update/create stats and torn page detection turned on. Could one of these
> option fixed the errors?
> Thanks Richard
>

index error?

Hi,
We had a query return the index entry error below.
Could not find the index entry for RID '36020100000200000100100007410400' in
index page (1:2223497), index ID 0, database 'dpSUM001'..
I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
planned to run a rebuild index on the table later that night to fix the
problem. Later in the afternoon I ran the query again and this time it
returned data and didn't have the index entry error. I ran the DBCC
CHECKTABLE and it reported no errors.
Does anyone know what fixed the index errors? The database has the auto
update/create stats and torn page detection turned on. Could one of these
option fixed the errors?
Thanks RichardNo idea. Thats some thing new and good for you. But from your side make sure
that you execute DBCC CHECKDB regularly and ensure that database
is in good shape. One more thing is talk to your hardware (DISK) vendor and
do some checks in DISK side during non peak time.
Thanks
Hari
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:789E3859-17AA-464A-B4FF-C30FDD6C3522@.microsoft.com...
> Hi,
> We had a query return the index entry error below.
> Could not find the index entry for RID '36020100000200000100100007410400'
> in
> index page (1:2223497), index ID 0, database 'dpSUM001'..
> I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
> planned to run a rebuild index on the table later that night to fix the
> problem. Later in the afternoon I ran the query again and this time it
> returned data and didn't have the index entry error. I ran the DBCC
> CHECKTABLE and it reported no errors.
> Does anyone know what fixed the index errors? The database has the auto
> update/create stats and torn page detection turned on. Could one of these
> option fixed the errors?
> Thanks Richard
>

Index Error from Database Integrity Job

Listed below is an error that I received from my SQL Server 2000 database
maintenance plan from the database integrity check job.
After receiving this error, I applied the following parameters to rebuild
all of the table indexes.
I received the same error after rebuilding the table indexes from the
parameter listed below.
DBCC DBREINDEX (MarketDeals, '', 0)
Please help me resolve this error.
Thanks,
Error Messages:
[Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed to
by the previous pointer of IAM page (1:8596228) object ID 781961862 index ID
3 but was not detected in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed to
by the previous pointer of IAM page (1:8596444) object ID 781961862 index ID
4 but was not detected in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2 allocation
errors and 0 consistency errors in table 'MarketDeals'
You seem to have a corruption in your database.
If you are lucky, you can just drop the corrupted index and then find out why this happened in the
first place (HW errors probably, so you need to fix that).
Warm up the tape machines, since this might mean that you need to do a restore. First, run DBCC
CHECKDB using the NO_INFOMSGS option to get error numbers back that you can search for in Books
Online. Then search for those error numbers. Make sure that you have the most recent update of Books
Online as it contains specific recommendations for each corruption-type of error number. Also, see:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E79AFF38-33A0-474B-A82D-F3FE62353ECC@.microsoft.com...
> Listed below is an error that I received from my SQL Server 2000 database
> maintenance plan from the database integrity check job.
> After receiving this error, I applied the following parameters to rebuild
> all of the table indexes.
> I received the same error after rebuilding the table indexes from the
> parameter listed below.
> DBCC DBREINDEX (MarketDeals, '', 0)
> Please help me resolve this error.
> Thanks,
>
> Error Messages:
> [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed to
> by the previous pointer of IAM page (1:8596228) object ID 781961862 index ID
> 3 but was not detected in the scan.
> [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed to
> by the previous pointer of IAM page (1:8596444) object ID 781961862 index ID
> 4 but was not detected in the scan.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2 allocation
> errors and 0 consistency errors in table 'MarketDeals'
|||Joe
I suspect your database gets corrupted
Run DBCC CHECKDB to repair the data
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E79AFF38-33A0-474B-A82D-F3FE62353ECC@.microsoft.com...
> Listed below is an error that I received from my SQL Server 2000 database
> maintenance plan from the database integrity check job.
> After receiving this error, I applied the following parameters to rebuild
> all of the table indexes.
> I received the same error after rebuilding the table indexes from the
> parameter listed below.
> DBCC DBREINDEX (MarketDeals, '', 0)
> Please help me resolve this error.
> Thanks,
>
> Error Messages:
> [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed
to
> by the previous pointer of IAM page (1:8596228) object ID 781961862 index
ID
> 3 but was not detected in the scan.
> [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed
to
> by the previous pointer of IAM page (1:8596444) object ID 781961862 index
ID
> 4 but was not detected in the scan.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2 allocation
> errors and 0 consistency errors in table 'MarketDeals'
|||You shouldn't ever run repair to correct corruptions without first working
out why the corruption occured and preferably restoring from a backup.
Repair fixes the database structures but will most likely break any
application logic inherent in the data stored in the database (as it may
have to delete records or pages to restore consistency).
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#pykSmLUFHA.2172@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Joe
> I suspect your database gets corrupted
> Run DBCC CHECKDB to repair the data
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:E79AFF38-33A0-474B-A82D-F3FE62353ECC@.microsoft.com...
database[vbcol=seagreen]
rebuild[vbcol=seagreen]
> to
index[vbcol=seagreen]
> ID
> to
index[vbcol=seagreen]
> ID
allocation
>
|||Paul
Yes, I agree that the OP should investigate why it has happened but this
commnad provides three methods of repair. My mistake was that I did not
mention to what repair to be more useful
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eeWU%23IPUFHA.544@.TK2MSFTNGP15.phx.gbl...
> You shouldn't ever run repair to correct corruptions without first working
> out why the corruption occured and preferably restoring from a backup.
> Repair fixes the database structures but will most likely break any
> application logic inherent in the data stored in the database (as it may
> have to delete records or pages to restore consistency).
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#pykSmLUFHA.2172@.tk2msftngp13.phx.gbl...
> database
> rebuild
pointed[vbcol=seagreen]
> index
pointed
> index
> allocation
>

Index Error from Database Integrity Job

Listed below is an error that I received from my SQL Server 2000 database
maintenance plan from the database integrity check job.
After receiving this error, I applied the following parameters to rebuild
all of the table indexes.
I received the same error after rebuilding the table indexes from the
parameter listed below.
DBCC DBREINDEX (MarketDeals, '', 0)
Please help me resolve this error.
Thanks,
Error Messages:
[Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is
pointed to
by the previous pointer of IAM page (1:8596228) object ID 781961862 index ID
3 but was not detected in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is
pointed to
by the previous pointer of IAM page (1:8596444) object ID 781961862 index ID
4 but was not detected in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2 a
llocation
errors and 0 consistency errors in table 'MarketDeals'You seem to have a corruption in your database.
If you are lucky, you can just drop the corrupted index and then find out wh
y this happened in the
first place (HW errors probably, so you need to fix that).
Warm up the tape machines, since this might mean that you need to do a resto
re. First, run DBCC
CHECKDB using the NO_INFOMSGS option to get error numbers back that you can
search for in Books
Online. Then search for those error numbers. Make sure that you have the mos
t recent update of Books
Online as it contains specific recommendations for each corruption-type of e
rror number. Also, see:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E79AFF38-33A0-474B-A82D-F3FE62353ECC@.microsoft.com...
> Listed below is an error that I received from my SQL Server 2000 database
> maintenance plan from the database integrity check job.
> After receiving this error, I applied the following parameters to rebuild
> all of the table indexes.
> I received the same error after rebuilding the table indexes from the
> parameter listed below.
> DBCC DBREINDEX (MarketDeals, '', 0)
> Please help me resolve this error.
> Thanks,
>
> Error Messages:
> [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0)
is pointed to
> by the previous pointer of IAM page (1:8596228) object ID 781961862 index
ID
> 3 but was not detected in the scan.
> [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0)
is pointed to
> by the previous pointer of IAM page (1:8596444) object ID 781961862 index
ID
> 4 but was not detected in the scan.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2
allocation
> errors and 0 consistency errors in table 'MarketDeals'|||Joe
I suspect your database gets corrupted
Run DBCC CHECKDB to repair the data
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E79AFF38-33A0-474B-A82D-F3FE62353ECC@.microsoft.com...
> Listed below is an error that I received from my SQL Server 2000 database
> maintenance plan from the database integrity check job.
> After receiving this error, I applied the following parameters to rebuild
> all of the table indexes.
> I received the same error after rebuilding the table indexes from the
> parameter listed below.
> DBCC DBREINDEX (MarketDeals, '', 0)
> Please help me resolve this error.
> Thanks,
>
> Error Messages:
> [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is point
ed
to
> by the previous pointer of IAM page (1:8596228) object ID 781961862 index
ID
> 3 but was not detected in the scan.
> [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is point
ed
to
> by the previous pointer of IAM page (1:8596444) object ID 781961862 index
ID
> 4 but was not detected in the scan.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2
allocation
> errors and 0 consistency errors in table 'MarketDeals'|||You shouldn't ever run repair to correct corruptions without first working
out why the corruption occured and preferably restoring from a backup.
Repair fixes the database structures but will most likely break any
application logic inherent in the data stored in the database (as it may
have to delete records or pages to restore consistency).
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#pykSmLUFHA.2172@.tk2msftngp13.phx.gbl...
> Joe
> I suspect your database gets corrupted
> Run DBCC CHECKDB to repair the data
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:E79AFF38-33A0-474B-A82D-F3FE62353ECC@.microsoft.com...
database[vbcol=seagreen]
rebuild[vbcol=seagreen]
> to
index[vbcol=seagreen]
> ID
> to
index[vbcol=seagreen]
> ID
allocation[vbcol=seagreen]
>|||Paul
Yes, I agree that the OP should investigate why it has happened but this
commnad provides three methods of repair. My mistake was that I did not
mention to what repair to be more useful
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eeWU%23IPUFHA.544@.TK2MSFTNGP15.phx.gbl...
> You shouldn't ever run repair to correct corruptions without first working
> out why the corruption occured and preferably restoring from a backup.
> Repair fixes the database structures but will most likely break any
> application logic inherent in the data stored in the database (as it may
> have to delete records or pages to restore consistency).
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#pykSmLUFHA.2172@.tk2msftngp13.phx.gbl...
> database
> rebuild
pointed[vbcol=seagreen]
> index
pointed[vbcol=seagreen]
> index
> allocation
>

Index Error from Database Integrity Job

Listed below is an error that I received from my SQL Server 2000 database
maintenance plan from the database integrity check job.
After receiving this error, I applied the following parameters to rebuild
all of the table indexes.
I received the same error after rebuilding the table indexes from the
parameter listed below.
DBCC DBREINDEX (MarketDeals, '', 0)
Please help me resolve this error.
Thanks,
Error Messages:
[Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed to
by the previous pointer of IAM page (1:8596228) object ID 781961862 index ID
3 but was not detected in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed to
by the previous pointer of IAM page (1:8596444) object ID 781961862 index ID
4 but was not detected in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2 allocation
errors and 0 consistency errors in table 'MarketDeals'You seem to have a corruption in your database.
If you are lucky, you can just drop the corrupted index and then find out why this happened in the
first place (HW errors probably, so you need to fix that).
Warm up the tape machines, since this might mean that you need to do a restore. First, run DBCC
CHECKDB using the NO_INFOMSGS option to get error numbers back that you can search for in Books
Online. Then search for those error numbers. Make sure that you have the most recent update of Books
Online as it contains specific recommendations for each corruption-type of error number. Also, see:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E79AFF38-33A0-474B-A82D-F3FE62353ECC@.microsoft.com...
> Listed below is an error that I received from my SQL Server 2000 database
> maintenance plan from the database integrity check job.
> After receiving this error, I applied the following parameters to rebuild
> all of the table indexes.
> I received the same error after rebuilding the table indexes from the
> parameter listed below.
> DBCC DBREINDEX (MarketDeals, '', 0)
> Please help me resolve this error.
> Thanks,
>
> Error Messages:
> [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed to
> by the previous pointer of IAM page (1:8596228) object ID 781961862 index ID
> 3 but was not detected in the scan.
> [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed to
> by the previous pointer of IAM page (1:8596444) object ID 781961862 index ID
> 4 but was not detected in the scan.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2 allocation
> errors and 0 consistency errors in table 'MarketDeals'|||Joe
I suspect your database gets corrupted
Run DBCC CHECKDB to repair the data
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E79AFF38-33A0-474B-A82D-F3FE62353ECC@.microsoft.com...
> Listed below is an error that I received from my SQL Server 2000 database
> maintenance plan from the database integrity check job.
> After receiving this error, I applied the following parameters to rebuild
> all of the table indexes.
> I received the same error after rebuilding the table indexes from the
> parameter listed below.
> DBCC DBREINDEX (MarketDeals, '', 0)
> Please help me resolve this error.
> Thanks,
>
> Error Messages:
> [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed
to
> by the previous pointer of IAM page (1:8596228) object ID 781961862 index
ID
> 3 but was not detected in the scan.
> [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed
to
> by the previous pointer of IAM page (1:8596444) object ID 781961862 index
ID
> 4 but was not detected in the scan.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2 allocation
> errors and 0 consistency errors in table 'MarketDeals'|||You shouldn't ever run repair to correct corruptions without first working
out why the corruption occured and preferably restoring from a backup.
Repair fixes the database structures but will most likely break any
application logic inherent in the data stored in the database (as it may
have to delete records or pages to restore consistency).
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#pykSmLUFHA.2172@.tk2msftngp13.phx.gbl...
> Joe
> I suspect your database gets corrupted
> Run DBCC CHECKDB to repair the data
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:E79AFF38-33A0-474B-A82D-F3FE62353ECC@.microsoft.com...
> >
> > Listed below is an error that I received from my SQL Server 2000
database
> > maintenance plan from the database integrity check job.
> >
> > After receiving this error, I applied the following parameters to
rebuild
> > all of the table indexes.
> >
> > I received the same error after rebuilding the table indexes from the
> > parameter listed below.
> >
> > DBCC DBREINDEX (MarketDeals, '', 0)
> >
> > Please help me resolve this error.
> >
> > Thanks,
> >
> >
> >
> > Error Messages:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed
> to
> > by the previous pointer of IAM page (1:8596228) object ID 781961862
index
> ID
> > 3 but was not detected in the scan.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed
> to
> > by the previous pointer of IAM page (1:8596444) object ID 781961862
index
> ID
> > 4 but was not detected in the scan.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2
allocation
> > errors and 0 consistency errors in table 'MarketDeals'
>|||Paul
Yes, I agree that the OP should investigate why it has happened but this
commnad provides three methods of repair. My mistake was that I did not
mention to what repair to be more useful
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eeWU%23IPUFHA.544@.TK2MSFTNGP15.phx.gbl...
> You shouldn't ever run repair to correct corruptions without first working
> out why the corruption occured and preferably restoring from a backup.
> Repair fixes the database structures but will most likely break any
> application logic inherent in the data stored in the database (as it may
> have to delete records or pages to restore consistency).
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#pykSmLUFHA.2172@.tk2msftngp13.phx.gbl...
> > Joe
> > I suspect your database gets corrupted
> > Run DBCC CHECKDB to repair the data
> >
> >
> > "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> > news:E79AFF38-33A0-474B-A82D-F3FE62353ECC@.microsoft.com...
> > >
> > > Listed below is an error that I received from my SQL Server 2000
> database
> > > maintenance plan from the database integrity check job.
> > >
> > > After receiving this error, I applied the following parameters to
> rebuild
> > > all of the table indexes.
> > >
> > > I received the same error after rebuilding the table indexes from the
> > > parameter listed below.
> > >
> > > DBCC DBREINDEX (MarketDeals, '', 0)
> > >
> > > Please help me resolve this error.
> > >
> > > Thanks,
> > >
> > >
> > >
> > > Error Messages:
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is
pointed
> > to
> > > by the previous pointer of IAM page (1:8596228) object ID 781961862
> index
> > ID
> > > 3 but was not detected in the scan.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is
pointed
> > to
> > > by the previous pointer of IAM page (1:8596444) object ID 781961862
> index
> > ID
> > > 4 but was not detected in the scan.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2
> allocation
> > > errors and 0 consistency errors in table 'MarketDeals'
> >
> >
>

Index Error

What caused the below error.
"
I/O error (torn page) detected during read at offset"
Thanks ,
AnnaProbably a power failure or I/O failure.
Hope you have a current backup!
"Anna" <anna@.ccn.com.sg> wrote in message
news:070c01c3df21$8bba8f00$a101280a@.phx.gbl...
> What caused the below error.
> "
> I/O error (torn page) detected during read at offset"
> Thanks ,
> Anna

Index Error

What caused the below error.
"
I/O error (torn page) detected during read at offset"
Thanks ,
AnnaProbably a power failure or I/O failure.
Hope you have a current backup!
"Anna" <anna@.ccn.com.sg> wrote in message
news:070c01c3df21$8bba8f00$a101280a@.phx.gbl...

quote:

---

> What caused the below error.
> "
> I/O error (torn page) detected during read at offset"
> Thanks ,
> Anna

---

Index chosen is wrong index

I'm on SQL Server 2000 SP3.
The execution plan for the query below selects the wrong
index. It selects the index that has the leading column
as SDITIM, which is not even in the where clause nor the
order by clause. The index that it chose is not even the
clustered index. Any ideas on WHY? Is this a BUG?
Index Chosen in Execution Plan
====================================
CREATE INDEX [F4211_9] ON [PRODDTA].[F4211]([SDITM],
[SDMCU], [SDDRQJ]) WITH FILLFACTOR = 90 ON [SECONDARY]
GO
SQL Statement
==============
SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU,
SDOKCO, SDOORN, SDOCTO, SDRKCO,
SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8,
SDDRQJ, SDTRDJ, SDPDDJ, SDOPDJ,
SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01,
SDITM, SDLITM, SDAITM, SDLOCN,
SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU,
SDRLIT, SDRKIT, SDSRP1, SDSRP2,
SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK,
SDSOCN, SDUPRC, SDAEXP, SDUNCS,
SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCDCD,
SDCARS, SDMOT, SDZON, SDFRTH,
SDUOM4, SDSO15, SDSLSM, SDSLM2, SDSWMS, SDCRCD, SDCRR,
SDFUP, SDFEA, SDFUC, SDTORG
FROM PRODDTA.F4211 (NOLOCK) WHERE ( SDLTTR >= '520' AND
SDDCTO = 'S2' AND
SDLTTR <= '999'
AND SDMCU = ' 9500' )
UNION SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO,
SDMCU, SDOKCO,
SDOORN, SDOCTO, SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT,
SDAN8, SDSHAN, SDPA8, SDDRQJ,
SDTRDJ, SDPDDJ, SDOPDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL,
SDRSDJ, SDPEFJ, SDVR01, SDITM,
SDLITM, SDAITM, SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY,
SDNXTR, SDLTTR, SDEMCU, SDRLIT,
SDRKIT, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM,
SDUORG, SDSOQS, SDSOBK, SDSOCN,
SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT,
SDPSN, SDDELN, SDCDCD, SDCARS,
SDMOT, SDZON, SDFRTH, SDUOM4, SDSO15, SDSLSM, SDSLM2,
SDSWMS, SDCRCD, SDCRR, SDFUP,
SDFEA, SDFUC, SDTORG FROM PRODDTA.F42119 (NOLOCK)
WHERE ( SDLTTR >= '520' AND SDDCTO = 'S2' AND SDLTTR
<= '999' AND
SDMCU = ' 9500')
It's difficult to say without the other relevant DDL. I would guess that
you don't have other indexes the optimizer considers beneficial here but
some of the other columns in your predicates are part of a clustered index.
In this case, the optimizer might choose a scan of narrow non-clustered
index to retrieve values of clustered index keys instead of a clustered
index (table) scan.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jean Bertrand" <jean.bertrand@.sbcglobal.net> wrote in message
news:077301c46e59$ffe7c2e0$a401280a@.phx.gbl...
> I'm on SQL Server 2000 SP3.
> The execution plan for the query below selects the wrong
> index. It selects the index that has the leading column
> as SDITIM, which is not even in the where clause nor the
> order by clause. The index that it chose is not even the
> clustered index. Any ideas on WHY? Is this a BUG?
>
> Index Chosen in Execution Plan
> ====================================
> CREATE INDEX [F4211_9] ON [PRODDTA].[F4211]([SDITM],
> [SDMCU], [SDDRQJ]) WITH FILLFACTOR = 90 ON [SECONDARY]
> GO
> SQL Statement
> ==============
> SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU,
> SDOKCO, SDOORN, SDOCTO, SDRKCO,
> SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8,
> SDDRQJ, SDTRDJ, SDPDDJ, SDOPDJ,
> SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01,
> SDITM, SDLITM, SDAITM, SDLOCN,
> SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU,
> SDRLIT, SDRKIT, SDSRP1, SDSRP2,
> SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK,
> SDSOCN, SDUPRC, SDAEXP, SDUNCS,
> SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCDCD,
> SDCARS, SDMOT, SDZON, SDFRTH,
> SDUOM4, SDSO15, SDSLSM, SDSLM2, SDSWMS, SDCRCD, SDCRR,
> SDFUP, SDFEA, SDFUC, SDTORG
> FROM PRODDTA.F4211 (NOLOCK) WHERE ( SDLTTR >= '520' AND
> SDDCTO = 'S2' AND
> SDLTTR <= '999'
> AND SDMCU = ' 9500' )
> UNION SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO,
> SDMCU, SDOKCO,
> SDOORN, SDOCTO, SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT,
> SDAN8, SDSHAN, SDPA8, SDDRQJ,
> SDTRDJ, SDPDDJ, SDOPDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL,
> SDRSDJ, SDPEFJ, SDVR01, SDITM,
> SDLITM, SDAITM, SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY,
> SDNXTR, SDLTTR, SDEMCU, SDRLIT,
> SDRKIT, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM,
> SDUORG, SDSOQS, SDSOBK, SDSOCN,
> SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT,
> SDPSN, SDDELN, SDCDCD, SDCARS,
> SDMOT, SDZON, SDFRTH, SDUOM4, SDSO15, SDSLSM, SDSLM2,
> SDSWMS, SDCRCD, SDCRR, SDFUP,
> SDFEA, SDFUC, SDTORG FROM PRODDTA.F42119 (NOLOCK)
> WHERE ( SDLTTR >= '520' AND SDDCTO = 'S2' AND SDLTTR
> <= '999' AND
> SDMCU = ' 9500')
|||Looks fine to me. SDMCU is in the WHERE clause and it is one of the SARGS
where you use an =. If the selectivity of this is low enough (and the
optimizer thinks it is) it can find the rows that match the value of '9500'
and just filter for the other sargs. Just a note, are the columns SDLTTR
and SDMCU really characters or integers?
Andrew J. Kelly SQL MVP
"Jean Bertrand" <jean.bertrand@.sbcglobal.net> wrote in message
news:077301c46e59$ffe7c2e0$a401280a@.phx.gbl...
> I'm on SQL Server 2000 SP3.
> The execution plan for the query below selects the wrong
> index. It selects the index that has the leading column
> as SDITIM, which is not even in the where clause nor the
> order by clause. The index that it chose is not even the
> clustered index. Any ideas on WHY? Is this a BUG?
>
> Index Chosen in Execution Plan
> ====================================
> CREATE INDEX [F4211_9] ON [PRODDTA].[F4211]([SDITM],
> [SDMCU], [SDDRQJ]) WITH FILLFACTOR = 90 ON [SECONDARY]
> GO
> SQL Statement
> ==============
> SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU,
> SDOKCO, SDOORN, SDOCTO, SDRKCO,
> SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8,
> SDDRQJ, SDTRDJ, SDPDDJ, SDOPDJ,
> SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01,
> SDITM, SDLITM, SDAITM, SDLOCN,
> SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU,
> SDRLIT, SDRKIT, SDSRP1, SDSRP2,
> SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK,
> SDSOCN, SDUPRC, SDAEXP, SDUNCS,
> SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCDCD,
> SDCARS, SDMOT, SDZON, SDFRTH,
> SDUOM4, SDSO15, SDSLSM, SDSLM2, SDSWMS, SDCRCD, SDCRR,
> SDFUP, SDFEA, SDFUC, SDTORG
> FROM PRODDTA.F4211 (NOLOCK) WHERE ( SDLTTR >= '520' AND
> SDDCTO = 'S2' AND
> SDLTTR <= '999'
> AND SDMCU = ' 9500' )
> UNION SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO,
> SDMCU, SDOKCO,
> SDOORN, SDOCTO, SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT,
> SDAN8, SDSHAN, SDPA8, SDDRQJ,
> SDTRDJ, SDPDDJ, SDOPDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL,
> SDRSDJ, SDPEFJ, SDVR01, SDITM,
> SDLITM, SDAITM, SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY,
> SDNXTR, SDLTTR, SDEMCU, SDRLIT,
> SDRKIT, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM,
> SDUORG, SDSOQS, SDSOBK, SDSOCN,
> SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT,
> SDPSN, SDDELN, SDCDCD, SDCARS,
> SDMOT, SDZON, SDFRTH, SDUOM4, SDSO15, SDSLSM, SDSLM2,
> SDSWMS, SDCRCD, SDCRR, SDFUP,
> SDFEA, SDFUC, SDTORG FROM PRODDTA.F42119 (NOLOCK)
> WHERE ( SDLTTR >= '520' AND SDDCTO = 'S2' AND SDLTTR
> <= '999' AND
> SDMCU = ' 9500')
|||Jean,
as other have suggested, you have not provided enough information to
support any conclusion. For example, do you have any unique
constraints/indexes, do you have a clustered index (if so, what is its
definition), what is the distribution of the data, etcetera.
In addition, since you are joining two resultsets, there is likely to be
a second index seek/scan, and the union can affect the query plan.
If you post more information, maybe someone can say something with more
confidence.
BTW: I assume obfuscated the query on purpose, it is really
unreadable...
Gert-Jan
Jean Bertrand wrote:
> I'm on SQL Server 2000 SP3.
> The execution plan for the query below selects the wrong
> index. It selects the index that has the leading column
> as SDITIM, which is not even in the where clause nor the
> order by clause. The index that it chose is not even the
> clustered index. Any ideas on WHY? Is this a BUG?
> Index Chosen in Execution Plan
> ====================================
> CREATE INDEX [F4211_9] ON [PRODDTA].[F4211]([SDITM],
> [SDMCU], [SDDRQJ]) WITH FILLFACTOR = 90 ON [SECONDARY]
> GO
> SQL Statement
> ==============
> SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU,
> SDOKCO, SDOORN, SDOCTO, SDRKCO,
> SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8,
> SDDRQJ, SDTRDJ, SDPDDJ, SDOPDJ,
> SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01,
> SDITM, SDLITM, SDAITM, SDLOCN,
> SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU,
> SDRLIT, SDRKIT, SDSRP1, SDSRP2,
> SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK,
> SDSOCN, SDUPRC, SDAEXP, SDUNCS,
> SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCDCD,
> SDCARS, SDMOT, SDZON, SDFRTH,
> SDUOM4, SDSO15, SDSLSM, SDSLM2, SDSWMS, SDCRCD, SDCRR,
> SDFUP, SDFEA, SDFUC, SDTORG
> FROM PRODDTA.F4211 (NOLOCK) WHERE ( SDLTTR >= '520' AND
> SDDCTO = 'S2' AND
> SDLTTR <= '999'
> AND SDMCU = ' 9500' )
> UNION SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO,
> SDMCU, SDOKCO,
> SDOORN, SDOCTO, SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT,
> SDAN8, SDSHAN, SDPA8, SDDRQJ,
> SDTRDJ, SDPDDJ, SDOPDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL,
> SDRSDJ, SDPEFJ, SDVR01, SDITM,
> SDLITM, SDAITM, SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY,
> SDNXTR, SDLTTR, SDEMCU, SDRLIT,
> SDRKIT, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM,
> SDUORG, SDSOQS, SDSOBK, SDSOCN,
> SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT,
> SDPSN, SDDELN, SDCDCD, SDCARS,
> SDMOT, SDZON, SDFRTH, SDUOM4, SDSO15, SDSLSM, SDSLM2,
> SDSWMS, SDCRCD, SDCRR, SDFUP,
> SDFEA, SDFUC, SDTORG FROM PRODDTA.F42119 (NOLOCK)
> WHERE ( SDLTTR >= '520' AND SDDCTO = 'S2' AND SDLTTR
> <= '999' AND
> SDMCU = ' 9500')
(Please reply only to the newsgroup)
|||Thanks for your response.
Please note that the query is from JD Edwards, so I have
no control over it. The data types of the columns in
predicates are truly characters, so they do match the
values. No problems there!
So, I don't understand how this could be correct if the
leading column for index F4211_9 is on the column SDITIM,
which isn't in the predicate. I would think that the
optimizer would have chosen another index where the
leading columns are in the predicate. (I didn't realize
that it could use an index when only the middle column is
in the predicate.)

>--Original Message--
>Looks fine to me. SDMCU is in the WHERE clause and it
is one of the SARGS
>where you use an =. If the selectivity of this is low
enough (and the
>optimizer thinks it is) it can find the rows that match
the value of '9500'
>and just filter for the other sargs. Just a note, are
the columns SDLTTR
>and SDMCU really characters or integers?
>--
>Andrew J. Kelly SQL MVP
>
>"Jean Bertrand" <jean.bertrand@.sbcglobal.net> wrote in
message[vbcol=seagreen]
>news:077301c46e59$ffe7c2e0$a401280a@.phx.gbl...
wrong[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
AND
>
>.
>
|||Thanks for the response, and I apologize for the lack of
detail. This is a JD Edwards query, which I have no
control over. In addition, they have lots of indexes on
this table... (They love to over index!) So including
them would be quite lengthy.
Anyway, there are other indexes that exist where the
leading column is in the predicate. For example, there is
an index on SDLTTR. Why didn't it use that one?
I don't understand why it would think the scan of an
index whose leading column isn't "filled-in" would be
more efficient than just doing a full table scan (or
using one of the other indexes in the predicate.)

>--Original Message--
>It's difficult to say without the other relevant DDL. I
would guess that
>you don't have other indexes the optimizer considers
beneficial here but
>some of the other columns in your predicates are part of
a clustered index.
>In this case, the optimizer might choose a scan of
narrow non-clustered
>index to retrieve values of clustered index keys instead
of a clustered
>index (table) scan.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Jean Bertrand" <jean.bertrand@.sbcglobal.net> wrote in
message[vbcol=seagreen]
>news:077301c46e59$ffe7c2e0$a401280a@.phx.gbl...
wrong[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
AND
>
>.
>
|||Thanks for the response.
1. There is a unique contraint/clustered index on
columns [SDDOCO], [SDDCTO], [SDKCOO], and [SDLNID]
2. I had to chuckle at your comment "I assume you
obfuscated the query on purpose, it is really
unreadable..."
The answer is "no". This is JD Edwards... I have no
control over their database nor their code.
Deciphering one of there queries is like reading a
foreign language...

>--Original Message--
>Jean,
>as other have suggested, you have not provided enough
information to
>support any conclusion. For example, do you have any
unique
>constraints/indexes, do you have a clustered index (if
so, what is its
>definition), what is the distribution of the data,
etcetera.
>In addition, since you are joining two resultsets, there
is likely to be
>a second index seek/scan, and the union can affect the
query plan.
>If you post more information, maybe someone can say
something with more
>confidence.
>BTW: I assume obfuscated the query on purpose, it is
really[vbcol=seagreen]
>unreadable...
>Gert-Jan
>
>Jean Bertrand wrote:
wrong[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
AND
>--
>(Please reply only to the newsgroup)
>.
>
|||I'm sorry but I thought the leading column in the index you posted was
SDMCU. Because of the way it wrapped I missed that it was really the second
column in the index. Is there another index where SDMCU is the first column
in the index? How many rows match the value of 9500 for SDMCU? If it's
not too many the partial index scan could still be the most efficient. You
mentioned in the other post that the first column in this index isn't filled
in. That means it is all the same value so it's pretty easy to see where
the 9500 values are in the second column of the index. Even if the number
of rows are more than would normally be used for a seek it can seek the
first matching row in the index and scan from there until it gets to the
last one. That's actually pretty efficient over scanning a whole large
table if the numbers are right.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:0f5b01c46ec6$123f8830$a601280a@.phx.gbl...[vbcol=seagreen]
> Thanks for your response.
> Please note that the query is from JD Edwards, so I have
> no control over it. The data types of the columns in
> predicates are truly characters, so they do match the
> values. No problems there!
> So, I don't understand how this could be correct if the
> leading column for index F4211_9 is on the column SDITIM,
> which isn't in the predicate. I would think that the
> optimizer would have chosen another index where the
> leading columns are in the predicate. (I didn't realize
> that it could use an index when only the middle column is
> in the predicate.)
> is one of the SARGS
> enough (and the
> the value of '9500'
> the columns SDLTTR
> message
> wrong
> column
> the
> the
> AND
|||jean.bertrand@.sbcglobal.net wrote:
> Thanks for the response.
> 1. There is a unique contraint/clustered index on
> columns [SDDOCO], [SDDCTO], [SDKCOO], and [SDLNID]
> 2. I had to chuckle at your comment "I assume you
> obfuscated the query on purpose, it is really
> unreadable..."
> The answer is "no". This is JD Edwards... I have no
> control over their database nor their code.
> Deciphering one of there queries is like reading a
> foreign language...
Hahaha, I guess JD Edwards did an excellent job at obfuscating :-)
So let's see what we have, and take it apart...
1. Is the index F4211_9 still used if you remove the "UNION SELECT"
part. The UNION (as opposed to UNION ALL) forces the resultset to be
merged, and so this can influence the query plan.
After removing the UNION SELECT part, I simplified the query to the
following:
SELECT SDLTTR, SDDCTO, SDMCU -- used in WHERE clause
, SDDOCO, SDKCOO, SDLNID -- covered by clustered index
, SDITM, SDDRQJ -- covered by index F4211_9
-- and many other columns not covered by any index
FROM PRODDTA.F4211 (NOLOCK)
WHERE SDLTTR BETWEEN '520' AND '999'
AND SDDCTO = 'S2'
AND SDMCU = ' 9500'
-- CLUQ = (SDDOCO, SDDCTO, SDKCOO, SDLNID)
-- IX F4211_9 = (SDITM, SDMCU, SDDRQJ)
So now, let's explore a few options that SQL-Server can choose from:
a) Seek the clustered index: not possible
b) Seek index F4211_9: not possible
c) Scan clustered index: always possible. Seems a fairly good choice,
but the F4211 table seems to be a very wide table (many bytes per row)
d) Scan index F4211_9 and perform bookmark lookups: this will cover the
SDMCU and SDDCTO predicates, but not the SDLTTR predicate. The index
F4211_9 is a narrow index (few bytes per row).
So now it depends on the estimated data distribution. If SQL-Server
estimates that there are many rows where SDDCTO = 'S2' AND SDMCU =
' 9500', then it will not choose scenario d. If it estimates that
there will just be a few rows, then it will.
2. How many pages does the table and the individual indexes occupy?
This can be queried with:
select indid,left(name,30),dpages from sysindexes where
id=object_id("F4211") and name not like '[_]WA%'
3. Most likely there are statistics on the relevant columns. These
statistics are used to estimate how many rows will have a SDDCTO of 'S2'
and how many rows will have a SDMCU of ' 9500'.
This can be queried with:
dbcc show_statistics ("F4211", 'SDDCTO')
dbcc show_statistics ("F4211", 'SDMCU')
Now that you have all the numbers, let's make your own estimation. For
this example, I made up some numbers. You can replace them with the real
numbers. Also, I guessed some internal SQL-Server estimation
percentages.
My example numbers:
- Rows in table: 100,000
- Size of table F4211 in pages: 10,000
- Non-leaf level of clustered index in pages: 700
- Size of index F4211_9 in pages: 900
- Occurrence of value 'S2' in column SDDCTO: 0.3%
- Occurrence of value ' 9500' in column SDMCU: 0.1%
- Assume a 60% overlap between the rows where SDDCTO='S2' and
SDMCU=' 9500'
Cost list for use of index F4211_9:
a) scan entire F4211_9 index: 900 reads
b) lowest occurrence column (SDMCU, 0.1%) * overlap percentage (60%) *
rows in table = 60 rows that need to be looked up: 60 reads
Total estimated cost: 960 reads
Cost list for use of clustered index scenario 1 (full scan):
a) scan the entire clustered index: 10,000 reads
Total estimated cost: 10,000 reads
Cost list for use of clustered index scenario 2 (partial scan):
a) scan tree (non-leaf level) for SDDCTO='S2': 700 reads
b) rows found approximately 0.3% of 100,000 = 300. Estimation with
respect to leaf level: 300 reads
Total estimated cost: 1,000 reads
In this example, using index F4211_9 would in fact be the fastest
estimated solution!
Hope this helps,
Gert-Jan
(Please reply only to the newsgroup)
|||> Anyway, there are other indexes that exist where the
> leading column is in the predicate. For example, there is
> an index on SDLTTR. Why didn't it use that one?
The optimizer estimates the number of qualifying rows based on index
statistics. If many rows satisfy your SDLTTR range criteria, it may be more
efficient to use another technique.

> I don't understand why it would think the scan of an
> index whose leading column isn't "filled-in" would be
> more efficient than just doing a full table scan (or
> using one of the other indexes in the predicate.)
A non-clustered index is usually much smaller than the underlying table so
it can take less time to scan the entire non-clustered index than to scan
the entire clustered index (table).
AsGert-Jan mentioned, the UNION may play a role in the index choice and
query plan. I see from your other post that you have a unique clustered
index on SDDOCO, SDDCTO, SDKCOO and SDLNID. This means the F4211 index
contains SDITM, SDMCU and SDDRQJ as well as SDDOCO, SDDCTO, SDKCOO and
SDLNID. The combination of these values will be unique and all are in your
SELECT list so SQL Server may have chosen it to help the UNION.
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:0f7501c46ec6$afa2d280$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks for the response, and I apologize for the lack of
> detail. This is a JD Edwards query, which I have no
> control over. In addition, they have lots of indexes on
> this table... (They love to over index!) So including
> them would be quite lengthy.
> Anyway, there are other indexes that exist where the
> leading column is in the predicate. For example, there is
> an index on SDLTTR. Why didn't it use that one?
> I don't understand why it would think the scan of an
> index whose leading column isn't "filled-in" would be
> more efficient than just doing a full table scan (or
> using one of the other indexes in the predicate.)
>
> would guess that
> beneficial here but
> a clustered index.
> narrow non-clustered
> of a clustered
> message
> wrong
> column
> the
> the
> AND

Index chosen is wrong index

I'm on SQL Server 2000 SP3.
The execution plan for the query below selects the wrong
index. It selects the index that has the leading column
as SDITIM, which is not even in the where clause nor the
order by clause. The index that it chose is not even the
clustered index. Any ideas on WHY? Is this a BUG?
Index Chosen in Execution Plan
====================================
CREATE INDEX [F4211_9] ON [PRODDTA].[F4211]([SDITM],
[SDMCU], [SDDRQJ]) WITH FILLFACTOR = 90 ON [SECONDARY]
GO
SQL Statement
==============
SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU,
SDOKCO, SDOORN, SDOCTO, SDRKCO,
SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8,
SDDRQJ, SDTRDJ, SDPDDJ, SDOPDJ,
SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01,
SDITM, SDLITM, SDAITM, SDLOCN,
SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU,
SDRLIT, SDRKIT, SDSRP1, SDSRP2,
SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK,
SDSOCN, SDUPRC, SDAEXP, SDUNCS,
SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCDCD,
SDCARS, SDMOT, SDZON, SDFRTH,
SDUOM4, SDSO15, SDSLSM, SDSLM2, SDSWMS, SDCRCD, SDCRR,
SDFUP, SDFEA, SDFUC, SDTORG
FROM PRODDTA.F4211 (NOLOCK) WHERE ( SDLTTR >= '520' AND
SDDCTO = 'S2' AND
SDLTTR <= '999'
AND SDMCU = ' 9500' )
UNION SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO,
SDMCU, SDOKCO,
SDOORN, SDOCTO, SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT,
SDAN8, SDSHAN, SDPA8, SDDRQJ,
SDTRDJ, SDPDDJ, SDOPDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL,
SDRSDJ, SDPEFJ, SDVR01, SDITM,
SDLITM, SDAITM, SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY,
SDNXTR, SDLTTR, SDEMCU, SDRLIT,
SDRKIT, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM,
SDUORG, SDSOQS, SDSOBK, SDSOCN,
SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT,
SDPSN, SDDELN, SDCDCD, SDCARS,
SDMOT, SDZON, SDFRTH, SDUOM4, SDSO15, SDSLSM, SDSLM2,
SDSWMS, SDCRCD, SDCRR, SDFUP,
SDFEA, SDFUC, SDTORG FROM PRODDTA.F42119 (NOLOCK)
WHERE ( SDLTTR >= '520' AND SDDCTO = 'S2' AND SDLTTR
<= '999' AND
SDMCU = ' 9500')It's difficult to say without the other relevant DDL. I would guess that
you don't have other indexes the optimizer considers beneficial here but
some of the other columns in your predicates are part of a clustered index.
In this case, the optimizer might choose a scan of narrow non-clustered
index to retrieve values of clustered index keys instead of a clustered
index (table) scan.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jean Bertrand" <jean.bertrand@.sbcglobal.net> wrote in message
news:077301c46e59$ffe7c2e0$a401280a@.phx.gbl...
> I'm on SQL Server 2000 SP3.
> The execution plan for the query below selects the wrong
> index. It selects the index that has the leading column
> as SDITIM, which is not even in the where clause nor the
> order by clause. The index that it chose is not even the
> clustered index. Any ideas on WHY? Is this a BUG?
>
> Index Chosen in Execution Plan
> ====================================
> CREATE INDEX [F4211_9] ON [PRODDTA].[F4211]([SDITM],
> [SDMCU], [SDDRQJ]) WITH FILLFACTOR = 90 ON [SECONDARY]
> GO
> SQL Statement
> ==============
> SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU,
> SDOKCO, SDOORN, SDOCTO, SDRKCO,
> SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8,
> SDDRQJ, SDTRDJ, SDPDDJ, SDOPDJ,
> SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01,
> SDITM, SDLITM, SDAITM, SDLOCN,
> SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU,
> SDRLIT, SDRKIT, SDSRP1, SDSRP2,
> SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK,
> SDSOCN, SDUPRC, SDAEXP, SDUNCS,
> SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCDCD,
> SDCARS, SDMOT, SDZON, SDFRTH,
> SDUOM4, SDSO15, SDSLSM, SDSLM2, SDSWMS, SDCRCD, SDCRR,
> SDFUP, SDFEA, SDFUC, SDTORG
> FROM PRODDTA.F4211 (NOLOCK) WHERE ( SDLTTR >= '520' AND
> SDDCTO = 'S2' AND
> SDLTTR <= '999'
> AND SDMCU = ' 9500' )
> UNION SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO,
> SDMCU, SDOKCO,
> SDOORN, SDOCTO, SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT,
> SDAN8, SDSHAN, SDPA8, SDDRQJ,
> SDTRDJ, SDPDDJ, SDOPDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL,
> SDRSDJ, SDPEFJ, SDVR01, SDITM,
> SDLITM, SDAITM, SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY,
> SDNXTR, SDLTTR, SDEMCU, SDRLIT,
> SDRKIT, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM,
> SDUORG, SDSOQS, SDSOBK, SDSOCN,
> SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT,
> SDPSN, SDDELN, SDCDCD, SDCARS,
> SDMOT, SDZON, SDFRTH, SDUOM4, SDSO15, SDSLSM, SDSLM2,
> SDSWMS, SDCRCD, SDCRR, SDFUP,
> SDFEA, SDFUC, SDTORG FROM PRODDTA.F42119 (NOLOCK)
> WHERE ( SDLTTR >= '520' AND SDDCTO = 'S2' AND SDLTTR
> <= '999' AND
> SDMCU = ' 9500')|||Looks fine to me. SDMCU is in the WHERE clause and it is one of the SARGS
where you use an =. If the selectivity of this is low enough (and the
optimizer thinks it is) it can find the rows that match the value of '9500'
and just filter for the other sargs. Just a note, are the columns SDLTTR
and SDMCU really characters or integers?
Andrew J. Kelly SQL MVP
"Jean Bertrand" <jean.bertrand@.sbcglobal.net> wrote in message
news:077301c46e59$ffe7c2e0$a401280a@.phx.gbl...
> I'm on SQL Server 2000 SP3.
> The execution plan for the query below selects the wrong
> index. It selects the index that has the leading column
> as SDITIM, which is not even in the where clause nor the
> order by clause. The index that it chose is not even the
> clustered index. Any ideas on WHY? Is this a BUG?
>
> Index Chosen in Execution Plan
> ====================================
> CREATE INDEX [F4211_9] ON [PRODDTA].[F4211]([SDITM],
> [SDMCU], [SDDRQJ]) WITH FILLFACTOR = 90 ON [SECONDARY]
> GO
> SQL Statement
> ==============
> SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU,
> SDOKCO, SDOORN, SDOCTO, SDRKCO,
> SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8,
> SDDRQJ, SDTRDJ, SDPDDJ, SDOPDJ,
> SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01,
> SDITM, SDLITM, SDAITM, SDLOCN,
> SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU,
> SDRLIT, SDRKIT, SDSRP1, SDSRP2,
> SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK,
> SDSOCN, SDUPRC, SDAEXP, SDUNCS,
> SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCDCD,
> SDCARS, SDMOT, SDZON, SDFRTH,
> SDUOM4, SDSO15, SDSLSM, SDSLM2, SDSWMS, SDCRCD, SDCRR,
> SDFUP, SDFEA, SDFUC, SDTORG
> FROM PRODDTA.F4211 (NOLOCK) WHERE ( SDLTTR >= '520' AND
> SDDCTO = 'S2' AND
> SDLTTR <= '999'
> AND SDMCU = ' 9500' )
> UNION SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO,
> SDMCU, SDOKCO,
> SDOORN, SDOCTO, SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT,
> SDAN8, SDSHAN, SDPA8, SDDRQJ,
> SDTRDJ, SDPDDJ, SDOPDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL,
> SDRSDJ, SDPEFJ, SDVR01, SDITM,
> SDLITM, SDAITM, SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY,
> SDNXTR, SDLTTR, SDEMCU, SDRLIT,
> SDRKIT, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM,
> SDUORG, SDSOQS, SDSOBK, SDSOCN,
> SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT,
> SDPSN, SDDELN, SDCDCD, SDCARS,
> SDMOT, SDZON, SDFRTH, SDUOM4, SDSO15, SDSLSM, SDSLM2,
> SDSWMS, SDCRCD, SDCRR, SDFUP,
> SDFEA, SDFUC, SDTORG FROM PRODDTA.F42119 (NOLOCK)
> WHERE ( SDLTTR >= '520' AND SDDCTO = 'S2' AND SDLTTR
> <= '999' AND
> SDMCU = ' 9500')|||Jean,
as other have suggested, you have not provided enough information to
support any conclusion. For example, do you have any unique
constraints/indexes, do you have a clustered index (if so, what is its
definition), what is the distribution of the data, etcetera.
In addition, since you are joining two resultsets, there is likely to be
a second index seek/scan, and the union can affect the query plan.
If you post more information, maybe someone can say something with more
confidence.
BTW: I assume obfuscated the query on purpose, it is really
unreadable...
Gert-Jan
Jean Bertrand wrote:
> I'm on SQL Server 2000 SP3.
> The execution plan for the query below selects the wrong
> index. It selects the index that has the leading column
> as SDITIM, which is not even in the where clause nor the
> order by clause. The index that it chose is not even the
> clustered index. Any ideas on WHY? Is this a BUG?
> Index Chosen in Execution Plan
> ====================================
> CREATE INDEX [F4211_9] ON [PRODDTA].[F4211]([SDITM],
> [SDMCU], [SDDRQJ]) WITH FILLFACTOR = 90 ON [SECONDARY]
> GO
> SQL Statement
> ==============
> SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU,
> SDOKCO, SDOORN, SDOCTO, SDRKCO,
> SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8,
> SDDRQJ, SDTRDJ, SDPDDJ, SDOPDJ,
> SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01,
> SDITM, SDLITM, SDAITM, SDLOCN,
> SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU,
> SDRLIT, SDRKIT, SDSRP1, SDSRP2,
> SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK,
> SDSOCN, SDUPRC, SDAEXP, SDUNCS,
> SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCDCD,
> SDCARS, SDMOT, SDZON, SDFRTH,
> SDUOM4, SDSO15, SDSLSM, SDSLM2, SDSWMS, SDCRCD, SDCRR,
> SDFUP, SDFEA, SDFUC, SDTORG
> FROM PRODDTA.F4211 (NOLOCK) WHERE ( SDLTTR >= '520' AND
> SDDCTO = 'S2' AND
> SDLTTR <= '999'
> AND SDMCU = ' 9500' )
> UNION SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO,
> SDMCU, SDOKCO,
> SDOORN, SDOCTO, SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT,
> SDAN8, SDSHAN, SDPA8, SDDRQJ,
> SDTRDJ, SDPDDJ, SDOPDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL,
> SDRSDJ, SDPEFJ, SDVR01, SDITM,
> SDLITM, SDAITM, SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY,
> SDNXTR, SDLTTR, SDEMCU, SDRLIT,
> SDRKIT, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM,
> SDUORG, SDSOQS, SDSOBK, SDSOCN,
> SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT,
> SDPSN, SDDELN, SDCDCD, SDCARS,
> SDMOT, SDZON, SDFRTH, SDUOM4, SDSO15, SDSLSM, SDSLM2,
> SDSWMS, SDCRCD, SDCRR, SDFUP,
> SDFEA, SDFUC, SDTORG FROM PRODDTA.F42119 (NOLOCK)
> WHERE ( SDLTTR >= '520' AND SDDCTO = 'S2' AND SDLTTR
> <= '999' AND
> SDMCU = ' 9500')
(Please reply only to the newsgroup)|||Thanks for your response.
Please note that the query is from JD Edwards, so I have
no control over it. The data types of the columns in
predicates are truly characters, so they do match the
values. No problems there!
So, I don't understand how this could be correct if the
leading column for index F4211_9 is on the column SDITIM,
which isn't in the predicate. I would think that the
optimizer would have chosen another index where the
leading columns are in the predicate. (I didn't realize
that it could use an index when only the middle column is
in the predicate.)

>--Original Message--
>Looks fine to me. SDMCU is in the WHERE clause and it
is one of the SARGS
>where you use an =. If the selectivity of this is low
enough (and the
>optimizer thinks it is) it can find the rows that match
the value of '9500'
>and just filter for the other sargs. Just a note, are
the columns SDLTTR
>and SDMCU really characters or integers?
>--
>Andrew J. Kelly SQL MVP
>
>"Jean Bertrand" <jean.bertrand@.sbcglobal.net> wrote in
message
>news:077301c46e59$ffe7c2e0$a401280a@.phx.gbl...
wrong[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
AND[vbcol=seagreen]
>
>.
>|||Thanks for the response, and I apologize for the lack of
detail. This is a JD Edwards query, which I have no
control over. In addition, they have lots of indexes on
this table... (They love to over index!) So including
them would be quite lengthy.
Anyway, there are other indexes that exist where the
leading column is in the predicate. For example, there is
an index on SDLTTR. Why didn't it use that one?
I don't understand why it would think the scan of an
index whose leading column isn't "filled-in" would be
more efficient than just doing a full table scan (or
using one of the other indexes in the predicate.)

>--Original Message--
>It's difficult to say without the other relevant DDL. I
would guess that
>you don't have other indexes the optimizer considers
beneficial here but
>some of the other columns in your predicates are part of
a clustered index.
>In this case, the optimizer might choose a scan of
narrow non-clustered
>index to retrieve values of clustered index keys instead
of a clustered
>index (table) scan.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Jean Bertrand" <jean.bertrand@.sbcglobal.net> wrote in
message
>news:077301c46e59$ffe7c2e0$a401280a@.phx.gbl...
wrong[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
AND[vbcol=seagreen]
>
>.
>|||Thanks for the response.
1. There is a unique contraint/clustered index on
columns [SDDOCO], [SDDCTO], [SDKCOO], and [SDLNID]
2. I had to chuckle at your comment "I assume you
obfuscated the query on purpose, it is really
unreadable..."
The answer is "no". This is JD Edwards... I have no
control over their database nor their code.
Deciphering one of there queries is like reading a
foreign language...

>--Original Message--
>Jean,
>as other have suggested, you have not provided enough
information to
>support any conclusion. For example, do you have any
unique
>constraints/indexes, do you have a clustered index (if
so, what is its
>definition), what is the distribution of the data,
etcetera.
>In addition, since you are joining two resultsets, there
is likely to be
>a second index seek/scan, and the union can affect the
query plan.
>If you post more information, maybe someone can say
something with more
>confidence.
>BTW: I assume obfuscated the query on purpose, it is
really
>unreadable...
>Gert-Jan
>
>Jean Bertrand wrote:
wrong[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
AND[vbcol=seagreen]
>--
>(Please reply only to the newsgroup)
>.
>|||I'm sorry but I thought the leading column in the index you posted was
SDMCU. Because of the way it wrapped I missed that it was really the second
column in the index. Is there another index where SDMCU is the first column
in the index? How many rows match the value of 9500 for SDMCU? If it's
not too many the partial index scan could still be the most efficient. You
mentioned in the other post that the first column in this index isn't filled
in. That means it is all the same value so it's pretty easy to see where
the 9500 values are in the second column of the index. Even if the number
of rows are more than would normally be used for a seek it can seek the
first matching row in the index and scan from there until it gets to the
last one. That's actually pretty efficient over scanning a whole large
table if the numbers are right.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:0f5b01c46ec6$123f8830$a601280a@.phx.gbl...[vbcol=seagreen]
> Thanks for your response.
> Please note that the query is from JD Edwards, so I have
> no control over it. The data types of the columns in
> predicates are truly characters, so they do match the
> values. No problems there!
> So, I don't understand how this could be correct if the
> leading column for index F4211_9 is on the column SDITIM,
> which isn't in the predicate. I would think that the
> optimizer would have chosen another index where the
> leading columns are in the predicate. (I didn't realize
> that it could use an index when only the middle column is
> in the predicate.)
>
> is one of the SARGS
> enough (and the
> the value of '9500'
> the columns SDLTTR
> message
> wrong
> column
> the
> the
> AND|||jean.bertrand@.sbcglobal.net wrote:
> Thanks for the response.
> 1. There is a unique contraint/clustered index on
> columns [SDDOCO], [SDDCTO], [SDKCOO], and [SDLNID]
> 2. I had to chuckle at your comment "I assume you
> obfuscated the query on purpose, it is really
> unreadable..."
> The answer is "no". This is JD Edwards... I have no
> control over their database nor their code.
> Deciphering one of there queries is like reading a
> foreign language...
Hahaha, I guess JD Edwards did an excellent job at obfuscating :-)
So let's see what we have, and take it apart...
1. Is the index F4211_9 still used if you remove the "UNION SELECT"
part. The UNION (as opposed to UNION ALL) forces the resultset to be
merged, and so this can influence the query plan.
After removing the UNION SELECT part, I simplified the query to the
following:
SELECT SDLTTR, SDDCTO, SDMCU -- used in WHERE clause
, SDDOCO, SDKCOO, SDLNID -- covered by clustered index
, SDITM, SDDRQJ -- covered by index F4211_9
-- and many other columns not covered by any index
FROM PRODDTA.F4211 (NOLOCK)
WHERE SDLTTR BETWEEN '520' AND '999'
AND SDDCTO = 'S2'
AND SDMCU = ' 9500'
-- CLUQ = (SDDOCO, SDDCTO, SDKCOO, SDLNID)
-- IX F4211_9 = (SDITM, SDMCU, SDDRQJ)
So now, let's explore a few options that SQL-Server can choose from:
a) Seek the clustered index: not possible
b) Seek index F4211_9: not possible
c) Scan clustered index: always possible. Seems a fairly good choice,
but the F4211 table seems to be a very wide table (many bytes per row)
d) Scan index F4211_9 and perform bookmark lookups: this will cover the
SDMCU and SDDCTO predicates, but not the SDLTTR predicate. The index
F4211_9 is a narrow index (few bytes per row).
So now it depends on the estimated data distribution. If SQL-Server
estimates that there are many rows where SDDCTO = 'S2' AND SDMCU =
' 9500', then it will not choose scenario d. If it estimates that
there will just be a few rows, then it will.
2. How many pages does the table and the individual indexes occupy?
This can be queried with:
select indid,left(name,30),dpages from sysindexes where
id=object_id("F4211") and name not like '[_]WA%'
3. Most likely there are statistics on the relevant columns. These
statistics are used to estimate how many rows will have a SDDCTO of 'S2'
and how many rows will have a SDMCU of ' 9500'.
This can be queried with:
dbcc show_statistics ("F4211", 'SDDCTO')
dbcc show_statistics ("F4211", 'SDMCU')
Now that you have all the numbers, let's make your own estimation. For
this example, I made up some numbers. You can replace them with the real
numbers. Also, I guessed some internal SQL-Server estimation
percentages.
My example numbers:
- Rows in table: 100,000
- Size of table F4211 in pages: 10,000
- Non-leaf level of clustered index in pages: 700
- Size of index F4211_9 in pages: 900
- Occurrence of value 'S2' in column SDDCTO: 0.3%
- Occurrence of value ' 9500' in column SDMCU: 0.1%
- Assume a 60% overlap between the rows where SDDCTO='S2' and
SDMCU=' 9500'
Cost list for use of index F4211_9:
a) scan entire F4211_9 index: 900 reads
b) lowest occurrence column (SDMCU, 0.1%) * overlap percentage (60%) *
rows in table = 60 rows that need to be looked up: 60 reads
Total estimated cost: 960 reads
Cost list for use of clustered index scenario 1 (full scan):
a) scan the entire clustered index: 10,000 reads
Total estimated cost: 10,000 reads
Cost list for use of clustered index scenario 2 (partial scan):
a) scan tree (non-leaf level) for SDDCTO='S2': 700 reads
b) rows found approximately 0.3% of 100,000 = 300. Estimation with
respect to leaf level: 300 reads
Total estimated cost: 1,000 reads
In this example, using index F4211_9 would in fact be the fastest
estimated solution!
Hope this helps,
Gert-Jan
(Please reply only to the newsgroup)|||> Anyway, there are other indexes that exist where the
> leading column is in the predicate. For example, there is
> an index on SDLTTR. Why didn't it use that one?
The optimizer estimates the number of qualifying rows based on index
statistics. If many rows satisfy your SDLTTR range criteria, it may be more
efficient to use another technique.

> I don't understand why it would think the scan of an
> index whose leading column isn't "filled-in" would be
> more efficient than just doing a full table scan (or
> using one of the other indexes in the predicate.)
A non-clustered index is usually much smaller than the underlying table so
it can take less time to scan the entire non-clustered index than to scan
the entire clustered index (table).
AsGert-Jan mentioned, the UNION may play a role in the index choice and
query plan. I see from your other post that you have a unique clustered
index on SDDOCO, SDDCTO, SDKCOO and SDLNID. This means the F4211 index
contains SDITM, SDMCU and SDDRQJ as well as SDDOCO, SDDCTO, SDKCOO and
SDLNID. The combination of these values will be unique and all are in your
SELECT list so SQL Server may have chosen it to help the UNION.
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:0f7501c46ec6$afa2d280$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks for the response, and I apologize for the lack of
> detail. This is a JD Edwards query, which I have no
> control over. In addition, they have lots of indexes on
> this table... (They love to over index!) So including
> them would be quite lengthy.
> Anyway, there are other indexes that exist where the
> leading column is in the predicate. For example, there is
> an index on SDLTTR. Why didn't it use that one?
> I don't understand why it would think the scan of an
> index whose leading column isn't "filled-in" would be
> more efficient than just doing a full table scan (or
> using one of the other indexes in the predicate.)
>
>
> would guess that
> beneficial here but
> a clustered index.
> narrow non-clustered
> of a clustered
> message
> wrong
> column
> the
> the
> AND