2

We have built a new 2 CPU, 6 core server, with 96GB of RAM. SQL server Enterprise Edition was configured with a maximum memory setting of 92GB.

A few days after adding into the load balancer I have noticed that perfmon says that only between 45-46GB of memory is being used.

SQL server reports it can see 98GB of ram on the server and windows is reporting 98GB of RAM in the server. The figure is suspiciously close to half the RAM though and task manager on the server normally reports the maximum memory setting configured in SQL server when running.

Any suggestions ?

Chris Wood
  • 133
  • 5
  • How big is your database? – Greg Askew Jul 05 '12 at 12:26
  • I have a couple on there, 1 is 200GB the other is 52GB, the 52GB one is currently being heavily used and I also have been running REINDEX jobs overnight as well as data compression over most of th 200GB database so I'd be surprised if it only really needed 45GB max at all times – Chris Wood Jul 05 '12 at 12:37
  • Depending on multiple factors, an index rebuild will simply stream to tempdb, not consume precious memory. You do know that SQL Server has its own memory management system , yes ? – adaptr Jul 05 '12 at 13:44

1 Answers1

5

Apparently, your dataset requires no more than 46GB of memory.

adaptr
  • 16,576
  • 23
  • 34
  • +1. ;) Help, I got a truck now the loading area is always empty when I go shopping ;) If you do not need the RAM, even SQLL Server will not use it. – TomTom Jul 05 '12 at 12:13
  • @TomTom thanks for the *smart* comment but the workload requires it, the server is used for 2 db's one being over 200GB, the other 52GB. Tempdb is heavily used by our APP Fabric configuration. I wouldn't have posted here if everything simply fitted into memory – Chris Wood Jul 05 '12 at 12:43
  • Dude, the SIZE OF THE DATABASE IS IRRELEVANT. In most databases the working memory set is a lot smaller and most data is rarely ever used. The worst I have seen was a 150gb database using 4gb server memory - because the active set was that small, the rest was archived data. – TomTom Jul 05 '12 at 12:51
  • the smaller db is a replicated with massively hit tables, for one reason or another a lot of these require scans, so i'd imagine virtually all of this would be in memory. That is just the small db, the larger db is not nearly as frequently hit but still requires a decent amount of memory, 1 hour after installing the memory is was using the max, 2 days later its only using half.. doesn't sound right – Chris Wood Jul 05 '12 at 14:19
  • Ah, entirely new information after the fact - always a pleasure! – adaptr Jul 05 '12 at 14:21
  • sometimes its hard to know what facts to included. I was almost certain it wasn't the load and just looking for some possible thoughts on why the server would only use half the memory, but not throw an error. apologies if this wasn't enough but it may be more constructive for everyone to spend a little more time helping rather than attempting to tear strips off people. – Chris Wood Jul 05 '12 at 14:29
  • The fact that this machine HAS, in the past, actually used all of its physical memory is pretty damn critical information when that is what you're asking about! – adaptr Jul 05 '12 at 14:31