memory in 64 bit when idling

Question

memory in 64 bit when idling

repent_kog_is_near

SSCertifiable

Points: 7278
More actions
January 8, 2010 at 7:47 pm

#218105

in 64 bit SQL Server, it is my understanding that SQL will have all the memory given.
when it is idling (when there are no users/connections), will the SQL Server still drink all the memory upto it's maxmemory settings?
thanks
Dan

Viewing 14 posts - 1 through 13 (of 13 total)

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

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

SQL will take memory a needed and will not release it unless the OS demands it back.

So if you have a SQL service that has just started and no one is using it, it'll be using very little memory. If it then gets used heavily, it'll allocate memory as it needs, however if there's an idle period again, it won't release that memory.

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

Paul White SSC Guru Points: 150467 More actions · Answer 2

...unless this is Enterprise Edition, with the lock pages in memory privilege granted, 8GB or more of RAM, and trace flag 834 enabled...in which case the entire buffer pool is allocated at start up and never released. See Large Pages Explained

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 3

Paul

Will the buffer pool be allocated and unused? in the scenario of the Ent Edition and the other factors?

how could we see how much is allocated and used/unused? what tools would you use to see that info.

thanks

Dan

Paul White SSC Guru Points: 150467 More actions · Answer 4

repent_kog_is_near (1/9/2010)
Will the buffer pool be allocated and unused? in the scenario of the Ent Edition and the other factors?

Normally, yes - but only in the circumstances I described. If you haven't specifically enabled that trace flag in conjunction with all the other conditions, SQL Server acquires BPool memory as it finds it needs it.

repent_kog_is_near (1/9/2010)
How could we see how much is allocated and used/unused? what tools would you use to see that info.

There are a number of methods you can use, including information exposed by dynamic views, performance counters, and the DBCC MEMORYSTATUS command. This is a very broad subject area - are you just asking out of curiosity, or is there a particular reason for your original question?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 5

I want to know if there is a sure way to know if SQL Server has more memory than it needs especially in the 64 bit OS environment.

In 32 bit it is not as transparent, as the extra memory (over 3GB) is used only for the BufferPool, for not for other internal memory usage.

Is the Process: Working Set for SQL Server an accurate way to know exactly how much is used, in 64 bit?

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

repent_kog_is_near (1/10/2010)
I want to know if there is a sure way to know if SQL Server has more memory than it needs especially in the 64 bit OS environment.

Unless you have the traceflag Paul described (and it's not that common of a one), SQL will only allocate memory as it needs. it. Are you one of the few people who is using that traceflag? If not, then SQL will be using the memory that it needs.

Working set is a process's working memory. None of the OS counters can tell you if memory is allocated to the SQL but not used by SQL. For that, you have to check SQL-specific counters and DMVs.

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

I will note that you should ALWAYS set a MAXIMUM limit for the RAM the buffer pool can use to avoid unfortunately common memory starvation scenarios. The amount to leave depends on MANY factors.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 8

Gail

I do not (yet) use that trace. Do you recommend it? is it's only job to make sure SQL gets all the memory buffered at one time, in the beginning?

So, the Working set of SQL process should tell accurately how much SQL actually uses and needs? Are you referring to the counter 'Process\Working Set' in Perfmon to get this detail.

thanks

Dan

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 9

TheSQLGuru

Are you referring to the maxmemory setting for the server?

thanks

Dan

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

repent_kog_is_near (1/12/2010)
I do not (yet) use that trace. Do you recommend it?

Have you looked up that traceflag (search engine of your choice)? Have you examined info given regarding when to enable it? Start with the link Paul gave.

There are almost no traceflags that I will recommend people enable without a good reason.

So, the Working set of SQL process should tell accurately how much SQL actually uses and needs? Are you referring to the counter 'Process\Working Set' in Perfmon to get this detail.

I would rather recommend SQL-specific counters, like Total Server Memory.

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

Paul White SSC Guru Points: 150467 More actions · Answer 11

repent_kog_is_near (1/12/2010)
I do not (yet) use that trace. Do you recommend it? is it's only job to make sure SQL gets all the memory buffered at one time, in the beginning?

Dan,

No. I only mentioned that trace flag and the article (which I presume you have not read) to illustrate a point. I would certainly not recommend it to you - it is a very advanced topic only suitable for a few high-end systems with very knowledgeable staff to support it.

It would probably help all of us to help you if you were to describe in detail what it is that is concerning you about your SQL Server's memory usage. It would also help a great deal to know more about the hardware and SQL Server installation. For example:

1. What edition of SQL Server are you running (e.g. 64-bit Enterprise)

2. What is the specification of the server (CPUs, memory, disks, etc.)

3. What does the Total Server Memory performance monitor counter show?

4. Does the account SQL Server starts under have the lock pages in memory privilege?

5. Does SQL Server start with any trace flags enabled?

6. What is the typical range of Page Life Expectancy?

7. What is the typical range of the Buffer Cache Hit Ratio?

...and so on

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

repent_kog_is_near (1/12/2010)
TheSQLGuru
Are you referring to the maxmemory setting for the server?
thanks
Dan

No, not for the server but rather for SQL Server itself.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150467 More actions · Answer 13

TheSQLGuru (1/12/2010)
repent_kog_is_near (1/12/2010)
TheSQLGuru
Are you referring to the maxmemory setting for the server?
No, not for the server but rather for SQL Server itself.

That reminds me...:-)

Question 8: What are the SQL Server min and max server memory configuration settings?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi