Memory Settings Help Please
-
I have a server running 2003 Enterprise x64 Edition SP2 Build 3790 OS and SQL Server 2005 (9.00.5254.00) Enterprise Edition 64bit with 64GB of ram installed. I have the min memory set to 15360 and max set to 51200. I am not using AWE. I am seeing SQL Server memory counter total = target almost 12 hours after start up.
96% Cached Buffer Pages Distribution on the Report with 84% of the db_buffer_percent coming from one particular database. I see where an entire 8.5GB user table has been loaded into buffer with index type heap. In fact the top 9 objects in that particular database are HEAPED user tables, all with buffer_mb greater than 1,000 mb. Is this normal? About every week we are receiving complaints of slowness from the app users. Someone has recommended locking pages in memory. I am going to look into this. Thank you!
-
If you are at the target memory, then you are fairly close to what is needed.
Having heaps can be a problem for performance, but not necessarily. Is there a reason you don't have clustered indexes on these tables? Clustered indexes do allow for potentially quicker access to data that is looked up with a non-clustered index.
Slowness might be because of poor design, poor queries, or something else. It isn't necessarily memory. You could have the table loaded into memory, but if you need to access it 100x to satisfy a poorly written query, it will still take time to process the query.
-
Locking pages in memory is definitely something to consider enabling if you are seeing Windows trim the working set from SQL Server. If this were happening you would see sudden drops in Page Life Expectancy (PLE) as well as entries in the SQL Error Log saying as much. There is a great debate on the topic of whether to enable it by default on x64 systems. Personally I only enable it if I am seeing the specific behaviors I mentioned but someone I respect a lot has written a great article on ths topic and cautiously recommends (if you monitor your server memory use very closely) enabling it by default:
Great SQL Server Debates: Lock Pages in Memory by Jonathan Kehayias, 12 December 2011[/url]
That said on the general debate, on Server 2003 running SQL 2005 memory trims imposed by Windows were much much much more prevalent than they are on SQL 2008+ on Server 2008 R2+.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply