0

i built a new 2 cpu 8 core 40 gb ram Server for our ERP software which use Sql server 2008 R2 on Win server 2008 standart R2, in Sql There are 7 databases, 2 of them for Tests, 2 of them to keep company datas and other 3 for ERP core operations, but all of them are used for ERP software, there are also 7 jobs running (Time scheduled), and largest size of database is 470,88 MB so far, but Sql using half of the Memory 20 GB, i informed ERP software technicians, they told me this is usual and they limited SQL memory usage to 20 GB, that looks suspicious, i dont have much info about Sql, and i dont want to damage any Sql settings, i searched very much on İnternet they all say Sql Consumpt too much Memory, how much you give Sql use, is it true, is there anything that i can do with my limited knowledge about Sql, because there are 30 people in the company using ERP software and it slows down from time to time.

washaq
  • 21
  • 1
  • 1
  • 4
  • You do not trust the application vendor's assessment of database memory usage? Instead of rambling and conjecture, why not just ask for help with your SQL performance issue? – Greg Askew Jun 10 '13 at 11:15
  • i didnt trust them because they act like i know nothing about it, and they told me this is usual, they might be right about the knowledge i have but they didnt explain anything at all. – washaq Jun 10 '13 at 12:19

1 Answers1

1

Add memory?

Seriously. SQL Server standard uses all memory as cache it can get it's hands on - that is the standard configuration for a larger setting. sharing SQL Server with something else does not work in larger scenarios.

If you limit SQL Server memory - that is a setting on server level that can jsut be changed. THAT SAID: 20gb may or may not be ok - depending on the size of databases and the hot set (i.e. data being used by SQL Server actively - the cache does not go in and load data that never is touched). If that is the case - I suggest you go in and just put in a lower number.

http://msdn.microsoft.com/en-us/library/ms178067.aspx

has the configuration options. Can be that they did not set it to 20gb MAX but to 20GB - min and max - and as such SQL Server will take the 20GB regardless whether it makes sense. Log in with management studio, in the server node check the options, there is minimum and maximum memory settings - all explained in the link I provided some lines up. OBVIOUSLY if you have tiny databases then a minimum of 20gb makes no sense at all. And yes, half a gig IS tiny - 7 of them are still minimal. I think the ERP just preconfigured 20 as minimum and maximum that is why SQL preallocates 20gb.

How did you get a new 2 CPU 8 Core btw.-? That smells like quite outdated technology already, or you turned off hyper-v on quad core intel cpu's.

Btw., - unless you have a good disc layout that will always slow down SQL Server and the server. Higher load SQL is tricky to set up - people actually PLAN the disc layout to get best IO performance.

TomTom
  • 50,857
  • 7
  • 52
  • 134
  • it is true 8 core and Hyper-V turned off, i checked the Sql options, max server memory is limited to 21something this is default i think, that means ERP company did nothing just ignored me, there is no disk layout Planed, so what should i do shall i decrease the max memory at least half? – washaq Jun 10 '13 at 12:47
  • Well, simply said - databases size + 4gb is good enough for a lot. If your databases are 7x0,5G MAX then 20GB is excessive and 12 would suffice. – TomTom Jun 10 '13 at 13:01
  • +1 for bringing up the disk layout. My guess is the OP has more disk issues than memory issues. – Rex Jun 10 '13 at 14:20
  • It would be usual. Most people buying servers are woefully unaware what IOPS means and how to structure a database server. And of the costs - they read the Megabytes/second number of large discs, and see the price, and wonder why to buy small fast discs or SSD.... – TomTom Jun 10 '13 at 14:44