Query optimizer not using index!

I have a test sql 7 and test sql 2000 database with the
same table structure and number of rows loaded.
When I add a new column and build an index on it in sql 7,
the optimizer uses the index and the query returns in 5
seconds. However, when I add the new column and build an
index on it i sql 2000, the optimizer does not use the new
index and runs over 5 minutes. If I put an Index hint on
the query then it uses the index and runs in 5 seconds.
Why will not sql 2000 use this new index but sql 7 uses it
without specifying the hint'
Thanks RichYou'll need to post a lot more information before anyone could take a guess.
But first, have you made sure that the statistics on the SQL Server 2000
database have been updated?
If that doesn't solve your problem, then what do the table and index
definitions look like? Can you post the DDL? What does the data actually
look like? You mention that both databases have the same number of rows
loaded, but are the values in the rows actually identical?
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Rich" <rstoll@.cadencenet.com> wrote in message
news:04cf01c36111$680d3460$a301280a@.phx.gbl...
> I have a test sql 7 and test sql 2000 database with the
> same table structure and number of rows loaded.
> When I add a new column and build an index on it in sql 7,
> the optimizer uses the index and the query returns in 5
> seconds. However, when I add the new column and build an
> index on it i sql 2000, the optimizer does not use the new
> index and runs over 5 minutes. If I put an Index hint on
> the query then it uses the index and runs in 5 seconds.
> Why will not sql 2000 use this new index but sql 7 uses it
> without specifying the hint'
> Thanks Rich|||put the two queries (with and without index hint)
in a Query Analyzer window
and use the Display Estimated Execution Plan
does the plan for not using the index show up as lower
cost? even though it took much longer to run
if so, this is a deliberate decision by the optimizer to
switch to a table instead of an index seek with bookmark
lookups
also, do the two systems have different amount of memory,
specically, is one <=1GB and another >1GB
this would also affect when the optimizer switches from a
index to a scan
>--Original Message--
>I have a test sql 7 and test sql 2000 database with the
>same table structure and number of rows loaded.
>When I add a new column and build an index on it in sql
7,
>the optimizer uses the index and the query returns in 5
>seconds. However, when I add the new column and build an
>index on it i sql 2000, the optimizer does not use the
new
>index and runs over 5 minutes. If I put an Index hint on
>the query then it uses the index and runs in 5 seconds.
>Why will not sql 2000 use this new index but sql 7 uses
it
>without specifying the hint'
>Thanks Rich
>.
>|||Hal,
I created the statistics on the CustID. The tables have
the same no. of rows and values. I used DTS to copy the
data from sql 7 to sql 2000.
Here is the query (with the hint specified to make it run
fast):
Select Distinct uab.BillDocID, bs.BilLSrvID,bs.SrvType
FROM tbl_bill_srv bs,
tbl_chrg_non_elec_mtr cn with (index(ix_custid)),
tbl_util_acct_bill uab
Where (uab.DateRowCreated >= '2003-06-01' AND
uab.DateRowCreated <= '2003-07-01')
and cn.custid = 169
AND uab.BilLDocID = cn.BilLDocID
And cn.BillSrvID = bs.BilLSrvID
Here is the DDL:
CREATE TABLE [dbo].[tbl_chrg_non_elec_mtr] (
[ChrgNonElecMtrID] [int] NOT NULL ,
[CustID] [int] NOT NULL ,
[BillSrvID] [int] NOT NULL ,
[AcctngPer] [datetime] NOT NULL ,
[AcctID] [int] NOT NULL ,
[UtilID] [int] NOT NULL ,
[AcctNo] [varchar] (22) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BillDocID] [int] NOT NULL ,
[CsmtnPer] [datetime] NULL ,
[StartDate] [datetime] NULL ,
[StopDate] [datetime] NULL ,
[NMtrChrgType] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TaxCode] [bit] NOT NULL ,
[Qty] [float] NULL ,
[QtyUM] [char] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Chrg] [money] NOT NULL ,
[ModBy] [char] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ModDate] [datetime] NOT NULL ,
[timestamp] [timestamp] NULL ,
[Chrg_CurrencyTypeID] [tinyint] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_chrg_non_elec_mtr] WITH NOCHECK ADD
CONSTRAINT [PK_chrg_non_elec_mtr] PRIMARY KEY
CLUSTERED
(
[ChrgNonElecMtrID]
) WITH FILLFACTOR = 85 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_chrg_non_elec_mtr] ADD
CONSTRAINT [DF_NON_Chrg_CurrencyTypeID] DEFAULT
(1) FOR [Chrg_CurrencyTypeID]
GO
/****** The index created by the following statement is
for internal use only. ******/
/****** It is not a real index but exists as statistics
only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_202_8] ON [dbo].
[tbl_chrg_non_elec_mtr] ([CsmtnPer]) ')
GO
CREATE INDEX [i1_tbl_chrg_non_elec_mtr] ON [dbo].
[tbl_chrg_non_elec_mtr]([BillDocID]) WITH FILLFACTOR =85, PAD_INDEX ON [PRIMARY]
GO
CREATE INDEX [i2_tbl_chrg_non_elec_mtr] ON [dbo].
[tbl_chrg_non_elec_mtr]([BillSrvID]) WITH FILLFACTOR =85, PAD_INDEX ON [PRIMARY]
GO
CREATE INDEX [i3_tbl_chrg_non_elec_mtr] ON [dbo].
[tbl_chrg_non_elec_mtr]([AcctID]) WITH FILLFACTOR = 85,
PAD_INDEX ON [PRIMARY]
GO
CREATE INDEX [i4_tbl_chrg_non_elec_mtr] ON [dbo].
[tbl_chrg_non_elec_mtr]([CsmtnPer]) WITH FILLFACTOR =85, PAD_INDEX ON [PRIMARY]
GO
CREATE INDEX [i5_tbl_chrg_non_elec_mtr] ON [dbo].
[tbl_chrg_non_elec_mtr]([BillDocID], [CsmtnPer],
[StartDate], [StopDate]) ON [PRIMARY]
GO
CREATE INDEX [IX_custid] ON [dbo].[tbl_chrg_non_elec_mtr]
([CustID]) ON [PRIMARY]
GO
/****** The index created by the following statement is
for internal use only. ******/
/****** It is not a real index but exists as statistics
only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_custid] ON [dbo].
[tbl_chrg_non_elec_mtr] ([CustID]) ')
GO
>--Original Message--
>You'll need to post a lot more information before anyone
could take a guess.
>But first, have you made sure that the statistics on the
SQL Server 2000
>database have been updated?
>If that doesn't solve your problem, then what do the
table and index
>definitions look like? Can you post the DDL? What does
the data actually
>look like? You mention that both databases have the same
number of rows
>loaded, but are the values in the rows actually identical?
>--
>Hal Berenson, SQL Server MVP
>True Mountain Group LLC
>
>"Rich" <rstoll@.cadencenet.com> wrote in message
>news:04cf01c36111$680d3460$a301280a@.phx.gbl...
>> I have a test sql 7 and test sql 2000 database with the
>> same table structure and number of rows loaded.
>> When I add a new column and build an index on it in sql
7,
>> the optimizer uses the index and the query returns in 5
>> seconds. However, when I add the new column and build an
>> index on it i sql 2000, the optimizer does not use the
new
>> index and runs over 5 minutes. If I put an Index hint on
>> the query then it uses the index and runs in 5 seconds.
>> Why will not sql 2000 use this new index but sql 7 uses
it
>> without specifying the hint'
>> Thanks Rich
>
>.
>|||Joe,
The sql 2000 server has 128meg of ram and 1 665mhz cpu.
The sql 7 server had 1gig of ram and 2 450mhz cpu's.
>--Original Message--
>put the two queries (with and without index hint)
>in a Query Analyzer window
>and use the Display Estimated Execution Plan
>does the plan for not using the index show up as lower
>cost? even though it took much longer to run
>if so, this is a deliberate decision by the optimizer to
>switch to a table instead of an index seek with bookmark
>lookups
>also, do the two systems have different amount of memory,
>specically, is one <=1GB and another >1GB
>this would also affect when the optimizer switches from a
>index to a scan
>>--Original Message--
>>I have a test sql 7 and test sql 2000 database with the
>>same table structure and number of rows loaded.
>>When I add a new column and build an index on it in sql
>7,
>>the optimizer uses the index and the query returns in 5
>>seconds. However, when I add the new column and build an
>>index on it i sql 2000, the optimizer does not use the
>new
>>index and runs over 5 minutes. If I put an Index hint on
>>the query then it uses the index and runs in 5 seconds.
>>Why will not sql 2000 use this new index but sql 7 uses
>it
>>without specifying the hint'
>>Thanks Rich
>>.
>.
>|||Well, the first thing to try is running on a system with comparable memory.
If that doesn't do it, then you could post the showplan_all for the query on
both systems.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"rich" <rstoll@.cadencenet.com> wrote in message
news:0ee401c36195$e5164850$a301280a@.phx.gbl...
> Hal,
> I created the statistics on the CustID. The tables have
> the same no. of rows and values. I used DTS to copy the
> data from sql 7 to sql 2000.
> Here is the query (with the hint specified to make it run
> fast):
> Select Distinct uab.BillDocID, bs.BilLSrvID,bs.SrvType
> FROM tbl_bill_srv bs,
> tbl_chrg_non_elec_mtr cn with (index(ix_custid)),
> tbl_util_acct_bill uab
> Where (uab.DateRowCreated >= '2003-06-01' AND
> uab.DateRowCreated <= '2003-07-01')
> and cn.custid = 169
> AND uab.BilLDocID = cn.BilLDocID
> And cn.BillSrvID = bs.BilLSrvID
>
> Here is the DDL:
> CREATE TABLE [dbo].[tbl_chrg_non_elec_mtr] (
> [ChrgNonElecMtrID] [int] NOT NULL ,
> [CustID] [int] NOT NULL ,
> [BillSrvID] [int] NOT NULL ,
> [AcctngPer] [datetime] NOT NULL ,
> [AcctID] [int] NOT NULL ,
> [UtilID] [int] NOT NULL ,
> [AcctNo] [varchar] (22) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [BillDocID] [int] NOT NULL ,
> [CsmtnPer] [datetime] NULL ,
> [StartDate] [datetime] NULL ,
> [StopDate] [datetime] NULL ,
> [NMtrChrgType] [varchar] (25) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TaxCode] [bit] NOT NULL ,
> [Qty] [float] NULL ,
> [QtyUM] [char] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Chrg] [money] NOT NULL ,
> [ModBy] [char] (8) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ModDate] [datetime] NOT NULL ,
> [timestamp] [timestamp] NULL ,
> [Chrg_CurrencyTypeID] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tbl_chrg_non_elec_mtr] WITH NOCHECK ADD
> CONSTRAINT [PK_chrg_non_elec_mtr] PRIMARY KEY
> CLUSTERED
> (
> [ChrgNonElecMtrID]
> ) WITH FILLFACTOR = 85 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tbl_chrg_non_elec_mtr] ADD
> CONSTRAINT [DF_NON_Chrg_CurrencyTypeID] DEFAULT
> (1) FOR [Chrg_CurrencyTypeID]
> GO
> /****** The index created by the following statement is
> for internal use only. ******/
> /****** It is not a real index but exists as statistics
> only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_202_8] ON [dbo].
> [tbl_chrg_non_elec_mtr] ([CsmtnPer]) ')
> GO
> CREATE INDEX [i1_tbl_chrg_non_elec_mtr] ON [dbo].
> [tbl_chrg_non_elec_mtr]([BillDocID]) WITH FILLFACTOR => 85, PAD_INDEX ON [PRIMARY]
> GO
> CREATE INDEX [i2_tbl_chrg_non_elec_mtr] ON [dbo].
> [tbl_chrg_non_elec_mtr]([BillSrvID]) WITH FILLFACTOR => 85, PAD_INDEX ON [PRIMARY]
> GO
> CREATE INDEX [i3_tbl_chrg_non_elec_mtr] ON [dbo].
> [tbl_chrg_non_elec_mtr]([AcctID]) WITH FILLFACTOR = 85,
> PAD_INDEX ON [PRIMARY]
> GO
> CREATE INDEX [i4_tbl_chrg_non_elec_mtr] ON [dbo].
> [tbl_chrg_non_elec_mtr]([CsmtnPer]) WITH FILLFACTOR => 85, PAD_INDEX ON [PRIMARY]
> GO
> CREATE INDEX [i5_tbl_chrg_non_elec_mtr] ON [dbo].
> [tbl_chrg_non_elec_mtr]([BillDocID], [CsmtnPer],
> [StartDate], [StopDate]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_custid] ON [dbo].[tbl_chrg_non_elec_mtr]
> ([CustID]) ON [PRIMARY]
> GO
> /****** The index created by the following statement is
> for internal use only. ******/
> /****** It is not a real index but exists as statistics
> only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_custid] ON [dbo].
> [tbl_chrg_non_elec_mtr] ([CustID]) ')
> GO
>
> >--Original Message--
> >You'll need to post a lot more information before anyone
> could take a guess.
> >But first, have you made sure that the statistics on the
> SQL Server 2000
> >database have been updated?
> >
> >If that doesn't solve your problem, then what do the
> table and index
> >definitions look like? Can you post the DDL? What does
> the data actually
> >look like? You mention that both databases have the same
> number of rows
> >loaded, but are the values in the rows actually identical?
> >
> >--
> >Hal Berenson, SQL Server MVP
> >True Mountain Group LLC
> >
> >
> >"Rich" <rstoll@.cadencenet.com> wrote in message
> >news:04cf01c36111$680d3460$a301280a@.phx.gbl...
> >> I have a test sql 7 and test sql 2000 database with the
> >> same table structure and number of rows loaded.
> >>
> >> When I add a new column and build an index on it in sql
> 7,
> >> the optimizer uses the index and the query returns in 5
> >> seconds. However, when I add the new column and build an
> >> index on it i sql 2000, the optimizer does not use the
> new
> >> index and runs over 5 minutes. If I put an Index hint on
> >> the query then it uses the index and runs in 5 seconds.
> >>
> >> Why will not sql 2000 use this new index but sql 7 uses
> it
> >> without specifying the hint'
> >>
> >> Thanks Rich
> >
> >
> >.
> >|||This typically happens if you have a query in the form
SELECT .. FROM ... WHERE Column = @.value
If @.value has a higher Data Type Precedence (see BOL for more
information), then in SQL2000 Colomn is implicitely converted to the
data type of @.value (which basically makes it impossible to use the
index). SQL7 behaved differently.
So make sure your values have the same data type as the column you are
filtering. When in doubt, you can CAST the value to the data type of the
column.
Hope this helps,
Gert-Jan
Rich wrote:
> I have a test sql 7 and test sql 2000 database with the
> same table structure and number of rows loaded.
> When I add a new column and build an index on it in sql 7,
> the optimizer uses the index and the query returns in 5
> seconds. However, when I add the new column and build an
> index on it i sql 2000, the optimizer does not use the new
> index and runs over 5 minutes. If I put an Index hint on
> the query then it uses the index and runs in 5 seconds.
> Why will not sql 2000 use this new index but sql 7 uses it
> without specifying the hint'
> Thanks Rich|||In the example he posted the predicate is "cn.custid = 169" and custid is
defined as an int, so I don't think the change in conversions is the
problem..
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F3A753D.2DD83C37@.toomuchspamalready.nl...
> This typically happens if you have a query in the form
> SELECT .. FROM ... WHERE Column = @.value
> If @.value has a higher Data Type Precedence (see BOL for more
> information), then in SQL2000 Colomn is implicitely converted to the
> data type of @.value (which basically makes it impossible to use the
> index). SQL7 behaved differently.
> So make sure your values have the same data type as the column you are
> filtering. When in doubt, you can CAST the value to the data type of the
> column.
> Hope this helps,
> Gert-Jan
>
> Rich wrote:
> >
> > I have a test sql 7 and test sql 2000 database with the
> > same table structure and number of rows loaded.
> >
> > When I add a new column and build an index on it in sql 7,
> > the optimizer uses the index and the query returns in 5
> > seconds. However, when I add the new column and build an
> > index on it i sql 2000, the optimizer does not use the new
> > index and runs over 5 minutes. If I put an Index hint on
> > the query then it uses the index and runs in 5 seconds.
> >
> > Why will not sql 2000 use this new index but sql 7 uses it
> > without specifying the hint'
> >
> > Thanks Rich|||It only applies in this case if the 'real' query does not use a literal
(in this case 169), but a variable defined as bigint, smallmoney, money,
decimal, real, float or sql_variant. Since so many programmers seem to
be using floats in all kinds of situations, it would not surprise me if
this was the case here...
Gert-Jan
Hal Berenson wrote:
> In the example he posted the predicate is "cn.custid = 169" and custid is
> defined as an int, so I don't think the change in conversions is the
> problem..
> --
> Hal Berenson, SQL Server MVP
> True Mountain Group LLC
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:3F3A753D.2DD83C37@.toomuchspamalready.nl...
> > This typically happens if you have a query in the form
> >
> > SELECT .. FROM ... WHERE Column = @.value
> >
> > If @.value has a higher Data Type Precedence (see BOL for more
> > information), then in SQL2000 Colomn is implicitely converted to the
> > data type of @.value (which basically makes it impossible to use the
> > index). SQL7 behaved differently.
> >
> > So make sure your values have the same data type as the column you are
> > filtering. When in doubt, you can CAST the value to the data type of the
> > column.
> >
> > Hope this helps,
> > Gert-Jan
> >
> >
> > Rich wrote:
> > >
> > > I have a test sql 7 and test sql 2000 database with the
> > > same table structure and number of rows loaded.
> > >
> > > When I add a new column and build an index on it in sql 7,
> > > the optimizer uses the index and the query returns in 5
> > > seconds. However, when I add the new column and build an
> > > index on it i sql 2000, the optimizer does not use the new
> > > index and runs over 5 minutes. If I put an Index hint on
> > > the query then it uses the index and runs in 5 seconds.
> > >
> > > Why will not sql 2000 use this new index but sql 7 uses it
> > > without specifying the hint'
> > >
> > > Thanks Rich