memory more than max server memory?

Question

memory more than max server memory?

david_wendelken

SSCrazy

Points: 2388
More actions
June 11, 2010 at 6:09 am

#225479

64bit system, sql server 2008, windows server 2008.
I set the max server memory property but my sqlserver.exe process is using all available memory instead of stopping at the max amount.
What's going on?
Would setting the AWE memory setting help?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What's the max memory set to, what's SQL using?

It is certainly possible, the max memory setting is for the buffer pool, SQL also uses memory outside the buffer pool for things like thread stacks, CLR, linked server drivers and the like.

AWE is ignored on 64-bit systems.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

david_wendelken SSCrazy Points: 2388 More actions · Answer 2

16GB RAM in the system.

Max memory set to 12GB

Using 14.5GB or more.

I inherited the system.

It was set to 8GB max ram plus awe enabled. The executable was using 8GB and under memory pressure.

So I changed it to 12GB and now it's using 14.5+.

???

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

CLR?

Extended stored procs?

Linked servers?

What you're seeing is certainly possible. As I mentioned, the max memory sets the size of the buffer pool. SQL can and does use memory outside of the buffer pool. If you read SQL architecture docs, it's the non-paged memory that's sometimes mentioned in them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

david_wendelken SSCrazy Points: 2388 More actions · Answer 4

It's a sharepoint server.

No linked servers.

I'm unaware of any custom CLR routines, don't know if sharepoint uses them. Ditto on custom extended procs

What would you recommend as the max server memory?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Are you seeing signs of memory pressure from the OS?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

david_wendelken SSCrazy Points: 2388 More actions · Answer 6

I'm running at 98+% of memory usage.

Sharepoint portal gets really, really slow every 30 minutes or so scheduled jobs kick in and need additional memory while they run.

I'm working on sharepoint tuning issues as a separate, but related issue. I suspect that there are lots of big lists in use and I've read that that can cause big memory usage. But that will take some time to identify the big lists and get them reorganized into smaller lists.

There are MS-supplied sharepoint queries that are causing updates and deletes to stack up waiting on the query to finish.

It doesn't help that running a query against the sharepoint database violates the MS support agreement.

(My jaw bounced off the floor about a dozen times the first time I heard that. The thought that querying a relational database was so dangerous to a system's stability that it would cause one to be de-supported by the vendor is -- heresy -- to a system architect like myself. But I digress...)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Are you running the web front end and the SQL database on the same server? If so, that's a really bad way of running sharepoint in anything more than a 1-person test environment.

If you do have the sharepoint front end and SQL on the same box, drop SQL's memory usage down to the 8GB that it was, and see if you can motivate for scaling it out.

I don't know if querying the sharepoint DB violates the support, making any changes whatsoever does, that includes adding indexes. That said, there's no good reason to query sharepoint DB directly, it's hard to understand and the API is more than adequate for just about any purposes

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

david_wendelken SSCrazy Points: 2388 More actions · Answer 8

No, web farm for the middle tier, clustered database on back end.

Nothing else of consequence running on the database server(s).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Then leave the memory at 12GB. Maybe drop to 11 if you're seeing evidence of OS-level memory pressure on the SQL server. If the database is the source of the performance problems, you may simply need a hardware upgrade. Make sure that it really is the DB server that's the bottleneck first.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

david_wendelken SSCrazy Points: 2388 More actions · Answer 10

I suspect the memory pressure I'm seeing is a symptom, not the cause.

I think it's probably oversized lists in sharepoint. But that will take time to find and reorganize, so I'm looking to get the most juice out of the database server until the root cause is found and fixed.

Thanks for the advice.