September 23, 2009 at 9:38 am
First of all I'm a novice when it comes to particulars about servers. My boss has asked me to research what implications there are for a SQL Server 2005 application (in our case the building of a cube from large data mart tables) to use only 1 CPU.
From what I can gather the boss is thinking of freeing up the other CPU's on the server to process data warehouse ETLs and querying.
Some obvious implications of using only 1 CPU would be :
application or cube may run longer
it won't run in parallel
it shouldn't affect processes running on the other CPU's and vice-versa
can anyone add any other impacts?
Thanks!
September 23, 2009 at 10:26 am
Can you provide a little more detail about your CPU please?
What is the CPU model that you intend to use?
Are you referring to a single "Physical" CPU or a core? If a physcial CPU does it have multiple cores etc.
Off the bat though, I think you will struggle creating any sort of robust ETL solution using a single CPU.
September 23, 2009 at 12:36 pm
Here's what I've been able to gather about our CPU:
cpu hyperthread physicalphysical
countratio cpu countmemory
164432762
SERVER: DELL POWEREDGE R900
SYSTEM: x64 based PC
PROCESSOR: EM64T
TOTAL PHYSICAL MEMORY: 32,762.03 MB
TOTAL VIRTUAL MEMORY: 31.84 GB
September 23, 2009 at 12:38 pm
To make this more readable:
cpu count 16
hyperthread
ratio 4
physical
cpu count 4
physical
memory 32762
September 24, 2009 at 6:10 am
Can anyone help out with further implications? Thanks.
September 24, 2009 at 8:34 am
Such an association between a thread and a processor is called processor affinity.
Although efficient from an operating system point of view, this activity can reduce Microsoft SQL Server performance under heavy system loads, as each processor cache is repeatedly reloaded with data. Assigning processors to specific threads can improve performance under these conditions by eliminating processor reloads and reducing thread migration across processors (thereby reducing context switching).
September 25, 2009 at 6:16 am
If your ETL processing is just one query among many others on the same SQL instance, you cannot restrict it to using a single CPU.
The best you can do is use the hint MAXDOP(1). However, MAXDOP only limits the number of CPUs that can be used for each portion of your statement. If your query plan has many branches, a separate CPU can be assigned to each branch so that multiple branches are active at the same time.
If your ETL is the only thing that is running on your SQL instance, you can set processor affinity for that instance to only use 1 CPU.
Whichever approach you take, it is worth doing some performance testing to see if it achieves the business objectives. (The objectives are most likely based on overall throughput or on the speed of the non-ETL queries, not on the technical ability to only use 1 CPU.)
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply