October 5, 2007 at 3:58 pm
Hello,
I posted this at sql-server-performance.com (where I found good information on SQL performance audits) but thought I should post it here too because you guys have a great set of experts as well.
I ran the audit steps named Identify SQL Server Hardware Bottlenecks and found that the server in question is averaging about 97 in Memory: Pages/sec. The section on how to interpret the counters says that this value should not average more than 20 and that a higher value is a possible symptom of a "memory bottleneck due to a lack of needed RAM."
As far as I can tell, the server has 8 GB of RAM. The Buffer Cache Hit Ratio is averaging over 99%, so that seems OK according to the audit steps. Also, I could not find any other significant process or application that is installed on the server that would be consuming memory, so that appears to rule out that possibility as suggested by the audit steps (although if anyone can suggest how to look for anything that is not obvious, please let me know).
So the final suggestion is regarding the SQL Server memory settings:
SQL Server should be configured so that it is set to the "Dynamically configure SQL Server memory" option, and the "Maximum Memory" setting should be set to the highest level. For optimum performance, SQL Server should be allowed to take as much RAM as it wants for its own use without having to compete for RAM with other applications.
Below are the values on the server I am looking at, transcribed from the SQL Server Memory Properties tab. The Maximum is currently set to 3107 out of 8187.
So my questions are:
1. Does the quoted advice above mean I should set the Maximum to 8187? (In case it makes a difference, the "Memory: Available Bytes" value is averaging more than 5,000,000,000 - I'm not sure how that number is determined by the performance monitor.)
2. Does this memory change require downtime (a restart of the SQL Server service or of the whole server itself)?
3. I became the DBA less than a year ago, so I am not sure how this value of 3107 was entered. Is this a default value when a SQL Server has 8 GB of RAM?
Thanks in advance for any help. If you need any more information to help give an answer, please ask me.
Sincerely,
webrunner
Dynamically configure SQL Server memory (this option is selected)
Minimum (MB): 0
0 MB ..... 8187 MB
Maximum (MB): 3107
4 MB .... 8187 MB
...
Minimum query memory (KB): 1024
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 6, 2007 at 11:56 am
Hello,
I haven't worked with servers >2gb yet.
But found a great link about how sql server deals with memory:
http://sqljunkies.com/Tutorial/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.scuk
So my questions are:
1. Does the quoted advice above mean I should set the Maximum to 8187? (In case it makes a difference, the "Memory: Available Bytes" value is averaging more than 5,000,000,000 - I'm not sure how that number is determined by the performance monitor.)
Since your hit ratio is 99% it looks sql server has enough memory for now. If the free memory isn't going to be used by other applications you can crank it up to 6-7gb (to leave memory for windows).
2. Does this memory change require downtime (a restart of the SQL Server service or of the whole server itself)?
I guess changing between dynamically & fixed requires a restart of service in the way sql server reserves the memory for itself.
3. I became the DBA less than a year ago, so I am not sure how this value of 3107 was entered. Is this a default value when a SQL Server has 8 GB of RAM?
It is not a default value (because of the memorydealing issues >2gb)
3Gb is now available to sql server, the other is free to use by other applications including windows itself.
October 7, 2007 at 9:28 pm
I think you're ok with memory as well, but with 8GB, you probably want to give 6-6.5 to SQL and see if performance improves
Make sure you have AWE on and PAE turned on. These are documented in BOL and in the KB on how to set for a server of your size. At 8GB, your OS needs > 512MB just to page memory out, so don't let the OS itself run low.
Also, are any other server services running on that machine?
October 8, 2007 at 6:58 am
You have two options with 8Gb of ram - I assume you have enterprise version as extra memory can't be used with std edition.
set the /3gb switch + /PAE + set awe on. set max memory to ( i'd suggest ) 6.5Gb .
set /PAE + awe . memory settings as before.
note that once you turn on awe dynamic memory does not work - doesn't matter what you set in memory, awe will take the amount of memory you set as max memory.- so this setting is important
The use of the /3gb switch can increase available memory to other areas of sql server, awe only increases the data cache. Depends on what else runs on your server and how much out of process stuff runs on your box, dts sysmaint.exe , xml etc.
once your server is running you can query sysperfinfo to check on memory being used. btw. I never use buffer cache hit ratio as an indicator - I prefer page life expectancy.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 9, 2007 at 9:39 am
Steve Jones - Editor (10/7/2007)
I think you're ok with memory as well, but with 8GB, you probably want to give 6-6.5 to SQL and see if performance improvesMake sure you have AWE on and PAE turned on. These are documented in BOL and in the KB on how to set for a server of your size. At 8GB, your OS needs > 512MB just to page memory out, so don't let the OS itself run low.
Also, are any other server services running on that machine?
Thanks for your reply!
I see other things running on the server, such as mssearch.exe (Mem usage 52,352 K), so it would be good to get advice about what services can be safely turned off if they are using memory unnecessarily.
But more importantly, we have SQL Server 2000 Standard Edition on this server, which judging from what I have read probably means that this particular server can't use more than 2 GB. Is that correct?
I took a look at the Task Manager and saw this value for SQL Server:
sqlservr.exe Mem Usage: 1,791,552 K
It seems to be around 1.5 to 1.8 GB whenever I check. Does this mean that the maximum memory setting doesn't make any real difference in this case due to the SQL version?
And if memory itself is not the problem (assuming that 2 GB is enough given the Buffer Cache Hit Ratio >99% as someone mentioned), what is the next step in identifying why blocking is occurring?
Thanks again for any help.
Webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 9, 2007 at 10:37 am
I see other things running on the server, such as mssearch.exe (Mem usage 52,352 K), so it would be good to get advice about what services can be safely turned off if they are using memory unnecessarily.
>If mssearch is running, you are probably using full-text search. So leave it on.
But more importantly, we have SQL Server 2000 Standard Edition on this server, which judging from what I have read probably means that this particular server can't use more than 2 GB. Is that correct?
>Aye, no more than 2GB.
I took a look at the Task Manager and saw this value for SQL Server:
sqlservr.exe Mem Usage: 1,791,552 K
It seems to be around 1.5 to 1.8 GB whenever I check. Does this mean that the maximum memory setting doesn't make any real difference in this case due to the SQL version?
Upper effective limit is 2GB. Since you have a memtoleave area it will float between 1.5-1.8 gb.
And if memory itself is not the problem (assuming that 2 GB is enough given the Buffer Cache Hit Ratio >99% as someone mentioned), what is the next step in identifying why blocking is occurring?
>Run a sql profiler trace to see which statements occuring. With sp_who2 you can see the connections and who is blocking who. In case of deadlocks you have to enable some extra startup flags for more info.
October 12, 2007 at 2:31 pm
Thanks for the replies!
I have one other question. How does memory work for SQL Server 2005? I see in the SQL Server 2005 feature comparison that the Standard Edition uses "Operating system maximum".
We have a SQL Server running SQL 2005 Standard Edition that I am checking right now. It is running in a cluster and has 8 GB of RAM. When I check the Maximum server memory setting for the server, I see that it is set to 2147483647 MB. But I would like to know how much memory SQL is actually using - whether it too is stuck at 2 GB or is somehow using more.
My specific questions are:
1. How can I tell how much memory SQL Server is allocating and using?
2. Does the Standard Edition option "Operating system maximum" mean that it will automatically use more than 2 GB as needed, or do we need to use the AWE option to make use of this memory?
It seems odd that there is no straightforward way to make sure SQL can use all of the memory it needs - was there a design reason for this?
Thanks for any help!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 16, 2007 at 1:16 am
1. How can I tell how much memory SQL Server is allocating and using?
There are specific performance counters like sql server memory : ...
October 16, 2007 at 3:09 pm
Jo Pattyn (10/16/2007)
1. How can I tell how much memory SQL Server is allocating and using?There are specific performance counters like sql server memory : ...
Thanks for your reply. Just to provide further information, here is some data from traces I have run a couple of times over the past month, following instructions at this page:
Identify SQL Server Hardware Bottlenecks
The listing below has average values for the times listed.
Also note that 10/8/2007 was a holiday here in the US (Columbus Day), so some counters were probably abnormally low. Furthermore, we are running SQL Server Standard Edition, so the Memory: Available Bytes counter of 5 GB is not how much RAM we can use - Standard Edition limits us to 2 GB and SQL appears to be running steadily at about 1.7 GB of RAM. I assume that the 5 GB listed says little about SQL Server except for how much RAM is available for the OS.
What I have noticed is that even though the Buffer Cache Hit Ratio, Avg. Disk Queue Length, % Processor Time, and Processor Queue Length appear to be good, the Memory: Pages/sec is is erratically up and down with several stretches of >1000 pages/sec, and the number of user connections always seems to hover around the same amount (even on the holiday). The Physical Disk: % Disk time also looks consistently too high.
I am not sure how to interpret these values, so I would be grateful for any help in doing so. Sorry for the layout - is there any way to post a table of values in forum posts?
Thanks!
webrunner
Memory: Pages/sec
9/15/2007 97.885
10/8/2007 24.389
10/15/2007 520.3
Memory: Available Bytes
9/15/2007 5,581,406,155
10/8/2007 5,609,269,088
10/15/2007 5,589,281,623
Physical Disk: % Disk time
9/15/2007 125.58
10/8/2007 142.33
10/15/2007 93.02
Physical Disk: Avg. Disk Queue Length
9/15/2007 3.7970
10/8/2007 4.2698
10/15/2007 2.7900
Processor: % Processor Time
9/15/2007 11.91
10/8/2007 10.25
10/15/2007 9.70
System: Processor Queue Length
9/15/2007 0.43
10/8/2007 0.28
10/15/2007 0.13
SQL Server Buffer: Buffer Cache Hit Ratio
9/15/2007 99.66
10/8/2007 99.64
10/15/2007 99.66
SQL Server General: User Connections
9/15/2007 922.40
10/8/2007 934.69
10/15/2007 916.81
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 17, 2007 at 1:19 am
Hello, I'm deprived of my sql installation for the moment, so this is from memory.
Memory: Pages/sec
9/15/2007 97.885
10/8/2007 24.389
10/15/2007 520.3
Is quite high. If it only spikes briefly at certain moments, it is because sql server is doing a checkpoint (writing dirty memory buffers to disk). There is an option to increase/decrease the frequency of doing it, but the default 0 (automatic) should be fine.
I suspect another program exercising pressure on sql server <2gb area.
Otherwise perhaps some IO intensive queries loading lots of data?
Memory: Available Bytes
9/15/2007 5,581,406,155
10/8/2007 5,609,269,088
10/15/2007 5,589,281,623
There is something like target memory. The memory sql server thinks to need to perform fully.
Physical Disk: % Disk time
9/15/2007 125.58
10/8/2007 142.33
10/15/2007 93.02
Rather look at disk queue length
Physical Disk: Avg. Disk Queue Length
9/15/2007 3.7970
10/8/2007 4.2698
10/15/2007 2.7900
Normally 2 per spindle is acceptable. Exceptions are SAN which have another kind of measuring (see sql server performance)
Assuming you have 3+ drives this looks well. (Raid 5 is slow for write actions, only recommended if the writepercentage vs read is below 15%)
Processor: % Processor Time
9/15/2007 11.91
10/8/2007 10.25
10/15/2007 9.70
Looks even underutilized ;assuming there is no I/O bottleneck.
System: Processor Queue Length
9/15/2007 0.43
10/8/2007 0.28
10/15/2007 0.13
There is also a recommended queue limit, guess 2
SQL Server Buffer: Buffer Cache Hit Ratio
9/15/2007 99.66
10/8/2007 99.64
10/15/2007 99.66
Looks fine
SQL Server General: User Connections
9/15/2007 922.40
10/8/2007 934.69
10/15/2007 916.81[/quote]
Each connection uses 1mb(?check books on line) of memory by default.
The auditing pages of sql server performance are a good start.
October 17, 2007 at 8:31 am
btw. I never use buffer cache hit ratio as an indicator - I prefer page life expectancy.
Seconded. I can imagine too many scenarios where the hit ratio might give a false indication of the need for memory. PLE is a better indicator.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply