Solved

SQL 2000 standard edition - 2GB RAM limit on service - how big a DB before need to upgrade to Enterprise Edition????

Posted on 2004-10-18
11
293 Views
Last Modified: 2008-02-01
Hello,

My company has SQL 2000 standard edition, loaded on win2k server, standard edition. my live database is currently 4982 MB in size. i understand that SQL 2000 standard edition will only allow a maximum of 2GB RAM allocated to itself regardless of how much RAM it really needs.

My question is, at what stage [size] am i going to out grow SQL standard edtion? my DB is nearly 5GB. i understand that we dont access the entire DB at any 1 time. concurrent users = 50ish++. the SQL process uses around 1.8 to 2GB RAM.

any users had experience here? what should i be looking for? users to complain its slow?? they already do! am i currently being limited?? im not sure
0
Comment
Question by:acetate
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 4

Assisted Solution

by:eclipse2k
eclipse2k earned 125 total points
ID: 12337299
we also use the standard edition. we have quite large databases (up to 14GB w/ all indexes) and the database is still running very fast.

i am sure that the needed amount of RAM is not depenent of the final size of the database but rather dependent of the average size of the resulting record counts, average sizes of temporary tables or variables you are using in your procedures, and finally the amount of simultaneous requests resp. the number of simultaneous users for example.
0
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 125 total points
ID: 12337348
It seems to me you're looking in the wrong direction.  For databases below 50Gb there is no direct  stable relationship between RAM usage and database size.  As a DBA, I have administered above 40Gb databases that use on ly 1GbRAM and allow the handling of uyp to 350 simultaneous connections with pretty decent througput.  Rather than working on the hardware part, I believe you need some serious SQL Tuning.  Proceed as follows:

Identify ressource consumming requests
Identify parts of the structure that can be improved
Use efficient encapsulated TSQL (use stored procedures exclusively)
Use smart indexing (Index Tuning Wizard)

Hope this helps...

Racimo
0
 
LVL 4

Expert Comment

by:eclipse2k
ID: 12337350
when you experience speed problems, then you should look for another reasons. 2 GB of RAM should still be really enough for less then 5 GB of DB size... i am not sure about the user amount though, we dont reach 50 simultanous users.

- do you have a lot of smaller tables or less, bigger tables?

- did you check all optimization possibilities? like indexes?

- the users could be slow down when using wrong transaction isolation levels, for example waiting for read commited queries?


eclipse2k
0
 
LVL 13

Assisted Solution

by:danblake
danblake earned 125 total points
ID: 12337703
I've managed a 100Gb databases+, sitting on 2Gb ram, with about 100 usrs.
It does depend on exactly how the system is used and what you are trying to do.

Prior to upgrade ensure that some tuning is performed (You will find that running Enterprise Edition introduces extra advantages into the product and speed enhancements above std edition) so you can justify the additional expenditure.
0
 
LVL 1

Assisted Solution

by:DaveyByrne
DaveyByrne earned 125 total points
ID: 12338169
I find that from a programming point of view there are a couple of things that you can do to dramatically improve database speed.

Use Table Variables instead of Cursors and #Temp tables.
Never use Views.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:acetate
ID: 12343342
Hi guys,

thanks, i have been totally on the wrong path with this! thanks to all of you for turning me around. Our database is for Microsoft Navision Axapta ERP, so the database is created upon deployment. i have run the index tuning wizard and it didnt make any changes to the index's. 1183 tables, mostly small tables with a few large tables. I will follow your advice and look at the all the other options.

Do any of you have any more advice for me?

thanks
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 12345470
acetate,

The best additional advice I can give you is never to believe there is one unique solution for all databases.  Each database is unique and require to sit down and think to what you  want to do to give it more oxygen.  good luck with the rest of the project.  

DaveByrne
I am curious on what ground you declare never to use Views.  I have been using views for a long time and there are certainly a major way to do tuning...

Hope this helped...

Racimo
0
 

Author Comment

by:acetate
ID: 12345480
thanks guys,
0
 
LVL 1

Expert Comment

by:DaveyByrne
ID: 12355874
Well my dislike of views is purely practical, and more about human nature than programming.

It stems from what people with no experience can do with them - obviously in the right hands they are a valuable tool, but sadly they're so easy to use that most people who fancy themselves as programmers start changing them and creating new views based on other views, and joining on them etc. Eventually the whole database can grind to a halt and nobody knows how to dig down into the structure.

I'd say that 80% of the problems I've seen with clients' databases have been as a result of views - or at least impossible to diagnose because of a crazy view structure.

Just yesterday I had to help a client with one of their databses (not my database!) because a select query from a view joined to some tables was causing their Tempdb to grow to 6 gigs in a matter of minutes... the system was hanging.

It came down to not being able to include a "LIKE '%XYZ%'" with a "=7" in their WHERE clause...

Go figure! It was completely baffling. (although not surprising since the database wasn't even normalised)

So I just giggled audibly and re-iterated my mantra of not using views and quietly returned to my project... hopefully they can dig down and work out why it's happening.

I realise that there are ways to tweak and tune views for optimum performance, but I still see them as an expensive overhead that can be avoided by writing lean, mean stored procedures. Also, by using stored procedures you can control better how people read data from your database - if you give them a view they can run amok (it's the same as giving them access to the tables) but if you just let them execute some stored procedures you can be sure that you're in control and the "I'm a bigtime programmer" brigade can be kept out...
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 12356348
Your point of view is very interesting and expresses lots of experiences (rather negative it seems..) with views.  Therefore I understand and respect your point of view.  I have had different experiences with views.  Let me share with you some of it.

Working in the media imaging industry and datamining sector, I use to think that administratively speaking, putting everything into packed up - stored procedures , would provide me with better control for the administration performance.  I was right in part.  Coming from mainframe systems, I felt confortable with limit the number of sp and making sure to encapsulate pretty much of the business logic used.  As a result, I felt more secure about what and when processes would run on the overall sytems.  I could make sure that everything ran smoothly and ended up tuning the whole system within couple monthes.  Everything was running smoothly until we had to scale up as we changed our business offer.  As the offer changed and new dev people started getting in, we have had more problems into sharing workload of development because we have had first to break up the code.  During that time, I had to work about 70 hours/week during 8months to explain to the teams onto how they could take away parts without dupplicating processes and letting the legacy processes run smoothly.  As I believe that developpers assistance is a part of DBA job, I believe it would have been easier for me to give them chunks of business logic packed up in views(cataloged or not).  Sure it is not as clean as I would have wanted, but this is an example of how using exclusively putting code into stored procedures can make your administrative job harder.  As to the people you mentionned("I'm a bigtime programmer" brigade), even though I can't stand them either, I still believe they helped change my point of view.  There's probably no real perfect answer, but my experience is that it is possible to keep in control of databases even though you use a wider variety of tools to store data and code.  You sure have to make adjustments (do some extra administration, like keep an eye on tempdb) but the feeling of flexibility within the overall databases is very rewarding.   Try it and let me know...(Still keep some silver bullets against "I'm big time programmer brigades").  Thank you for sharing your experience and take care.

Sincerely,

Racimo
0
 
LVL 1

Expert Comment

by:DaveyByrne
ID: 12356415
You too Racimo...
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now