Re: Query Performance
From: Brian Moran (brian_at_solidqualitylearning.com)
Date: 09/07/04
- Next message: ben brugman: "Re: Exception handling in Triggers."
- Previous message: Nick Dakoronias: "Ms SQL server db options for performance issues after db corruption"
- In reply to: hdsjunk: "Query Performance"
- Next in thread: SQLCatz: "RE: Query Performance ~ Split the SP."
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 7 Sep 2004 09:30:38 -0400
for 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
- Next message: ben brugman: "Re: Exception handling in Triggers."
- Previous message: Nick Dakoronias: "Ms SQL server db options for performance issues after db corruption"
- In reply to: hdsjunk: "Query Performance"
- Next in thread: SQLCatz: "RE: Query Performance ~ Split the SP."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
