Query Performance

Good Morning All!
I have a couple questions regarding query performance:
#1 - I have a VB6 program that allows the user to build
the where clause of a query to be ran against a SQL 2000
database. When the where clause is established, it is
sent into a stored procedure where there are 3 different
queries that can be executed based on the criteria being
used. Basically, the join structure of each query is
different. I created an index for a query that had been
running slow that executes 1 of the 3 queries in the
stored procedure. However, now whenever 1 of the other
queries are executed for a different set of criteria it is
3X longer to run. I remove the index, and it is fast
again, but my other query is slow again. Can someone
please explain to me why this happens, and why SQL chooses
a less optimized plan for some of the queries when a new
index is added?
#2 - In the same type of environment as explained above
(i.e. VB6, SQL 2000, and stored procedures), we have a
single user that tests query speed to determine what
indexes need built. We can get a query to run in 5
seconds, but when multiple users try to execute the same
or different queries together, it takes nearly 5X longer.
Once the first user gets results, then the others come
back one right after the other in no time. Does SQL only
allow 1 person to execute a stored procedure at any given
time? What effect does the mult-user environment have in
this scenerio.
Any help would be greatly appreciated, as I am new to
working with indexes.
Thank you,
Heidifor number 1...
Posting the text of the procedure would allow a more precise answer, but I
suspect the following info will help you...
Procedure plans are cached and are not recompiled if there is a plan already
in cache that can be reused. (I'm greatly simplying, but that's accurate
enough for this discussion...)
In your case, you have a proc that might run a variety of different queries
and for each query it sounds like the parameters might be quite different
from run to run. A good plan for one of the queries might not be a good plan
for the other queries. However, if a plan is already cached, it may be
re-used even if it's not the best plan. You might want to experiment with
having the 'top level' procedure call one of 3 other procs where each of the
child procs accepts the parameters.
for number 2...
It's difficult to say without more data. The most likley scenarios are a)
blocking. Do the queries update,delete,insert data? or b) waiting on some
type of resource. Search www.sqlmag.com archieves for an article (with
scripts) by Tom Davidson from MS. Use the script to run dbcc
sqlperfwaitstats and see if you have a high wait in any area. I suspect you
may be having a high wait of pageiolatch_sh. ALso, while the queries are
'running' and are not returning data untilt he first query is done... you
could look at the row in master..sysprocesses for each of the 'waiting'
queries to see what the waittype is.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:756b01c494dc$6bf12be0$a301280a@.phx.gbl...
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi|||Also have a look at
http://www.sommarskog.se/dyn-search.html
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:756b01c494dc$6bf12be0$a301280a@.phx.gbl...
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi|||hdsjunk wrote:
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi
If a query takes 5 seconds to run on a server with low utlization, then
the query is unlikely running efficiently as it is consuming 5 seconds
of duration and likely something similar in CPU. I would say you need to
performance tune the query to run in much less than 5 seconds. Something
in the 10's of milliseconds should be your goal.
As other posters have mentioned, if it's taking 5 seconds, it's likely
locking pages on the same tables other callers to the same procedure
require.
While 5 seconds may be an appropriate time for an individual end-user to
wait for a response from the application, it usually won't do in a
concurrent database environment. Use Profiler and Query Analyzer to see
what is taking so long in the query to execute and try and tune it.
Also, as others have said, you are risking recompiles sending 3
different where clauses for execution to the same stored procedure. The
way to eliminate this is to use sp_executesql inside the procedure to
execute the dynamic SQL.
David G.