performance problem - sql server 2005 x64 version - memory allocation - need more information

Question

performance problem - sql server 2005 x64 version - memory allocation - need more information

Viewing 12 posts - 16 through 26 (of 26 total)

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

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

With 5 GB memory on the server (that's a rather weird amount) I'd probably set min to 3.5 and max to 4, providing there are no other apps that run on the server.

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

swjohnson Hall of Fame Points: 3254 More actions · Answer 2

We had a similar issue and in short what was happening was that virtual memory was killing the server and eventually not allowing memory to be allocated and the server would stop all connections until it freed up memory. See this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1783523&SiteID=1

The setting for Max memory is 2TB which is basically unlimited as far as Windows is concerned.

What we did after consulting with MS about our issue was to set the MAX memory for that instance to 1GB less than the total RAM for the machine. Our box had 4GB so we set MAX Memory in SQL to 3GB and left the OS with 1GB. Since then no more errors. (We run SQL 2005 x64 on Windows 2003 Std x64). We are planning on increasing the RAM significantly.

This assumes that there is only one instance running on the box and only used for SQL. If you have other instances running on the box, you need to allocate accordingly for each instance so that all instance have sufficient RAM.

What was difficult for us is that errors weren't always generated in the log files. Sometimes we would see the error, othertimes nothing but it would stop accepting connections for about 2 minutes and then start again.

SJ

http://blog.mathomsolutions.com

Accepting all invites

swjohnson Hall of Fame Points: 3254 More actions · Answer 3

One more thing that I forgot to post. Since you rebuilt this server and I am assuming restored your databases, it would be wise to make sure to reindex and update your db statistics. This was also a recommendation from MS.

SJ

http://blog.mathomsolutions.com

Accepting all invites

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 4

Hi All,

Thank you for your technical advice.

We will implement the change this week.

Regards,

Parthipan

Vik Nat Hall of Fame Points: 3930 More actions · Answer 5

Can someone please tell me if "lock pages in memory" is supported in SQL 2005 Standard, 64 bit. From from I can tell - it is not supported and even though you can set it up, it will not be used. Is it only supported in Enterprise? I am in the process of testing SQL 2005 standard 64 bit. Its got 16GB memory and I have let SQL dynamically manage memory. I was running some load testing this morning and basically SQL Server ran away with all the memory leaving nothing to the OS, causing the server to hang.

swjohnson Hall of Fame Points: 3254 More actions · Answer 6

Vik

It is ignored on 64 bit OS/SQL. You can set it but it is ignored. Doesn't matter which version -- standard or enterprise.

Also, don't let SQL dynamically manage your RAM otherwise it grabs everything it sees and doesn't play nicely. In the DB properties set the MAX Server Memory to something less that your total RAM.

Shawn Johnson

http://blog.mathomsolutions.com

Accepting all invites

Vik Nat Hall of Fame Points: 3930 More actions · Answer 7

Shawn - thats what I thought, thanks for clarifying that.

yeah - I have left 500MB for the OS. Will run some more tests and see how things look

Have a good one.

Vik.

swjohnson Hall of Fame Points: 3254 More actions · Answer 8

I have 1Gb for my server and it still thrashes a bit at peak periods. Since you have 16GB, you might want to go higher for the OS.

Shawn

Accepting all invites

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 9

Hi All,

After enabling lock pages in memory and reindexing all user databases, the user commented the performance of the server is fine. As an administrator we cannot find any difference. But we can find the duration of execution of the user defined jobs is less and improved.

Thank you all for technical advice.

Please advice me how we have to enable 'lock pages in memory' option in 2 node active/passive custer server.

We need to enalble 'lock pages in memory' option for sql account in both nodes of the server?

BR,

Parthipan

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 10

Also I found that the service account should have enough rights to lock the pages in memory on both the nodes. How can I check this permission on windows login and what rights it should have..

Please confirm that we have to enable 'lock pages in memory' option for sql account on individual nodes of the cluster server?

BR,

Parthipan

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 11

As asked earlier, please confirm that lock pages in memory will work on x64bit sql server 2005 standard edition installed on active/passive server as I can find in the below msdn page that the option is not supported..

http://msdn2.microsoft.com/en-us/library/ms190730.aspx

Please clarify me.

BR,

Parthipan

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 12

with 64 bit sql you the MUST set the max memory for sql server, I'd suggest 1.5 to 2gb below the installed memory.

ANY version on 64bit if you do not set max memory your server will thrash. you can largely ignore minimum memory, why would you want to decrease memory from SQL Server ? and to what? assuming it's a dedicated server.

sql server should not use the page file - set a fixed size and forget about it.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/