July 5, 2011 at 11:48 am
Yeah, I agree,
The unionall plan is attached...it looks MUCH better....2 seeks.
July 5, 2011 at 11:58 am
as2higpark (7/5/2011)
Yeah, I agree,The unionall plan is attached...it looks MUCH better....2 seeks.
Can you post the statistics IO?
Also why do you even join in there? It seems utherly useless!
select candidatew0_.userWorkHistoryID as col_0_0_
from schema.BH_UserWorkHistory candidatew0_
left outer join schema.BH_UserWorkHistoryCustom candidatew0_1_
on candidatew0_.userWorkHistoryID=candidatew0_1_.userWorkHistoryID
where (candidatew0_.userWorkHistoryID=1231324
)
and candidatew0_.isDeleted=0
July 5, 2011 at 12:00 pm
If for some odd reason you need to push this even way further you could consider add or changing the current index to add the filter deleted = 0.
Should save even a little more on the reads side of things. Tho at this point I'm not too sure I'd go this far for a little extra gain.
Reset the waitstats when you put this in prod and keep us posted on the cpacket waits. With dop at 1 you should see a major difference if this is the #1 query in your system.
July 5, 2011 at 4:34 pm
It's always good to see these threads come together and a solution come to light. I am very curious now, on your old server how were you not seeing these same performance issues. Good luck rolling this intro production and being able to breathe again 🙂
July 5, 2011 at 4:46 pm
patrick.doran (7/5/2011)
It's always good to see these threads come together and a solution come to light. I am very curious now, on your old server how were you not seeing these same performance issues. Good luck rolling this intro production and being able to breathe again 🙂
Might be as simple as the 3 ghz procs hid the problem (requiring less parallelism in the plans). Or the fact that they decided to upgrade the server because of slowish performance was not the best decision at the time... and now the real culprit has been exposed.
More cpus also mean extra overhead to pull the streams together (as seen in cpacket waits) which might not help at all to have more cpus.
Could be lower cache (or insufficient cache on the procs) or less performance firmware of the cpus.
Could also be that windows 2008 R2 needs more ram or deals with it differently... or a missed setting on the server. Or the link to the san behind sluggish. I once had a similar issue where everything was the same except the windows version and some small rule change in the handling of something caused our linked server to crash. Could be the same thing here too.
Missing update or setting on the sql side?!?!
It's really hard to tell without side by side tests. :hehe:
July 5, 2011 at 4:57 pm
patrick.doran (7/5/2011)
It's always good to see these threads come together and a solution come to light. I am very curious now, on your old server how were you not seeing these same performance issues. Good luck rolling this intro production and being able to breathe again 🙂
P.S. Thanks for dropping by. I had never seen / grasped the DOP parameter in the plan. It'll surely be invaluable for me in the future.
Tx 1 M again! :hehe:
July 6, 2011 at 12:50 am
July 6, 2011 at 5:27 am
Yup timely is definitely the word.
She makes a good point about making sure to not return duplicate.
I don't know about the avg # of rows returned but I would probably avoid the UNION here simply by adding a ID <> @Param in the 2nd query. That would avoid the costly union operator. I know the OP has talked about completely splitting the logic but I don't know what is the word on this one!
July 6, 2011 at 5:31 am
Holly crap, and it's in the newsletter too for a 3rd site :w00t::hehe::w00t::-D
http://www.sql-server-performance.com/2011/union-or-sql-server-queries/
July 6, 2011 at 11:00 am
Wow...I might just have to use this as Evidence Item #1 when convincing Dev to change the code....
Thanks everyone for your contributions, when we have a fix, I will definitely report back.
July 6, 2011 at 11:05 am
as2higpark (7/6/2011)
Wow...I might just have to use this as Evidence Item #1 when convincing Dev to change the code....Thanks everyone for your contributions, when we have a fix, I will definitely report back.
At this point you should already have all you need.
You ran a trace and proved that this query is the #1 cpu eater and takes n% of the whole server's cpu ressources.
You changed the code slightly and by doing side by side runs you prove than it now runs 100 times faster and takes 99.5% less cpu ressources (use the real numbers).
All that's left to do is change 5 lines of code and retest QA before going to prod which takes time but would defenitely help aleniate the issue you are having.
Of course to completely solve the issue I'd expect possibly 5-10 queries to need work on (might just be changing 2-3 indexes on some or nothing can be done on others). Usually the top 3-5 queries will eat out a crapload of ressource compared to the rest (80 / 20 rule).
July 7, 2011 at 8:56 am
On the new box have you set the lock pages in memory option? It is a user right granted to the SQL Server Service account.
FisherDad
August 1, 2011 at 7:44 am
To all those who helped, we did lick this one for our affected DB, but with results that we are not super happy about.
We finally moved the DB back to its original host that had been rebuilt with Windows 08 R2 and the issues disappeared.
This shows, with our current configs, the Intel Xeon 5450 (2 x 4 cores @ 3.0GHz) seems to handle our workload better than the AMD 6172 (2 x 12 cores @2.1GHz). Unfortunately, this was a live box, so we couldn't do extensive live testing of further changes.
We have planned a full scale test of a new box to see if we can make changes to the AMD boxes to bring them in line. Planned changes are:
- Disable the AMD's power-saving settings in the BIOS
- 2 MS Hotfixes that "may" affect performance on Windows 2k8 R2.
- Lock pages in memory setting
- Possible upgrade to SQL 2k8 R2
- Whatever else....
Just wanted to say thanks to those that helped. The first part of this forum did help us to remove some nasty bad queries that helped perf on the AMD and also translated to better perf back on the Intel Procs.
I have found a few more forums where people are seeing issues with the AMD procs and other NUMA procs, so I will report back any findings.
August 2, 2011 at 8:27 am
as2higpark (8/1/2011)
To all those who helped, we did lick this one for our affected DB, but with results that we are not super happy about.We finally moved the DB back to its original host that had been rebuilt with Windows 08 R2 and the issues disappeared.
This shows, with our current configs, the Intel Xeon 5450 (2 x 4 cores @ 3.0GHz) seems to handle our workload better than the AMD 6172 (2 x 12 cores @2.1GHz). Unfortunately, this was a live box, so we couldn't do extensive live testing of further changes.
We have planned a full scale test of a new box to see if we can make changes to the AMD boxes to bring them in line. Planned changes are:
- Disable the AMD's power-saving settings in the BIOS
- 2 MS Hotfixes that "may" affect performance on Windows 2k8 R2.
- Lock pages in memory setting
- Possible upgrade to SQL 2k8 R2
- Whatever else....
Just wanted to say thanks to those that helped. The first part of this forum did help us to remove some nasty bad queries that helped perf on the AMD and also translated to better perf back on the Intel Procs.
I have found a few more forums where people are seeing issues with the AMD procs and other NUMA procs, so I will report back any findings.
It just pains me to see people spend DAYS going back and forth on a production problem such as this when getting a qualified consultant in for an hour or two would have made all the difference! The 1st three of those things up there would have been on my initial "check this now" list, along with IO system configuration for new machine and a whole host of other checks.
A review of some of the information on this link (and links it contains) suggests that there was definitely something wrong with your server's setup, configuration (or perhaps hardware itself, although that is very unlikely): http://blogs.amd.com/work/2010/04/01/sleepless-in-seattle-%e2%80%93-launching-the-amd-opteron%e2%84%a2-6000-series-platform/
Glad you are finally back to running successfully, and good luck getting the new machine tested and configured!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 8, 2011 at 2:25 pm
Just wanted to swing back and close the books on this issue. We determined that there was a default BIOS setting that was causing the performance issues.
Supposedly, these new AMD chips have a "green / eco-friendly" setting which is usually disabled on servers, but was enabled on ours. This was causing the chip to not run at full capacity 100% of the time. Since we have disabled this setting, the performance issues have gone away.
Thanks again for all that contributed.
Viewing 15 posts - 76 through 90 (of 94 total)
You must be logged in to reply to this topic. Login to reply