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 ?

  • How big is your database? – Greg Askew Jul 5 '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 5 '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 5 '12 at 13:44

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

  • +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 5 '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 5 '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 5 '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 5 '12 at 14:19
  • Ah, entirely new information after the fact - always a pleasure! – adaptr Jul 5 '12 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.