SQL Server - SQL Server Query Optimizer ignores index

Asked By pam

06-Mar-08 06:12 AM

Hi, I have a problem with a SQL Server2005 sp2. We are running a
standard ERP system (Visma) so the object structure in the database
(tables,index etc) are set nothing we can alter.

The users are begining to complain about long response time...the
problem seems to be that there is a excessive use of table scan.

I ran several querys in the tuning advisor and the output was to
create an index thas was already there. I backup/restored the database
2,6 Gb into another Server with SQL2005 sp2 and ran the exact same
querys now the Query Optimizer use the existing index, performance was
very much improved. So the database it selves seems to be fine?!

Back on the production/problem SQL, I have updated stat with full scan
and rebuilded index with no result optimizer keeps on
tablescanning...why?

Is it like in Oracle possible to config how the Optimizer works
(rulebased etc)? Or is has the instance gone corrupt?

As I said the database seems to be fine but the instance is not
performing as it should.

Thanks in advance

Kind regards /Patrik

05-Mar-08 04:52 PM

Did you update the stats on the db you restored before running the queries?
If not try that and see if it still uses the index. If the stats are up to
date and the index is not used the optimizer clearly thinks it is not
selective enough. What is the selectivity of the value you are searching
for? How many rows total in the table and how many match the value you are
searching for?  Can you post the INDEX definition and the exact query you
use?  You can use a PLAN GUIDE (see BooksOnLine for mere details) to force
it to use an index with an index hint or otherwise affect the plan by
specifying a value and the OPTIMIZE FOR hint.

--
Andrew J. Kelly    SQL MVP
Solid Quality Mentors

06-Mar-08 06:12 AM

On 5 Mar, 22:52, "Andrew J. Kelly" <sqlmvpnooos...@shadhawk.com>
?


u
ce


Hi Andrew,

First I recognized the problem with table scans in the production
database. I then profiled the ERP-appl and tested som querys in the
tuning advisor the output was to create Index that already existed. I
then copied the database with back/restore to another Server/Database
and ran the same query there. The Query Optimizer used the existing
index. After that I updated stat then rebuild all index on the table,
this I did on the prod-database but with no result same queryplan with
table scan.

I can give an example: we have a table ProdTr with 143052 rows. PK is
on JNO,TrNo if I ran a simple query like:

Select PR.*
from ProdTr PR
order by PR.JNo, PR.TrNo asc


The query plan used table scan

The tuning advisor suggest creating an index with JNo and TrNo?? Which
already exists that is the PK!

On the non production database

The query plan was RID lookup and index scan on PK-index just lika the
tuning advisor suggested on the production database.

These servers are not identical the production server is more powerful
cpu,mem and IO is much faster could that play a role?

I can't use hints or anything like that we are using a std. erp-system
so the querys that the erp-system generates is not for us to alter.

regards /Patrik

06-Mar-08 08:53 AM

On Thu, 6 Mar 2008 01:30:37 -0800 (PST), Atomic240 <pama@unitema.se>


The * means you want every column.  There is no WHERE clause so you
want every row.  A table scan would be normal here.

Indexes generally serve two purposes.  One is to get you to specific
rows without reading the entire table, but this query asks for the
entire table.  The other purpose is to avoid doing a sort, such as
might be required to satisfy an ORDER BY.  The query does have an
ORDER BY.  Since the ORDER BY matches the PK, if the PK is clustered
it should actually be a clustered index scan.  If the table is not
clustered to match the ORDER BY the optimizer will perform a table
scan followed by a sort.  A non-clustered index will not be used when
every row must be accessed.


The tuning advisor isn't widely admired, and you seem to be finding
out why.

Roy Harvey
Beacon Falls, CT

06-Mar-08 09:15 AM

As Roy stated when you have no WHERE clause and you use SELECT * there is no
choice but to scan something. Your choices depend on what you have for
indexes. If you have a HEAP (no clustered index) then it must scan the heap
(table). If you have a clustered index then it will scan the clustered index
but a scan of the clustered index is a scan of the table itself.   I also
agree with Roy in that the Database Tuning advisor is not always to be
trusted. Is your PK clustered?  If not the DTA was probably suggesting you
cluster it. When you restored the db and ran the query the statistics were
not to be trusted. You should always update the stats directly after a
restore.

--
Andrew J. Kelly    SQL MVP
Solid Quality Mentors


On 5 Mar, 22:52, "Andrew J. Kelly" <sqlmvpnooos...@shadhawk.com>


Hi Andrew,

First I recognized the problem with table scans in the production
database. I then profiled the ERP-appl and tested som querys in the
tuning advisor the output was to create Index that already existed. I
then copied the database with back/restore to another Server/Database
and ran the same query there. The Query Optimizer used the existing
index. After that I updated stat then rebuild all index on the table,
this I did on the prod-database but with no result same queryplan with
table scan.

I can give an example: we have a table ProdTr with 143052 rows. PK is
on JNO,TrNo if I ran a simple query like:

Select PR.*
from ProdTr PR
order by PR.JNo, PR.TrNo asc


The query plan used table scan

The tuning advisor suggest creating an index with JNo and TrNo?? Which
already exists that is the PK!

On the non production database

The query plan was RID lookup and index scan on PK-index just lika the
tuning advisor suggested on the production database.

These servers are not identical the production server is more powerful
cpu,mem and IO is much faster could that play a role?

I can't use hints or anything like that we are using a std. erp-system
so the querys that the erp-system generates is not for us to alter.

regards /Patrik