March 22, 2011 at 2:14 am
1. Is there any performance benefit of segregating database files on different logical drives (physically single storage medium)? What general strategy should be?
2. "If you use a modern Intel processor (such as a Xeon X5680 or Xeon X7560) you should strongly consider disabling hyper-threading, since long running queries do not perform as well on a hyper-threaded cores in a processor."
What are the implications of above statement?
3. How to get max from modern multi-core processors?
4. Databases upgraded from Single Processor Environments do degrade performance in some situations, due to Parallelism factor. And restricting such queries to single thread (MAXDOP) produce good results! Do we need to tweak Processor Affinity or I/O Affinity settings at server level or any settings need to be adjusted?
Any concise Feedback?
Thank you
March 22, 2011 at 2:56 am
Abrar Ahmad_ (3/22/2011)
1. Is there any performance benefit of segregating database files on different logical drives (physically single storage medium)?
In general, no. You need separate spindles to get IO-related gains
3. How to get max from modern multi-core processors?
Err, don't understand the question
4. Databases upgraded from Single Processor Environments do degrade performance in some situations, due to Parallelism factor. And restricting such queries to single thread (MAXDOP) produce good results!
Often those queries need optimisation. Other times maybe cost threshold for parallelism needs to be increased. Forcing queries onto one processor is not a good general approach.
Do we need to tweak Processor Affinity or I/O Affinity settings at server level or any settings need to be adjusted?
Leave the affinities at default unless you really know what you're doing and how the setting s work.
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
March 22, 2011 at 3:19 am
GilaMonster (3/22/2011)
Abrar Ahmad_ (3/22/2011)
3. How to get max from modern multi-core processors?
Err, don't understand the question
3. How to get maximum from modern multi-core processors [if for long running queries (and actually the performance counter is set to such queries in general :-)) we get a little degraded throughput then what should be optimum/good configuration, for average cases? Do we need to have hit and trial mechanism to judge optimum/good for each environment?]
Thanks Gila!
March 22, 2011 at 4:42 am
I still don't understand what you're looking for.
Write well performing queries and don't interfere with SQL unless you know what you're doing.
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
March 24, 2011 at 9:21 am
Abrar Ahmad_ (3/22/2011)
1. Is there any performance benefit of segregating database files on different logical drives (physically single storage medium)?
The only gain here is for administration purposes, no performance gain
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply