April 30, 2011 at 12:19 am
Hello,
I have a server running 3 instances of SQL on it. When I have each instance installed on a single physical server, I have no problem. But when I moved all of them to a high performance server, I encounter odd results in performance. All CPUs work in it's 100% usage!!!! Why should this happen, since this is a server with much higher performance and powerful CPUs than the addition of all 3 physical servers?!!!
Another question is : Does any one know of any good resource on multiple instances bottlenecks?
April 30, 2011 at 1:09 pm
This High Performance Server is a Physical server or a Virtual Host server . If virtual Host Server then you will definitely notice some degradation on the performance . Resource Management of the SQL Servers deployed on VM Host servers will be different than a Physical Host Server.
Whenever you are placing multiple servers on a single Powerful Server you need to share the resources like Memory and CPU among these multiple servers. You should not leave the default settings for the SQL Servers.
Say if you have X GB of RAM on the powerful server and 3 instances are placed on it then you assign the following Max memory to each SQL Server
Max memory for each SQL Server = (X- [2 to 4 GB left for OS])/3 GB
Coming to CPUs, you need to use Processor Affinity to dedicate processors to the SQL Server instances.
Now coming to 100 % CPU utilization, did your previous individual SQL Servers have any 100% CPU contention issues.
Also is your new Powerful Server has been assigned enough number of CPUs (Sum of the number of CPUs that the individual SQL Servers used to have ) ?
Are the SQL Servers SQL 2
Thank You,
Best Regards,
SQLBuddy
April 30, 2011 at 10:05 pm
I have a powerful physical server, not virtual. 3 instances are installed on it.
I've checked the "automatically set processor affinity mask..." check box. one instance is sql 2005 and 2 other instances are sql server 2008 R2. The server has no processor queue length issue. but processor time stays on 90%-100% when running some normal SPs.
May 1, 2011 at 1:50 am
Total Processors
Total RAM
Total Drives not partition
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 1, 2011 at 1:54 am
?What do you mean by this? No issues in RAM, Disk,....
May 1, 2011 at 1:53 pm
Syed Jahanzaib Bin hassan (5/1/2011)
Total RAM
Total Drives not partition
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
http://www.aureus-salah.com[/quote%5D
I'm not sure if you are asking for more information, but this is not a good way to post a question for more information. You seem to be assuming the poster can read your mind and understand what you want.
May 1, 2011 at 1:56 pm
A couple of questions. First, what is more powerful in terms of scale? How did the 3 servers CPU/RAM /Disk compare to the larger one?
100% CPU isn't necessarily bad unless it's sustained. If it goes on for more than a couple minutes often, I'd worry. Since CPU pressure is harder to get rid of in a new server since you just spend the money, I might look to better balance memory. I do like SQLBuddy's affinity selection. Might not alleviate this, but maybe it will. Do all three need to be equally balanced? All 3 equally important databases?
Make sure you have a MAX memory set for each instance. Potentially a minimum set as well. I don't think I have a great resource for tracking this and a lot of it is looking at target server memory as well as the page life expectancy and see how they compare.
For disk, look at virtual file stats, look for waits. Perhaps you have a lot of disk activity going on. Any flexibility with adding more disks?
May 2, 2011 at 6:44 am
Without more information it's hard to know. I'd suggest going after the wait states to see what's causing stuff to slow down. But you should also check that the system is configured correctly. There are lots of things that could be going wrong.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 2, 2011 at 10:13 am
Maybe your CPU utilization sucked on the old machines because the IO wasn't feeding them data fast enough and now on the new server it is. Outside of that there are about a bajillion things to check here. Get a professional on your system for a bit to give it a review and see what is really going on and why.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 2, 2011 at 10:45 am
parissa_bakhshi (5/1/2011)
?What do you mean by this? No issues in RAM, Disk,....
It should be noted that a lot of factors can affect your CPU usage. Including but not limited to RAM and disk space available. This is why everyone above has been asking questions about other factors on your server. In fact its possible that one of your instances is hogging more resources than it really needs on a regular basis, causing your other instances to use extra CPU.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
May 2, 2011 at 10:56 pm
I restarted the SQL server service. So it let the RAM to get free. I tested after it. I ran a SP with "order by" commands. The CPU usage reachd 100%.
- Available MB : 20000 out of 28000
- Context switches between 1000-70000 (I also turned hyperthreading off-no visible chage in counters)
- processor queue length ,% disk time and average disk queue length have amounts below the threshold. All works well.
The old PCs had core 2 due CPUs, where the new one is a server with 2 6 core Xeon CPUs.
Any further info?
May 3, 2011 at 6:58 am
I will give you the same info I did the first time I posted: get a professional on your system for a quick review. There are just too many things that could be at fault here.
I will ask if when you moved to the new server if you also upgraded to a new database version?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 3, 2011 at 8:36 am
I can run one query on my desktop and use 100% CPU. that's how SQL Server is designed. If it can use the extra CPU to process the query quicker, it will.
Context switches are relative, and dependent on the workload. The free memory should be low, though it's hard to tell based on a server restart what happened.
This can be tricky to diagnose and get multiple instances tuned. Ultimately you ought to get a consultant for a few hours to help you check on the setup and do some tracing/monitoring. I know many that work remotely and would help you in a few hours here and there.
Otherwise, you might try setting max memory for each instance, and min, and also thinking about affinity. If these databases were moved/restore, you might make sure that your indexes are rebuilt or at least you have updated statistics.
May 3, 2011 at 9:58 am
I can run one query on my desktop and use 100% CPU.
Yeah, but you write REALLY sucky queries Steve!! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 3, 2011 at 11:07 am
I probably do, but so the OP knows, plenty of queries on a normal system will spike at 100% CPU. That's not necessarily a problem.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply