Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2005-06-08 : 12:38:04
|
| When we have a physical server containing only one instance of SQL Server we typically set SQL Server to Dynamically Configure Memory and leave the min and max server memory options at the default values. I'm not sure if this is the approach to take when a physical server contains a default instance of SQL Server and multiple named instances. In this case should I continue to have SQL Server dynamically configure memory, but set a min and max server memory value per instance to ensure no one instance grabs too much memory?One other item of note, the servers with multiple instances are virtualized. Not sure if that makes a difference.Thanks, Dave |
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 2005-06-08 : 14:02:50
|
| It may be a good idea to manually allocate memory amongst the instances. The two don't talk to each other, and one could conceivably monopolize and choke the other. Allocate more to the more active system (common sense), although if you are aware of intimate details (such as tiny row sizes, few stored procedures, etc.) you can adjust accordingly.BTW: Make sure that there is a good reason for multiple instances (ie: NOT just to house another database). Each instance has significant overhead, and you will most likely get better overall throughput with a single instance. (Of course there are an abundance of good reasons to run multi-instance, but it is commonly done for the wrong reasons.) |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-06-08 : 14:23:40
|
| Thanks for the advice. We're running multiple instances to save on the cost of a physical server and unfortunately the instances cannot be combined into one instance.I asked about the memory recommendations because we recently moved our data center to a remote site. The development and test servers have been virtualized and I've been noticing performance problems. A DTS package that I use to rebuild dimensions and cubes took about 35 minutes to complete at our old data center. On Monday, at the new site, the DTS package completed in less then 35 minutes, but today I can't get it to complete. Each time I kill the process the log indicates progress has been made, however it is very very slow. I ran Perfmon and Pages/sec was averaging between 9 & 10, which indicates a memory problem. Total Target Memory was greater then or equal to Total Server Memory and Page Reads/Sec spiked to > 5.Thanks again, Dave |
|
|
|
|
|
|
|
|
Allocating memory for named instances