October 9, 2015 at 5:28 am
We are running SQL2008R2 in a production application. VM Ware, 4CPU, 6GB memory. Performance is adequate. In the test lab we've been performing tests to see if hardware will improve performance.
We upped the memory to 12GB in 2GB increments. We are getting consistently worse performance with more memory on the server. We doubled the CPU with only a minor improvement. We're happy with our current performance. However, looking down the road, the server may be upgraded and it would not be good to have worse performance.
Are there any server configurations that I need to modify to take advantage of the additional hardware? We've set the SQL memory to max. Is there anything else?
Edit: I've read that in some execution plans, additional memory can cause the execution to become non-optimal. Overall performance is less than expected. We've specifically chosen 5 of the known-slowest operations on the server for testing. They are all slower with more memory.
Any thoughts are helpful.
Thanks
ST
October 9, 2015 at 5:30 am
What do you mean, you've set SQL's memory to max?
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
October 9, 2015 at 6:32 am
In SSMS under server properties we're using the default. It seems that these values are being used by the server. When we watch the server the memory usage is growing to full capacity as expected with the new RAM installed.
From BOL
Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.
Use the default settings to allow SQL Server to change its memory requirements dynamically based on available system resources. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 megabytes (MB). The minimum amount of memory you can specify for max server memory is 16 MB.
October 9, 2015 at 6:48 am
souLTower (10/9/2015)
In SSMS under server properties we're using the default.
That's a problem. The default is 2000 TB. It's fine if the server has over 2000 TB of memory, but if not, you're telling SQL it can use every bit of memory on the server, not to leave anything for anyone, and even potentially starve the OS of memory, and it probably will.
See Chapter 4: https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ for guidance
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
October 9, 2015 at 6:55 am
Ah, so I should enter a real number. EX 2GB for OS, remainder for SQL?
October 9, 2015 at 6:56 am
In addition to setting the max memory as Gail suggests, I'd want to see what your wait statistics are before and after you add this memory. Further, you want to look at the execution plans of the queries before and after you add the memory. Also, get the wait statistics of the queries so that you know specifically what is causing them to run slow. While adding memory is no guarantee of speeding things up, I haven't seen a system get slower because of adding memory.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 9, 2015 at 7:02 am
souLTower (10/9/2015)
Ah, so I should enter a real number. EX 2GB for OS, remainder for SQL?
GilaMonster (10/9/2015)
See Chapter 4: https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ for guidance
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
October 9, 2015 at 7:42 am
It seems to me that 6 GB is very low end for a database server today. I've got more memory than that on my laptop. Even 12 GB is low end, except for a smallish database. At this level, SQL Server could easily allocate more memory to the query plan cache than it does the page buffer cache. It's the page buffer cache that minimizes physical reads keeps your queries running at top speed.
Run this to get max memory allocated for an instance and a breakdown of memory allocated for each memory cache. The page buffer cache is CACHESTORE_OBJCP and query plan cache is CACHESTORE_SQLCP.
SELECT value AS max_MB
,value_in_use AS used_MB
FROM master.sys.configurations
WHERE NAME IN ('Max server memory (MB)');
select type,SUM(single_pages_kb / 1024) As single_pages_mb
,SUM(multi_pages_kb / 1024) As multi_pages_mb
from sys.dm_os_memory_cache_counters
Group By type
Order By SUM(single_pages_kb)+ SUM(multi_pages_kb) desc;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 9, 2015 at 7:44 am
null
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply