November 5, 2003 at 12:21 pm
I have 16gb in 190 DB's running on 2 xeons w/ 2gb RAM. Same query against same db on a differenct SQL instances on the same box with only 1 db takes 1 second vs. 26 seconds on the big instance box. letting both dynamically manage memory between 1GB and 2GB. Do I need more RAM??
November 5, 2003 at 12:37 pm
Run performance monitor to verify how much memory has been allocated to each instances. The query running fast in the instance with one database may due to all data the query needed have been cached into memory of that SQL Server instance. On the other instance that has 190 database, you have insuuficeient memory and that query has to access the data in the disks to run. Check the physical read of Query execution plan on both queries will give you some useful information.
SQL Server: Memory Manager: Total Server Memory (KB) - Total amount of dynamic memory the server is currently consuming and Target Server Memory (KB) - Total amount of dynamic memory the server is willing to consume.
If Target Server Memory (KB) is greater than Total Server Memory (KB), which indicates more memory is required.
Edited by - allen_cui on 11/05/2003 12:42:06 PM
November 5, 2003 at 12:59 pm
Avg Total = 1389568
Avg Target = 13945438
So I am short on RAM(?)
November 5, 2003 at 1:46 pm
Don't quite understand your reply. Can you provide the answers to my questions?
Reconfigure memory allocation to these two instance may help improve the performance, for example, give less memory to instance that has one database and allocate more memory to the instance which has more database.
November 5, 2003 at 2:07 pm
In addition, you should set memory amounts with multiple instances. The auto management can be too slow to respond to changing conditions.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
November 5, 2003 at 2:15 pm
Actually, I only set up the second instance to test perfomance of the query on a DB all by itself. I don't plan on running both. But with the same server load, query against the DB on the 1 DB instance = <1sec on the 190 DB side 26+/- sec. I appreciate your comments and help very much!
November 6, 2003 at 3:44 am
Sets see : 1 instance with 1 db returns in 1 second.
1 instance wit 190db's takes way longer.
Your 1-190-instance has to manage 190 db's (with at least 2 files) and gets 1.3Gb.
Are the other databases (all) open ?
What volume do they take ?
How intense is the queryplan ?
Are queryplans equal in both cases ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply