July 5, 2011 at 8:47 am
Looks like an estimated plan...
However 4 MLN rows from both tables is a huge load. I suspect it would run much better with a loop join. Can you force it and post the actual execution plan for the forced join query as well?
-- Gianluca Sartori
July 5, 2011 at 8:53 am
Gianluca Sartori (7/5/2011)
Looks like an estimated plan...However 4 MLN rows from both tables is a huge load. I suspect it would run much better with a loop join. Can you force it and post the actual execution plan for the forced join query as well?
I never used hints, ever. There's always (99.99999%) another to get there. And we're far from having to use that option.
Do you have the leeway to add a indexes in those tables? 1st look shows a pretty awefull plan (filter happens after 2 huge tables have been joined, much better if it happens before...).
July 5, 2011 at 8:56 am
Thanks guys, sorry for the need to obfuscate. This one just has the schema and DBname changed.
the index definitions are also included.
July 5, 2011 at 9:06 am
ooops...checking on something in Web Edition
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 5, 2011 at 9:10 am
** Sweet ** Plan Guides are available in 2008 Web Edition.
as2higpark (7/5/2011)
...Any ideas on how to deal with this offender or am I left to try to convince the developers to change it?
...
No, you're not stuck. You can use plan guides to coax a plan out of SQL Server that you like. You'll probably be most interested in "SQL Plan Guides".
Here is an article that will get you started. You're not the first one to have to deal with this problem (think third party software too) and this helps untie your hands. One of the examples is for getting a specific query to use a MAXDOP option 🙂
http://technet.microsoft.com/en-us/library/bb895390.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 5, 2011 at 9:10 am
If this doesn't work I'll ask you to try something new I haven't seen done here ;-).
I don't understand what the query does. What's the business requirement?
You select candidatew0_.userWorkHistoryID
But you already know the value >>> 980719
Seems like you can whack the join and maybe go for an exists or something.
July 5, 2011 at 9:12 am
opc.three (7/5/2011)
** Sweet ** Plan Guides are available in 2008 Web Edition.
as2higpark (7/5/2011)
...Any ideas on how to deal with this offender or am I left to try to convince the developers to change it?
...
No, you're not stuck. You can use plan guides to coax a plan out of SQL Server that you like. You'll probably be most interested in "SQL Plan Guides".
Here is an article that will get you started. You're not the first one to have to deal with this problem (think third party software too) and this helps untie your hands. One of the examples is for getting a specific query to use a MAXDOP option 🙂
Still way low on my options list.
Plan guides and hints are there where you're 100% out of every other options in the book.
Right now I don't even think the query makes sense so I'm waiting on more info!
July 5, 2011 at 9:25 am
Ninja,
The business use of the query is to keep two systems in sync. So the query runs (all the time) to check for the existence of rows.
The only thing that we can do with the query might be change it from a lookup based on VALUE1 OR value2 to 2 separate lookups, which would then be index seeks. The trouble with changing the query text is the time to change is about 1 week.
It is starting to seem to me that changing the sql might be my only real (longterm) option.
July 5, 2011 at 9:29 am
Another option you might want to try out.
Do Select @CandidateKey WHERE EXISTS (SELECT * FROM basetable where Candidate = @CandidateKey AND Deleted = 0 )
Make sure you have a covering index on Candidate + deleted (in that order). It Could even be a filtered index (deleted = 0). That would drop the reads dramatically on that query.
Then UNION ALL.
Select CandidateKey FROM BaseTable2 WHERE customText2=@Value
Index customText2 INCLUDE CandidateKey
Then use can use that query pretty much every way you want... check with exists, inner join (whacks the left join). Allows you to send to temp table to get even better stats before doing the join.
That might be enough to whack both scans and get seeks on both tables which would dramatically improve the perf.
July 5, 2011 at 9:32 am
as2higpark (7/5/2011)
Ninja,The business use of the query is to keep two systems in sync. So the query runs (all the time) to check for the existence of rows.
The only thing that we can do with the query might be change it from a lookup based on VALUE1 OR value2 to 2 separate lookups, which would then be index seeks. The trouble with changing the query text is the time to change is about 1 week.
It is starting to seem to me that changing the sql might be my only real (longterm) option.
We're clearly thinking the same way. If this is the #1 overall cpu killer, then refatoring the app after you've proven it solve the issue is definitely the way to go. Have a look at my previous idea. Make sure to use covering indexes in both tables and tune both queries separately.
Your stats really seem spot on. Keep up the good work there.
Once you get a new version post the new actual exec plans and we'll see if we can get more out of it.
I'd like to have the statistics IO on the old version and the new version too. I think this could really save work on the hds / cache as well.
July 5, 2011 at 9:40 am
Ninja,
On the second table, I have a covering index, but I can't seem to get it to not do an index scan, the definition is below, am I missing something on this index?
CREATE NONCLUSTERED INDEX [XIEUserWorkHistoryCustom_CustomText2_v3] ON [schema].[BH_UserWorkHistoryCustom]
(
[customText2] ASC
)
INCLUDE ( [userWorkHistoryID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
July 5, 2011 at 9:59 am
Just for the sake of trying something else I'd move the column out of the include into the index. Hoping that the distribution of the data in the stats change enough the give the optimizer a change to do a seek.
The full story (excluding my last desperate hope of a try).
http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
Plz post the actual execution plan for both version of the index for the new query.
July 5, 2011 at 10:06 am
Ok, I changed that and it used the new index, but it is still a scan....
I need to go fight with development on changing the code for a little bit. I will let you know if I am able to change it.
In the estimated and actual plans, doing them separate is like night and day.
July 5, 2011 at 10:12 am
as2higpark (7/5/2011)
Ok, I changed that and it used the new index, but it is still a scan....I need to go fight with development on changing the code for a little bit. I will let you know if I am able to change it.
In the estimated and actual plans, doing them separate is like night and day.
I'm not done tuning this... please post the new execution plan. There's no fight to have when the new version runs 100 times than the old one :w00t:.
If the new position of the included column doesn't help then it's probably better to leave it where it was (confirm that the page reads are lower when included and make a choice based on that).
July 5, 2011 at 10:26 am
Ninja,
Thanks and I love it. The new plan is attached.
Viewing 15 posts - 46 through 60 (of 94 total)
You must be logged in to reply to this topic. Login to reply