Memory for SQL Server Machine

You can do a search on that topic as there are a lot of articles/blogs people have written. It essentially allows SQL Server to 'lock pages in memory' instead of swapping pages out to disk and please note that SQL Server is a memory greedy monster. 😛

Edit: Do you know how to enable the permission?

My Blog!

Apurva SSCrazy Points: 2412 More actions · Answer 3

Just to add to my previous info, while looking in to the SQL Logs I found the following type messages

Unsafe assembly 'microsoft.sqlserver.mpusqlclrwrapper, version=10.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil' loaded into appdomain 4 (mssqlsystemresource.dbo[runtime].3).

AppDomain 6 (mssqlsystemresource.dbo[ddl].5) unloaded.

AppDomain 5 (mssqlsystemresource.dbo[ddl].4) unloaded.

AppDomain 4 (mssqlsystemresource.dbo[runtime].3) created.

AppDomain 3 (mssqlsystemresource.dbo[ddl].2) unloaded.

AppDomain 2 (mssqlsystemresource.dbo[ddl].1) unloaded.

I checked the appdomain message and people have said it is an indication of memory pressure or memory leak. Our SQL servers are not running any other application.

Can anyone suggest what the Unsafe assembly indicates? Any clarification on the messages will be appreciated.

These messages have been recorded for the past 4 months since the time server has been in production. These have usually been recorded 3-4 times a month. I did not find them on any of our other sql servers.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

Syed Jahanzaib Bin hassan (4/22/2011)

2-You have define Max Degree of Parallelism to 8,This will define as 2 because its depend on physical processor not on logical

3-Set MAX worker Thread 704 if this OS is 64bit othwisewise

1. Don't touch the setting MAX worker Threads unless there is a strong reason or you are sure that changing that setting will fix the issue. It should be tested well before changing this option. Let the SQL Server decide what is good for it.

2. Max Degree of Parallelism is for Logical Processors.

dajonx (4/25/2011)

Another thing I just thought about is if your cache hit ratio is low. Perhaps the performance dashboard/Perfmon can assist in diagnosing the server. Sorry, I'm just thinking about anything that can help you resolve this issue. Please let us know if you find the issue and the resolution.

Cache Hit Ratio cannot be solely relied upon to judge Memory pressures, instead use Page Life Expectancy and Available Bytes (MB).

Also follow great advices from Gail and Steve. They are right as always.

If this is a dedicated box for SQL Server, then you can set Max Memory upto 28 GB.

Grant "Lock Pages in Memory" privilege to the SQL Server service account.

Also enable " Optimize for Adhoc workloads " option which will help in improving the performance.

Also run a Perfmon Counter Log with Basic counters to pin-point the root cause. With these kind of issues you can't rule out any resource bottlenecks or rely just on the error logs.

Thank You,

Best Regards,

SQLBuddy

.

sgvv SSC Veteran Points: 231 More actions · Answer 5

SQL Server "max server memory" controls only th BufferPool memory. SSIS, LOB data,CLR process, SQL Agent process etc, do not use BufferPool memory of SQL Server. They use what is called MemtoLeave memory.

Drop the max server memory to as low as possible, and then increase it slowly, till you find that a optimum setting is reached.

http://support.microsoft.com/kb/977190

http://www.johnsansom.com/index.php/2009/03/sql-server-memory-configuration-determining-memtoleave-settings/#axzz1LGk4O7jV

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

LOB data does use the buffer pool. It's paged allocations, part of the data cache.

The setting of memtoleave (-g) is only applicable on 32-bit instances. What's the relevance of a kb article on SSIS memory here?

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

sgvv SSC Veteran Points: 231 More actions · Answer 7

sgvv

SSC Veteran

Points: 231

May 3, 2011 at 2:29 am

#1318925

Thank you Gail. I am learning.

We had faced a similar problem with SSIS (64 bit system )and hence the link. Reducing the max server memory settings solved our issue

Apurva SSCrazy Points: 2412 More actions · Answer 8

I tried most of the suggestions given here including changing memory settings and account permissions. Since last friday when we rebooted the machine, the performance has been acceptable since users are not complaining. But it is no way near to the performance we are getting on test machine which was our earlier production box. Now we are waiting to upgrade the memory. Due to small oversight our network guys had installed the Standard version of windows 2008 r2. We will be grading it to enterprise. I am not sure if that would make a difference. Will keep everyone posted.