Query not using index

Hi,
I have a client who is running a query (generated from his ERP system) that
is taking 2-3 minutes to run, doing a full table scan. I tried the same
thing in my database with the same database (tables etc.) and it uses an
index and takes a couple of seconds to run. I have checked that he has the
same index as me in place and he has re-indexed the table.
In addition he has Auto Create Statistics and Auto Update Statistics turned
on (same as me). I'm now running low on ideas. Can anybody give me a few
more pointers.
Thanks in advanceHi,
Execute DBCC SHOW_STATISTICS on that table and check when the statistics was
last updated.
As well as, Could you please run a DBCC DBREINDEX on that table and check
the query execution plan.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP
> system) that is taking 2-3 minutes to run, doing a full table scan.
> I tried the same thing in my database with the same database (tables
> etc.) and it uses an index and takes a couple of seconds to run. I
> have checked that he has the same index as me in place and he has
> re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
> turned on (same as me). I'm now running low on ideas. Can anybody
> give me a few more pointers.
> Thanks in advance
If you can try running DBCC DBREINDEX on this table. This is an offline
operation and the table will not be available during the rebuild.
As an alternative, you could try running DBCC INDEXDEFRAG on the table
which is an online operation.
Or you could try running Update Statistics on the table.
Using an index (or not) also depends on server utilization and memory,
table fragmentation, statistics, etc.
How many rows are returned from the query on the table where you expect
the index to be used? How many rows are in the table?
David G.|||Thanks for that Hari,
I got him to run the show_statistics command and it was last updated two
days ago. I asked him to run a DBREINDEX today when everyone is off the
system
Thanks for your help
Michael
"Hari Prasad" wrote:
> Hi,
> Execute DBCC SHOW_STATISTICS on that table and check when the statistics was
> last updated.
> As well as, Could you please run a DBCC DBREINDEX on that table and check
> the query execution plan.
>
> Thanks
> Hari
> MCDBA
>
> "Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
> news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> > Hi,
> >
> > I have a client who is running a query (generated from his ERP system)
> that
> > is taking 2-3 minutes to run, doing a full table scan. I tried the same
> > thing in my database with the same database (tables etc.) and it uses an
> > index and takes a couple of seconds to run. I have checked that he has
> the
> > same index as me in place and he has re-indexed the table.
> >
> > In addition he has Auto Create Statistics and Auto Update Statistics
> turned
> > on (same as me). I'm now running low on ideas. Can anybody give me a few
> > more pointers.
> >
> > Thanks in advance
>
>|||Hi David,
Thanks for your comments. The table contains 19,000 rows. The statistics
were last updated two days ago. I have asked him to run a dbcc reindex when
everyone is off the system tonight.
Michael|||Hi,
Since there is only 19,000 records you could execute DBCC INDEXDEFRAG during
online as David recommended.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:20DF83D2-A9FE-4E74-90CF-BC73E9991DA5@.microsoft.com...
> Hi David,
> Thanks for your comments. The table contains 19,000 rows. The statistics
> were last updated two days ago. I have asked him to run a dbcc reindex
when
> everyone is off the system tonight.
> Michael
>|||I want to give more infromation to you.
Though the value of rowmodctr(column of sysindexes table) is smaller than
the threashold that statistics is updated automatically on, some performance
problem can occur on your system because of huge table on AUTO UPDATE
STATISTICS.
Then you may update statistics periodically.
Hanky
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||Need to view execution plans for both databases. BTW, check to see if the
servers have different parallism settings.
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||If the 'slow' server is an SMP system, try disabling parallel processing
for that query by adding the OPTION (MAXDOP 1).
Gert-Jan
Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP system) that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance
--
(Please reply only to the newsgroup)