December 6, 2002 at 7:28 pm
We are new to using sql 2k instances but we want to try them as a first step toward sql server consolidation. Surely, the instances must compete with each other for real resources (i.e. RAM, hard disk, etc). What performance monitor metrics should I watch out for? Should I auto9matically restrict the amountof RAM each instance has access to?
TIA,
Bill
December 6, 2002 at 10:14 pm
The memory manager in SQL2K is supposed to handle memory across multiple instances reasonably well, but to be honest, my shop has not put it to the test. We have multiple instances only in the case of clustered servers (active/active) and we've got enough RAM that memory isn't a bottleneck.
You can track each instance's Target Server Memory (KB) and Total Server Memory (KB) under the Memory Manager object to see how the instances are doing. You should be able to tell after a couple of days of baselining if the instances are reaching a happy equilibrium. If they aren't, you'll probably have to set memory based on how much each one should require. The target and total memory amounts will give you an idea of how to split the memory.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
December 7, 2002 at 5:12 pm
First download the updated SQL BOL for most current information. But in Indexes type in "instances of SQL Server" and choose the "memory usage" subitem. There you will find additional info on what happens with memory. Especially read the item "Dynamically Managing Memory Between Multiple Instances" which talks about how they are controlled by a mechanism that aims for an equilibrium between them to prevetn too many resource thefts.
December 9, 2002 at 9:04 am
Besides having enough RAM, which I think you have enough info to investigate about (what out for paging if you modify min/max mem settings), I would also monitor disk utliziation. Look at counters like sec/Read or sec/Write and any of the queue length counters. Make sure logs and data as well as tempdb (if used heavily) are separated.
December 9, 2002 at 9:35 am
quote:
First download the updated SQL BOL for most current information.
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
enjoy your 38.5Mb
December 9, 2002 at 12:21 pm
If you've not grabbed the updated BOL, the 38.5 MB DL is completely worth it.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
December 9, 2002 at 4:39 pm
Yes there are some major errors in the original documentation.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply