May 11, 2011 at 4:09 pm
We have some slow response time of our applicaitons recently. I did some tracing and one problem we found is freelowmemory for SQL server.
Targeted memory SQL Server memory
used : 8279984 kB
SQL Server target memory : 9472528 kB
% target memory available: 12.59%
Currently SQL server memory is setup using dynamic memory configuration of default installation . It’s using 8.2 gb now. The total memory of this server is 12 gb.
Operating system at least needs 3-4 gb for 64 bits OS. So there is no more memory can be allocated to SQL server from operating system.
As I understand, it’s designed behavior -after SQL server restart, it gradually shoot up and reserve as much as possible memory for itself, and it dynamic adjust memory usage, if windows need memory , it will release it.
We can also manually setup the max amount of memory to the server, but we have to leave to operating system at least 3-4 gb for 64 bits OS.
The SQL targeting memory is 9.4 gb. So there will be not enough or pretty low memory for operating system.
The only way to obtain more memory is to adding more RAM to this server. Correct?
And also if this statement is true: We know that SQL Server could consume 9.4 GB memory it is was needed and currently it is using about 87% of that amount. I believe this suggests that memory pressure on the DB server is not necessarily the problem. If SQL Server were under pressure for memory it would have consumed and held the entire amount of Target Memory available.
May 11, 2011 at 5:06 pm
Have you checked the hard drive performance during this slowness? What's the Disk Queue lenght metrics show?
Also, If I were you I wold set the start memory for SQL at 7GB and Max memory at 8GB. Also consider setting/assigning the lock pages in memory for the account that SQL runs as.
My hunch is disk i/o.
May 11, 2011 at 10:18 pm
Follow Warren's advice.
1. First set Max Memory for SQL Server and also enable Lock pages in Memory.
2. If this server is dedicated for SQL Server then you can assign 2-4 GB to the OS.
3. Enable "Optimize for the Adhoc Workloads " option, which may reduce the memory consumption of the SQL Server due to the non parameterized adhoc queries.
4. Slowly increase the Max Memory setting to 8.5, 9.0 and upto 9.5 Max to see how the OS responds to it.
5. Increase the size of the page file to 2 to 2.5 times that of the Physical RAM on the server and if possible move the Page file to a dedicated physical drive. This will be really useful if you are going to increase the SQL Server Max Memory above 8 GB.
Thank You,
Best Regards,
SQLBuddy
May 12, 2011 at 2:43 am
Thank you both, I will look into this and give it a try tomorrow.
A couple of thing I don't quite understand is 1. Warren said " If I were you I wold set the start memory for SQL at 7GB and Max memory at 8GB", but what we can our server as below is already using 8gb, it's targeting 9.2gb. 8 gb is even less than currently used memory, What will happen if we don't set it's max memory to the targeting memory?
SQL Server memory
used : 8279984 kB
SQL Server target memory : 9472528 kB
% target memory available: 12.59%
sqlbuddy123 said 'Slowly increase the Max Memory setting to 8.5, 9.0 and upto 9.5 Max to see how the OS responds to it', this sounds easier to understand, but 8.5 is still lower than targeting memory.
2. If we add physical memory, this is probably the last thing to try, do we need to do all these things you two suggested above? Shall we use default setting to let sql dynamically allocate memory or shall we need to set max memory to for example 9.2 gb?
3. if I set up the max memory, do I need to restart server, this is production server, it will be hard if I do so.
for all the other steps you suggested, do we need to restart the server?
We did monitoring hardware like CPU, memory, disk, process, network, not found significant problem. I will double check disk i/o
One thing we caught during the bad performance period, sql server wait time for network statistics number is very high. but no data collected why it is so high.
All this above is for trouble shooting the performance we have as below:
1. With one user, able to use search/override/save with almost no delay – pretty instant results.
2. Added User #2, slowness began, but did not become significant until User #2 was on 5th assignment. All Assignments are made in Override.
3. Once User #2 logged off, system went back to pretty quick.
Sample Times
User #1
Search= 45secs Override=38 secs Save=2min 38 secs
Search =47 secs Override=50secs Save=2min
Search =46 secs Override=1min 5secs Save=1min 52 secs
User #2
Search =1 min 28 secs Override=1min 10secs Save=1min in
Thanks so much for helping with this.
May 12, 2011 at 8:56 am
I said 8GB because it sounded like you didn't want to have less than 4GB for the OS. Anything more than 8GB for SQL is better.
A box with 12GB is a pretty hefty server. If a second user logs in and runs whatever query and causes slowness, then I would have to say look at the query that it runs and optimize it.
Is it safe to assume there are proper indexes used for each table and each query? If so, do a re-index and try it.
Are you on Server Standard or Enterprise? x32 or x64?
May 12, 2011 at 9:21 am
Thank you, it is enterprise 64 bits.
May 12, 2011 at 10:10 am
I can be flexible as long as OS is fine with that.
I see the targeting memory is already 9.2, so if I set max to 8.5, is that OK?
May 12, 2011 at 5:36 pm
1. Even if you add additional memory, it is necessary to configure the above settings to get optimum Performance. If it is dedicated SQL Server BOx, then always enable Max Memory along with Lock pages in Memory privilege. AWE is not needed for 64-Bit machines..
2. Setting Max Memory doesn't require a Restart.
3. You can set it up to 8.5 GB Max.
4. Enable "Optimize for Adhoc work loads" option for SQL 2008 which reduces the Memory consumption due to the non-parameterized Ad-hoc queries.
5. You should also look into the Page file settings and optimization of if you are trying to increase max mem above 8 GB.
What maintenance are you doing on the Server ?
Index Rebuilds\Reorganize ?
Update Statistics ?
Run a 24 hour perfmon counter log to identify the SQL Server Bottlenecks .
Thank You,
Best Regards,
SQLBuddy
May 12, 2011 at 6:24 pm
Thanks so much. Please see in lines.
1. Even if you add additional memory, it is necessary to configure the above settings to get optimum Performance. If it is dedicated SQL Server BOx, then always enable Max Memory along with Lock pages in Memory privilege. AWE is not needed for 64-Bit machines..
I talked with our network guy, it's not possible for them to add another memory disk on that computer.
So for now I can only tune what we have now. I set it up to 8.5 max.
But I'm hesitated to enable the lock pages in memory privilge, for two reasons, one is we seems already experience a long delay in performance, the lock pages in memory sounds will more locks.
also I see in article http://msdn.microsoft.com/en-us/library/ms190730(v=SQL.100).aspx
it says, Locking pages in memory is not required on 64-bit operating systems
5. You should also look into the Page file settings and optimization of if you are trying to increase max mem above 8 GB.
I checked with network person, currently our page files are in another drive separately 16 gb.
He said it's standard to be 1.5 gb of RAM size. So 16gb is that size now.
But he thinks if really needs, we can do that.
What maintenance are you doing on the Server?
Index Rebuilds\Reorganize ? This only runs on Sunday.
Update Statistics ?
Run a 24 hour perfmon counter log to identify the SQL Server Bottlenecks .
We have a monitoring tool called heroix. We turned on 7X24.
Now I get this:
SQL Server memory used : 8356848 kB
SQL Server target memory : 8912896 kB
% target memory available: 6.24%
Target memory refers to the amount of total physical memory the dynamic memory manager of SQL Server is willing to consume at any given time. If this condition persists, it will significantly degrade the performance of SQL Server.
To alleviate the problem, do one of the following:
1. Increase the physical memory for server .
2. Enable dynamic memory configuration for server on the memory tab of the properties page for the default SQL Server instance in SQL Server Management Studio.
3. If the percent of total memory targeted is low, and dynamic memory configuration is enabled, reduce the concurrent application load on server.
4. Increase the maximum amount of memory available to the default SQL Server instance in the properties screen for this server in the SQL Server Management Studio.
The above message are the exactly same as before I changed from default memory to 8.5 max. the only difference is the numbers are different see the top post.
when 3 says, dynamic memory configuration , what does it mean, this is a monitoring tool for both windows, unix, oracle, sql server, and other database programs, so the recommendations are not always good, do you think so too?
Thanks
May 12, 2011 at 11:11 pm
SQLMyFriend (5/12/2011)
1. Even if you add additional memory, it is necessary to configure the above settings to get optimum Performance. If it is dedicated SQL Server BOx, then always enable Max Memory along with Lock pages in Memory privilege. AWE is not needed for 64-Bit machines..
I talked with our network guy, it's not possible for them to add another memory disk on that computer.
So for now I can only tune what we have now. I set it up to 8.5 max.
But I'm hesitated to enable the lock pages in memory privilge, for two reasons, one is we seems already experience a long delay in performance, the lock pages in memory sounds will more locks.
also I see in article http://msdn.microsoft.com/en-us/library/ms190730(v=SQL.100).aspx
it says, Locking pages in memory is not required on 64-bit operating systems
It is recommended to Enable the Lock Pages in Memory on 64-bit systems too. I always had good results with this option on 64-bit systems. It's no way related to the SQL Server Locks and something like that. Actually with this option the SQL Server pages in the memory will locked in the memory and are prevented from paging to the Hard disk during the memory pressures.
Please refer to the community content displayed at the bottom of the link that you mentioned.
SQLMyFriend (5/12/2011)
5. You should also look into the Page file settings and optimization of if you are trying to increase max mem above 8 GB.I checked with network person, currently our page files are in another drive separately 16 gb.
He said it's standard to be 1.5 gb of RAM size. So 16gb is that size now.
But he thinks if really needs, we can do that.
Thanks
You can leave the page file at 16 GB.
SQLMyFriend (5/12/2011)
What maintenance are you doing on the Server?
Index Rebuilds\Reorganize ? This only runs on Sunday.
Update Statistics ?
Schedule Index Reorgs to run on every night .
Schedule Index Rebuilds to run on every sat or sun night .
Update statistics have to done as frequently as possible. Schedule them to run nightly.
If you miss any of the above mentioned procedures, you will have performance problems.
If Fragmentation is > 5% and < 30 % do Index Reorgs
> 30 % do Index Rebuilds
To do these index maintenance and update statistics, it is recommended to use the popular scripts at
SQLMyFriend (5/12/2011)
when 3 says, dynamic memory configuration , what does it mean, this is a monitoring tool for both windows, unix, oracle, sql server, and other database programs, so the recommendations are not always good, do you think so too?
Yep.. So go with Max Memory, Lock Pages in Memory and Optimize for adhoc workloads.
If you can do the Maintenance properly and do the server configuration as recommended, you could see a reduction in the target server memory or the total memory consumption by sql server.
Thank You,
Best Regards,
SQLBuddy
May 13, 2011 at 3:14 am
You don't make a decision on the lack of memory of a server by looking at the target memory.
You check the perfmon counters related to memory (Lazy Writes, page life expectancy and Free pages) and make your decision based on that.
For all you know you may have some concurrency problem and adding 2TB of RAM will not solve it.
You need to monitor exactly what's going on when you have the problem and find the bottleneck (if there is one), in most cases the queries can be improved to fix the problem without having to touch the hardware.
May 13, 2011 at 10:48 am
SQLMyFriend (5/12/2011)
I talked with our network guy, it's not possible for them to add another memory disk on that computer.
A network guy is managing the SQL Server????
OK, let me ask a few hardware questions....
What type of "Server" is this? HP? Dell? IBM?
What type of hard drives are installed? SCSI? SATA? SAS?
Is the NIC 100Mb/s? If so is it set to Full Duplex on the server? If full duplex, is the switch hard coded to full duplex?
Is the NIC 1Gb/s? If so, is the Switch hard coded to 1Gb/s?
Has anyone tried to "sniff" the network on that port for any collisions, droppped packets or bad packets, connection "flapping"?
Lastly, have you looked at the query that is run when the slowness occurs?
Can you run the same query local on the SQL server in SSMS and does it run slow? Did you try running the same query on another PC in SSMS and does it run slow?
May 13, 2011 at 11:27 am
Thanks, sqlbuddy.
This is very helpful, I will bookmark it for future reference.
May 13, 2011 at 11:46 am
Our application/queries run much faster in testing enviroment with the same number of users.
About memory,
Thanks Oliii for pointing out perf counters for memory.
We do have a monitor tool for our enviroment, it is desiged not only for sql server, and I found analysis and recommendations are not so accurate for sql server, esp about memory. For memory comsumption, all is a chart that shows the same amount memory used for example it now shows 8.5 gb bar all hours of the day, so it didn't help much.
besides using perf counters to run every time, (not sure how to read it either,) is there an easier way to find out how much memory is SQL server is using, free memory, and the memory of the rest OS using, is it low?
Or is it a place I can easily read memory SQL is really using, free memory of sql server, memory os is using, free memory on the computer?
Thanks
May 16, 2011 at 11:00 am
To get the memory SQL uses, tun the following...
SELECT
--Amount of physical memory on server
physical_memory_in_bytes
, physical_memory_in_bytes / 1024 / 1024.00 as physical_memory_in_MB
,virtual_memory_in_bytes
, virtual_memory_in_bytes / 1024 / 1024.00 as virtual_memory_in_MB
--Committed physical memory in buffer pool
--Does not include MemToLeave memory area
,bpool_committed AS 'Number of 8KB buffers in buffer pool'
, bpool_committed * 8 / 1024.00 as bpool_committed_in_MB
, bpool_commit_target AS 'Number of 8KB buffers needed by the buffer pool'
, bpool_commit_target * 8 / 1024.00 as bpool_commit_target_in_MB
,CASE
WHEN bpool_commit_target > bpool_committed THEN 'Extra memory needed from OS for Buffer Pool'
WHEN bpool_commit_target < bpool_committed THEN 'Memory may be released from Buffer Pool to OS'
END AS 'Status of Dynamic Memory'
, bpool_visible AS 'Number of 8KB Buffers in Buffer Pool that are directly accessible in the processes VAS.'
, bpool_visible * 8 / 1024.00 as bpool_directly_accessible_VAS_in_MB
FROM sys.dm_os_sys_info
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply