|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Query performance
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Deane Landreth 2002-11-29, 5:23 am |
| I maintain a database of ~ 4 Gb that stores data on approx 750000 books.
Over the course of a few days the perfomance of one of the stored procedues
that does a title search degrades by around twenty times.
However ,simply by opening the stored procedure in the query analyser then
saving it again , performance is restored.
What sort of things would cause this behavior? Initially I thought it was a
memory issue , but the problem survives a restart of the sql service or the
machine. The tables involved are heavily indexed so I am assuming that
somehow recompiling the query allows better use of the indexes. If this is
so where should I start looking to 1. solve the problem and 2. further
optimise the query for speed.
| |
| Allan Mitchell 2002-11-29, 6:23 am |
| Have a look at the Query Plan. Bad things = Table scan.
Also make sure the statistics are up to date.
Could this be clashing with something else running at the same time ????
--
Allan Mitchell (Microsoft SQL Server MVP)
www.SQLDTS.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Deane Landreth" <dlandreth@no_spam_estaronline.com> wrote in message
news:#Ugnfl5lCHA.1516@tkmsftngp04...
> I maintain a database of ~ 4 Gb that stores data on approx 750000
books.
> Over the course of a few days the perfomance of one of the stored
procedues
> that does a title search degrades by around twenty times.
> However ,simply by opening the stored procedure in the query analyser
then
> saving it again , performance is restored.
>
> What sort of things would cause this behavior? Initially I thought it was
a
> memory issue , but the problem survives a restart of the sql service or
the
> machine. The tables involved are heavily indexed so I am assuming that
> somehow recompiling the query allows better use of the indexes. If this is
> so where should I start looking to 1. solve the problem and 2. further
> optimise the query for speed.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
| |
| Steve Thompson 2002-11-29, 2:24 pm |
| "Deane Landreth" <dlandreth@no_spam_estaronline.com> wrote in message
news:#Ugnfl5lCHA.1516@tkmsftngp04...
> I maintain a database of ~ 4 Gb that stores data on approx 750000
books.
> Over the course of a few days the perfomance of one of the stored
procedues
> that does a title search degrades by around twenty times.
> However ,simply by opening the stored procedure in the query analyser
then
> saving it again , performance is restored.
>
> What sort of things would cause this behavior?
Index statistics that are out of date could cause this, as well as stored
procedures that need to have their query plan updated. It may be time to
rebuild the indexes on the table that is used for the title searches. Also,
this may be one of those items that could benefit from a clustered index and
a query that performs a covered index search.
Steve
| |
| Gert-Jan Strik 2002-11-29, 2:24 pm |
| Deane,
make sure you have turned "Auto create statistics" and "Auto update
statistics" on.
Gert-Jan
Deane Landreth wrote:
>
> I maintain a database of ~ 4 Gb that stores data on approx 750000 books.
> Over the course of a few days the perfomance of one of the stored procedues
> that does a title search degrades by around twenty times.
> However ,simply by opening the stored procedure in the query analyser then
> saving it again , performance is restored.
>
> What sort of things would cause this behavior? Initially I thought it was a
> memory issue , but the problem survives a restart of the sql service or the
> machine. The tables involved are heavily indexed so I am assuming that
> somehow recompiling the query allows better use of the indexes. If this is
> so where should I start looking to 1. solve the problem and 2. further
> optimise the query for speed.
| |
| Quentin Ran 2002-11-29, 4:23 pm |
| Deane,
To me it sounds like the stored proc may be using an outdated execution
plan. I don't know whether "alter procedure ...." in query analyzer or open
and save in EM means a recompile of the procedure, but it appears to
according to what you are saying -- no update of statistics but just a
"refresh" of the stored proc makes is run better.
To test this, you can add "with recompile", like
create procedure .... with recompile
....
Possible for you to share what you find out?
Quentin
"Deane Landreth" <dlandreth@no_spam_estaronline.com> wrote in message
news:#Ugnfl5lCHA.1516@tkmsftngp04...
> I maintain a database of ~ 4 Gb that stores data on approx 750000
books.
> Over the course of a few days the perfomance of one of the stored
procedues
> that does a title search degrades by around twenty times.
> However ,simply by opening the stored procedure in the query analyser
then
> saving it again , performance is restored.
>
> What sort of things would cause this behavior? Initially I thought it was
a
> memory issue , but the problem survives a restart of the sql service or
the
> machine. The tables involved are heavily indexed so I am assuming that
> somehow recompiling the query allows better use of the indexes. If this is
> so where should I start looking to 1. solve the problem and 2. further
> optimise the query for speed.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
| |
| Brad Wartman 2002-11-30, 3:23 pm |
| It sounds like the culprit may be your statistics if you're also
getting lots of inserts/updates/deletes to your tables. You can use
the WITH RECOMPILE option on your stored proc to force the SQL
Optimizer to redo the query plan. The down side of WITH RECOMPILE is
that the procedure will be recompiled every time whether you like it
or not.
You might also check to see if Auto Update Statistics or Auto Create
Statistics is turned on. I generally leave them turned off because
they can cause poor query performance by flushing the cache whenever
they run.
On Sat, 30 Nov 2002 00:19:03 +1300, "Deane Landreth"
<dlandreth@no_spam_estaronline.com> wrote:
>I maintain a database of ~ 4 Gb that stores data on approx 750000 books.
>Over the course of a few days the perfomance of one of the stored procedues
>that does a title search degrades by around twenty times.
>However ,simply by opening the stored procedure in the query analyser then
>saving it again , performance is restored.
>
>What sort of things would cause this behavior? Initially I thought it was a
>memory issue , but the problem survives a restart of the sql service or the
>machine. The tables involved are heavily indexed so I am assuming that
>somehow recompiling the query allows better use of the indexes. If this is
>so where should I start looking to 1. solve the problem and 2. further
>optimise the query for speed.
|
|
|
|
|