May 4, 2011 at 12:58 am
Oh, Thanks for your kind replies. I should consult with a DBA or with a network admin?I'm a Network admin and need the help of a DBA.
I'm asking question in this forum because I want someone to show me the way of solving problem. If there's a need for counters , OK, what's needed, I'll do that.
May 4, 2011 at 7:18 am
You haven't necessarily defined a problem that well so far and there have been some questions asked that you haven't answered.
I would look at Target Server Memory for all instances as well as Total memory for them and the page life expectency. That will give you an idea of how well they are using memory or how much they are short.
In terms of CPU, you can try to balance out the CPUs for each instance by using affinity, but the distribution depends on how important the instances are. I might start by giving each one 4 cores and seeing what happens.
May 4, 2011 at 7:53 am
I might also recommend checking the max degree of parallelism across the instances. If each instance has numerous queries constantly going parallel, this will quickly consume whatever CPUs are assigned to the instance.
May 4, 2011 at 10:26 am
Can you check which SQL Process ID is consuming all of the CPU, or if it is a combination of two or all three...?
Just out of curiousity did you upgrade the versions of SQL Server - so you have run an update stats or rebuilt the indexes post migration.
Obviously dodgy disks and a lack \ misconfigured memory could be a cause, but you need to look at the number of processors on your old boxes, speed of the cores, how much memory was allocated on your old instances.
May 5, 2011 at 7:22 am
sqlbuddy123 (4/30/2011)
Coming to CPUs, you need to use Processor Affinity to dedicate processors to the SQL Server instances.
+1
Do not use the automatically set processor affinity checkbox; assign each instance its own CPU cores/hyperthreads, preferably from the same physical socket/NUMA node. Try using Coreinfo to figure that out.
May 8, 2011 at 5:45 am
Thank you for all responses friends.
You gave me hints and I found 2 good resources which let me learn considerations when installing multiple instances:
http://technet.microsoft.com/en-us/library/cc917532%28printer%29.aspx
pro SQL server 2008 failover clustering (chapter 5)
They were very useful to me. I learned that when using multiple instances using affinity is not a good practice, instead I should use WSRM....and many other useful hints.
Have Fun
Parissa Bakhshi
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply