July 12, 2011 at 6:01 am
GilaMonster (7/12/2011)
Ninja's_RGR'us (7/12/2011)
Good Enough Plan Found (not awefull but better if you don't have this warning)That's not a warning. It's saying that the optimiser stopped because it had found a good enough plan. That's the one you want to see (the other option is Timeout)
Ok, I left out the obvious :w00t:.
What I've found is that if I can remove the "warning" completely then I never-ish have cases where perf is unstable.
My thinking is that if on the best scenario the plan stops at good enough (meaning more options to explore), then it's more likely to go to timeout when things go a little bad than if there's no "warning" to start with.
I usually use divide and conquer to solve this. Apply to best filters on the biggest tables first and use covering index if possible. Dump results to temp table (with index or stats to have awesome estimates). Then finish the joins to get the rest of the columns (picture lookup tables with low row count here).
As I said, takes a while to do and it can't really be done without seeing the real db and real usage because of the indexing strategy part.
July 12, 2011 at 6:21 am
Ninja's_RGR'us (7/12/2011)
What I've found is that if I can remove the "warning" completely then I never-ish have cases where perf is unstable.My thinking is that if on the best scenario the plan stops at good enough (meaning more options to explore), then it's more likely to go to timeout when things go a little bad than if there's no "warning" to start with.
If there's no message there it means that SQL went all three rounds of optimisation (and the 3rd is quite an expensive one) because the plan cost at the end of phase 0 and phase 1 was still too expensive. In all honesty, GoodEnoughPlanFound is the one I want to see. It means that SQL was able to find a cheap plan without running through all phases of the optimisation.
It's actually more likely to go to timeout if it has to go through all three phases than if it can find a good enough plan fast.
Brief discussion here: http://sqlblog.com/blogs/ben_nevarez/archive/2009/08/20/the-phases-of-query-optimization.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 12, 2011 at 6:32 am
Wow awesome info.
Just out of curiosity. Are you comming from oltp or olap for this conclusion?
I understand phase 3 is expansive, but if it saves my butt from a 5 minute query instead of 5 seconds then I don't really care about the extra hit even for 1000 report runs.
July 12, 2011 at 6:43 am
Ninja's_RGR'us (7/12/2011)
Wow awesome info.Just out of curiosity. Are you comming from oltp or olap for this conclusion?
Yes. Optimiser behaves the same way. OLAP (being more complex queries) are more likely to get all phases of optimisation because the resultant plans aren't cheap enough to qualify for an early abort
I understand phase 3 is expansive, but if it saves my butt from a 5 minute query instead of 5 seconds then I don't really care about the extra hit even for 1000 report runs.
The only time the optimiser will finish early is if the plan is already considered to be cheap enough. If not, then it will go on to another phase in order to try and get the cost down to an acceptable level. It's the complex queries that go to phase 3, the simpler ones it can get an acceptable plan in a shorter period of time.
It's not that it's lazy, that it'll just give up and give you something bad back. The optimiser always gives you back a good enough plan (unless it times out). It'll never optimise until it finds the one best (that could take days, week even). It's just a case of how far does it have to go before it finds a plan that it considers good enough.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 12, 2011 at 7:46 am
Ninja's_RGR'us (7/12/2011)
Cursor query :Good Enough Plan Found (not awefull but better if you don't have this warning)
estimate 4 vs 354 found (can be a real big problem if you have more rows or even just that little amount)
Um, guys... Hang on a bit... Don't forget I'm not a SQL Server guy... What did you use to analyze the query plan ? Where do you get these warnings from ?
Ninja's_RGR'us (7/12/2011)
Last queryGood Enough Plan Found
30 rows found vs 1 estimate (imagine if the query returns 1000, that could get hellishly slow)
From what I've seen in the code I'm guessing the last query never returns more than 30 rows (or whatever is the page size), because it has a join with the @tblPK table that is filled by the cursor. Since the cursor runs for a number of iterations equal to the page size (or less if it's the last page), this query won't really return more than 30 rows. But I may be wrong...
Ninja's_RGR'us (7/12/2011)
Instead of the cursor you could do INSERT INTO #tmp SELECT TOP (What you want here) Columns FROM (cursor query).
I don't think I can use TOP 30 here because it returns only the first 30 results. In this case it would actually work because I'm asking for page 1 of the result set, but if I need other pages, it would always return the first 30 items right ? This code is dynamically generated given specific parameters one of which is the page number we are requesting. Incidentally, do you guys know of any best practices to do paging of the result sets on the database side ?
Ninja's_RGR'us (7/12/2011)
((0 = 1 and tblRegisto.flagPasta <> 'R') or (0 = 0)) and ...That kind of filtering only gets optimized correctly if the query plan is rebuilt at every run. That could very well be your source of the problem (along with everything else). Either use dynamic sql (sp_executesql) or use with recompile for the whole procedure. This will incure a compile hit but I don't think you have a choice here.
I will try the tips you sent, plus I will also try out sp_executesql or with recompile. If I cannot solve this, then I will probably have to find a database expert to do some tuning.
Thanks for your help, once again.
Regards,
jm
July 12, 2011 at 7:54 am
Ignore the Good Enough Plan Found comment, Gail had more info than me on that topic. Bottom line is that this is not an issue.
I've read a really good article on paging but I can't find it atm, Gail??
If you're new at this you really should get an expert in right away. This is far from a simple project.
In what region of the world are you? Maybe I can send recommendations.
I could also VPN in if you can work this way.
July 12, 2011 at 7:57 am
Ninja's_RGR'us (7/12/2011)
I've read a really good article on paging but I can't find it atm, Gail??
Paul White's articles here (don't have link handy)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 12, 2011 at 8:03 am
GilaMonster (7/12/2011)
Ninja's_RGR'us (7/12/2011)
I've read a really good article on paging but I can't find it atm, Gail??Paul White's articles here (don't have link handy)
Here's the link to article #2. Article #1 is linked in it.
July 12, 2011 at 8:14 am
Ninja's_RGR'us (7/12/2011)
Ignore the Good Enough Plan Found comment, Gail had more info than me on that topic. Bottom line is that this is not an issue.I've read a really good article on paging but I can't find it atm, Gail??
If you're new at this you really should get an expert in right away. This is far from a simple project.
In what region of the world are you? Maybe I can send recommendations.
I could also VPN in if you can work this way.
Regardless of Good Enough Plan comment, what did you use to analyze the plan ? Is it something I can use on my side for future reference ? It never hurts to learn something new... 🙂
I am in Portugal, do you know someone nearby ?
Regards,
jm
July 12, 2011 at 8:21 am
I don't know any Portugal DBAs.
Here's what I used. Keep in mind that it still takes a deep understanding of the product to make most uses of this product.
http://www.sqlsentry.net/plan-explorer/sql-server-query-view.asp
July 12, 2011 at 8:42 am
Ignore the 'good enough plan' stuff. It's a mile deeper than where you are now.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 12, 2011 at 8:45 am
Thanks for your help guys.
I will try to dig deeper into this, but I will also try to find a DBA to help me.
Regards,
jm
July 12, 2011 at 9:35 am
Actually there's one more thing I'd like to pick your brains with. On the post that started this thread I mentioned that I looked at waits and wait types on my system. I think I know why most of them are happening but there's one type that puzzles me...
I have two kinds of waits that make up for about 80% of all waits: OLEDB and CXPACKET. The average wait time for these is not a lot though (0.004 seconds, at most). There's also another one - LCK_M_X- that happens about 5% of the time but when it does the average wait time is about 1 second.
CXPACKET, as I understand, is related to paralellism issues on some queries. I do have some queries that return several result sets, usually a big result set and some smaller ones, so they may be to blame.
LCK_M_X is related to locks. I'm using MSDTC and COM+ on the business level tier, and no one has configured transaction isolation level for any of my components (it's the default for COM+ which is SERIALIZABLE), so that may account for the LCK_M_X waits.
What I don't get are OLEDB waits... The documentation states that this type of waits happens when a process is waiting on a OLEDB resource, but I don't know of my database accessing any OLEDB resource other than a linked server, and even that only happens when a user does a full text query on a Indexing Services Catalog and we join the results with some data from the database, which in this case does not happen at all because the users on this client don't use that type of searches. So what can be to blame for the OLEDB waits ? They account for about 60% of all the waits on the system... 😐
I don't really get it...
Anyway sorry for picking your brains once again, I apologize if my questions seem basic.
jm
July 12, 2011 at 9:43 am
It's not basic at all actually.
I'm no expert in waits but I'll hang around if I can help more.
What's the normal use of the system? OLTP, OLAP, both?
What's the cost threshold for parallelism setting atm (you can find out with sp_configure using advanced options)?
July 12, 2011 at 9:51 am
Ninja's_RGR'us (7/12/2011)
It's not basic at all actually.I'm no expert in waits but I'll hang around if I can help more.
What's the normal use of the system? OLTP, OLAP, both?
What's the cost threshold for parallelism setting atm (you can find out with sp_configure using advanced options)?
Normal use for the system is OLTP.
The Max Degree of Parallelism setting is 0.
The Cost Threshold for Parallelism setting is 5.
Locks setting is 0, and Query Wait setting is -1.
Regards,
jm
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply