/3GB Switch Question

Question

/3GB Switch Question

Glenn Owens

SSCrazy

Points: 2099
More actions
August 8, 2006 at 9:07 am

#116043

I've got a situation where I have a fairly crowded database server (39 low-volume - both transactional and size databases). The server currently has 2GB of memory on a W2k OS with SQL Server 2000 SP4.
I'd like to add an additional 2GB of memory and set the /3GB switch. If I go ahead - I'm assuming that SQL will still dynamically manage it memory allocation across the 39 databases. Is there a way to assign more/less memory availability to the individual databases?
Thanks!
Glenn

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

The short answer: No.

You'd want to consider placing multiple instances on the machine if you wanted to be able to split the memory. Then you could give one instance more memory than the other and place the more high-use databases on that. The problem is that there is a certain amount of overhead with running multiple instances on a given physical machine. Not a whole lot, but some. However I'd recommend just adding the extra memory first and seeing how that works out. Monitor your memory counters and your buffer/cache hit ratio to see if you have a bottleneck and if it is memory.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Glenn

Also, you don't say whether you're using SQL Server 2000 Standard or Enterprise Edition. If it's Standard, you're stuck with 2GB whatever you do.

John

Glenn Owens SSCrazy Points: 2099 More actions · Answer 3

Aaron - thanks for the input.

I'm using the Idera Diagnostic Manager to monitor the server/database statistics (a very nice albeit expensive tool)... memory usage is pegged continually. The databases support some poorly written 3rd party applicaitons which make considerable use of cursors within the stored procedures along with a lot of AD Hoc queries. So I suspect that adding the memory can't hurt. But I do need to find a way to give priority memory to some of the more critical apps.

I had thought to add an additional instance or create a virtual server (VMWare) for the one application/database in question (no I don't have any apps on the database server).

Thanks again!

Glenn

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 4

We use VMware around here for a great deal of things. I would not recommend it for production database servers. For development and test it's fine, but we've experienced some problems with it and some performance issues that we can't track down.

Don't get me wrong, I love being able to set up a new dev/test machine in about 15 minutes, but I don't think the technology is good for production systems. That's my opinion, however.

As for the poorly written vendor applications... I know your pain. If they're going to overuse cursors there's not a lot you can do. John brings up a good point, however, that you should ensure you're using the proper versions of SQL Server in order to be able to gain anything from the switch.

Glenn Owens SSCrazy Points: 2099 More actions · Answer 5

Thanks!

I had looked at the question of the SQL Server version and got differing answers. If you've got a definative document that addresses the memory/version questions I'd really appreciate the link.

It looks more like I'll wind up creating a new SQL instance and portioning the memory and database migration.

I do have a couple of VMWare production servers. What we've found is that the virtual works well in situations where I/O traffic is not heavy. So yes... you're right about the use of Virtuals (especially in this situation).

Glenn

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Glenn

Not the version, the edition. You can get this by right-clicking on the server name in Enterprise Manager and choosing Properties. There are also functions in T-SQL you can use. With Standard, you're limited to 2GB. With Enterprise, you can use more, how much depending on operating system and whether you use the /PAE switch and AWE. I haven't got a link for all of this, but it should be fairly easy to find it on the Microsoft website.

John

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 7

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part2/c0361.mspx?mfr=true

Under SQL Server 2000 Standard Edition: 2gb of RAM max.

Glenn Owens SSCrazy Points: 2099 More actions · Answer 8

Yes - I just went through all of that with an Active/Passive SQL Cluster that we stood up and upgraded to 8GB of memory per server. There were a lot of learnings with the AWE +switches +MS required patches (most of which was "learned" at 3AM).

I was really hoping that this simple memory upgrade would be well... simpler. Oh well . I've inherited the database environment. Previously there was little maintenance, and no tuning done on any of the servers/databases... being a developer/systems architect by trade I'm slowly learning this new DBA role - thanks to folks like you and a lot of digging around for information.

Glenn