Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

optimizer is not using the correct query plan

Posted on 2006-07-20
4
Medium Priority
?
738 Views
Last Modified: 2011-10-03
HI,
I have a problem with a query that is not using the correct query plan and this is causing the stored proc to get stuck at a particular point.  I have the same stored procedure on 2 systems, production and uat. When I look at the query plan in uat it is correct. However when the same stored procedure is executed in production the query plan is not correct. There is a join that is not executed in the right order. What could be causing this ? Co-incidentally another error appeared in my logs at the same time this problem occured:

 Err Description: [SYBASE][OLE DB Sybase provider]Sybase does not allow more than one active statement when retrieving results without a cursor.

May not be related though but i'm guessing it is.
0
Comment
Question by:colechr
4 Comments
 
LVL 3

Accepted Solution

by:
knel1234 earned 2000 total points
ID: 17145313
Hi,

With the information that you have supplied, I would try the following.
First, I would verify that the indexes are the same on this table for both environments.
This happens more than most of us would like to admit.  Second, I would run update stats.  Maybe your statistics are "out of wack".  Third, it looks like you are comparing a test and production environment.  If so, people often trim down their data to save money on disk space.  Please make sure that the cardinality of the fields in the index that you expected to be used are similar in both environments.  Lastly, you might want to drop and recreate the SP.  Just to make sure you are utilizing the correct query plan.  These should get you started.  If you could provide results and/or an indication of which of these you have attempted, that would be great.  Either way, I am sure a positive answer is coming your way.


cheers
knel  
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 17159198
Knel's points are all excellent. Unless your UAT database was dumped and loaded from production, you don't know if anything is the same in the two environments.

Rowcounts, indexes, optimiser statistics, even data fragmentation issues can all be reasons for the optimiser "doing the right thing" by picking a different query plan. I'd also suggest checking that the procedure itself is the same on both sides!!! (I had that once and it drove me crazy before I thought to check!)

Rowcounts are easy to check. Cardinality is a bit more difficult ("select count(distinct COLUMN)) ...") but could shed some useful light.

Note that running update stats will simply refresh the stats that are already there. So (say) if someone ran "update statistics [TABLE]" in one database, and "update index statistics [TABLE] using 200 values", just running "update statistics" will refresh those stats. ie. it's still possible for stats to be very different after an "update statistics" on both sides. If you really want stats to be identical, then perhaps do a "delete statistics" first. (Can you tell I've also had this happen to me?? 8-) ) Don't forget at a minimum you need to do an sp_recompile on the table after changing any stats...

Fragmentation is actually very important to the optimiser. I've seen a difference of just 0.05 in a DPCR (data page cluster ratio) cause a different join order. If you're running ASE 12.5.x of a recent vintage you have a function "derived_stats" which can give you this and other fragmentation stats, else just use "optdiag" (from the command line, like "bcp") and compare the various ratios it gives on the columns in the table. If this is an issue, you'd fix it with either "reorg rebuild" (if the table is locked datapages or datarows), or drop & recreate clustered index (if not).

Good luck!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Transferring FSMO roles is done when an admin wants to split roles between certain Domain Controllers or the Domain Controller holding the Roles has been forcefully demoted using dcpromo / forceremoval
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question