April 5, 2011 at 10:18 am
Thanks so much for your assistance on this!
FYI - the server default for MAXDOP is 0 (on all our servers). With MAXDOP, I've always been under the impression that it being set to 0 was just as potentially dangerous as setting it to 1
Yes?/No?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 5, 2011 at 1:01 pm
Nah, 0 is fine, unless you have known issues or you're sharing the box across multiple instances, then you might want finer controls. Hyperthreading on the other hand has been known to cause problems and should be tested in your environment.
Hoping someone else can hop in with some ideas on the memory problems.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 5, 2011 at 2:57 pm
Jeff Moden (4/5/2011)
forsqlserver (4/4/2011)
Looping Myself...Please explain.
My guess is they are used to being added for immediate notifications when replying to a thread, so they added a reply to get notifications. (Rather than using the "Topic Options" link in the upper right corner to setup the notifications.)
April 5, 2011 at 3:12 pm
UMG Developer (4/5/2011)
Jeff Moden (4/5/2011)
forsqlserver (4/4/2011)
Looping Myself...Please explain.
My guess is they are used to being added for immediate notifications when replying to a thread, so they added a reply to get notifications. (Rather than using the "Topic Options" link in the upper right corner to setup the notifications.)
Heh: "Loop me in, Odd One."
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 5, 2011 at 4:44 pm
UMG Developer (4/5/2011)
Jeff Moden (4/5/2011)
forsqlserver (4/4/2011)
Looping Myself...Please explain.
My guess is they are used to being added for immediate notifications when replying to a thread, so they added a reply to get notifications. (Rather than using the "Topic Options" link in the upper right corner to setup the notifications.)
Now that you've said that, that makes perfect sense.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2011 at 6:53 am
Linked servers can be disatrously bad for performance. See what you can do to avoid those. I have helped many clients use replication to eliminate linked server needs. There are other solutions and also things you can do to improve perf if you must use them.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 6, 2011 at 10:30 am
In our existing architecture I don't know if we have much choice with our linked servers. The tables we have from one of the 3rd party DB systems doesn't have any primary keys (yuck*) and using SNAPSHOT replication to replicate 50+ million rows of data daily doesn't exactly sound like a good idea to me (but then again maybe it would result in some sort of performance boost 🙂
The reporting guys at my company created VIEWs in the past that comprised of 2-5 tables that often have 20+ million rows of data in each of the tables included in the view, they will often create a stored-procedure that SELECT's directly from a table and then JOINs to these "wonder" views.
It's crazy - so what other options would your reccomend?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 6, 2011 at 10:38 am
MyDoggieJessie (4/6/2011)
The reporting guys at my company created VIEWs in the past that comprised of 2-5 tables that often have 20+ million rows of data in each of the tables included in the view, they will often create a stored-procedure that SELECT's directly from a table and then JOINs to these "wonder" views.It's crazy - so what other options would your reccomend?
Yeah, that usually goes very bad once the data volume hits a tipping point. You can try using the REMOTE join hint in the queries using these "wonder" views if they are such that doing the work remotely makes sense.
From my experience the best way to deal with it is to find the SPs that are taking the longest, i.e. pulling huge data volumes from the linked server, and re-writing it to use OPENQUERY to do as much joining and filtering directly on the linked server instead of letting SQL Server decide and pulling too much data over the network. (I say that because there usually isn't enough time to fix them all, but getting the biggest ones and working down helps, and once they see the performance benefit from doing that they will sometimes allocate resources to update more if not all of them.)
June 3, 2011 at 6:05 am
The resource semaphore waits are usually related to 32bit SQL Server. I get high waits on my 32 bit servers due to the lack of adequate procedure cache available to 32bit versions of SQL. Resource semaphores are created when a new plan is waiting to get into the procedure cache while old plans are removed.
If you upgrade to 64bit, you open up about 7gb of space to procedure cache and this wait type will disappear.
I suspect you probably have a lot of ad-hoc or dynamic SQL that causes SQL to not reuse plans in the procedure cache. You might also look into BOL for Optimizing for Ad Hoc Workloads.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply