Solved

optimizer is not using the correct query plan

Posted on 2006-07-20
4
731 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Unified and professional email signatures help maintain a consistent company brand image to the outside world. This article shows how to create an email signature in Exchange Server 2010 using a transport rule and how to overcome native limitations …
How many times a day do you open, acknowledge, or close an IT incident? What’s your process? Do you have a process depending on the incident, systems involved, and other factors? New Relic Alerts gives you options for how you interact with notifica…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

710 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