Solved

optimizer is not using the correct query plan

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

In this article we will learn how to backup a VMware farm using Nakivo Backup & Replication. In this tutorial we will install the software on a Windows 2012 R2 Server.
When you have clients or friends from around the world, it becomes a challenge to arrange a meeting or effectively manage your time. This is where Outlook's capability to show 2 time zones in one calendar comes in handy.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

803 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