We recently purchased a large server (w.r.t our company size) consisting of 4 socket x 8 core x 2 hyper threading (64 logical cores) and 256 gig RAM. The idea is to install multiple SQL 2008 instances to support different application and assign processor
and i/o affinity for each instance.
We have installed our first such SQL serve instance and i need some direction with configuring and setup of cpu and io affinities. For our first instance we have decided to assign 32 processors to this instance and leave i/o affinity as auto. I have read
this isn't advisable since we could have situation where the same processor and io assigned to same cpu causing performance issue. Below is a snapshot of how i have it setup. We have a total of 8 NUMA grouping and i am alternating the cpu per ea numa so as
to balance the load.
1) is this advisable setup? Or should i uncheck auto i/o affinity. Example i would check cpi1, cpu3, cpu5...for i/o affinity. Basically not sharing same processor for both processor and i/o affinity?
2) Once i install a second 2008 instance and would be assigning processor and i/o affinity as well..basically utilize the remaining 32 cpus. This is where my confusion is. On second instance i would be selecting and assigning cpu for processor and i/o which
are different form first instance correct? For example for new instance i would assign cpu1, 3, 5, ...for processor and cpu0,2,4... for i/o. With this setup am i gaining anything?
3) We are noticing high disk os time with certain drive specially the drive containing the tempdb and we have a total of 16 tempdb. Is the high os disk time due to current i/o affinity set to auto ?