Russ Suter
asked on
How to preferentially optimize a SQL Server instance
We have a Microsoft SQL Server database server that has 5 instances running on it. They all work just fine for our needs but one database on one instance is a little performance sensitive. The virtual machine has 4 CPU cores and 64 GB of RAM allocated to it. I'm wondering if there's a way to optimize performance on one instance so it hogs maybe half of all the available resources and let the other 4 share the rest.
I should point out that this is a development and not a production environment. I wouldn't consider loading a server up with 5 instances in a production setting.
I should point out that this is a development and not a production environment. I wouldn't consider loading a server up with 5 instances in a production setting.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Dung Dinh
I'm not having any performance issues. I just want to configure the instance to ensure that performance is not a factor.
@pcelba
Thanks. I'm looking at those settings and I see options for processor affinity and I/O affinity. What do you recommend I set for those?
I've configured the RAM to use a minimum of 16 GB and a max of 24 GB. I don't see where to boost the SQL Server priority though. Where is that setting?
I'm not having any performance issues. I just want to configure the instance to ensure that performance is not a factor.
@pcelba
Thanks. I'm looking at those settings and I see options for processor affinity and I/O affinity. What do you recommend I set for those?
I've configured the RAM to use a minimum of 16 GB and a max of 24 GB. I don't see where to boost the SQL Server priority though. Where is that setting?
you can limit the cpus and ram used by OTHER instances in order to make sure this one has some resource left.
afaik there is no such thing as priority in he GUI.
likewise there is no way to set priority for I/O operations other than using dedicated disks for that instance.
that said, there is some concept of "niceness" in windows, and you can identify the process that runs that instance in the task manager and change it's priority manually. likewise if you can start that instance separately, you can use "start /HIGH mssql ...". i'm unsure that is feasible.
... it might be simpler to move that instance to a dedicated server
afaik there is no such thing as priority in he GUI.
likewise there is no way to set priority for I/O operations other than using dedicated disks for that instance.
that said, there is some concept of "niceness" in windows, and you can identify the process that runs that instance in the task manager and change it's priority manually. likewise if you can start that instance separately, you can use "start /HIGH mssql ...". i'm unsure that is feasible.
... it might be simpler to move that instance to a dedicated server
I/O Affinity was important on 32 bit systems so you can skip this setting. You may read more e.g. here: https://blogs.msdn.microsoft.com/psssql/2010/11/19/how-it-works-io-affinity-mask-should-i-use-it/
CPU affinity allows to preserve some CPU cores to given SQL Server engine and disable them for another instances. And yes, CPU affinity is also available in the Task Manager but the setting done in the task manager isn't permanent.
The Boost SQL Server priority is available as a checkbox on the same property page where CPU affinity can be set.
Maybe to boost the SQL Server priority for the sensitive instance could be enough. Of course, everything depends on the particular instance load. I would not port it to a dedicated server. Developers need to see how the server works when stressed...
CPU affinity allows to preserve some CPU cores to given SQL Server engine and disable them for another instances. And yes, CPU affinity is also available in the Task Manager but the setting done in the task manager isn't permanent.
The Boost SQL Server priority is available as a checkbox on the same property page where CPU affinity can be set.
Maybe to boost the SQL Server priority for the sensitive instance could be enough. Of course, everything depends on the particular instance load. I would not port it to a dedicated server. Developers need to see how the server works when stressed...
I suggest that you should monitor this instance with SQL Profiler or DMV in SQL Server to understand what causes.