Solved

optimizer is not using the correct query plan

Posted on 2006-07-20
4
729 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 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Facebook has became the #1 social media platform. People share many funny videos there, yet you don't know how to download them? Now you can download Videos from Facebook in just 3 simple steps.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

713 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