Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Memory Settings Help Please Expand / Collapse
Posted Tuesday, December 18, 2012 10:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:23 AM
Points: 233, Visits: 810
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!
Post #1397918
Posted Tuesday, December 18, 2012 10:58 AM



Group: Administrators
Last Login: Today @ 2:55 PM
Points: 34,371, Visits: 18,589
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.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1397934
Posted Friday, December 21, 2012 5:42 AM



Group: General Forum Members
Last Login: Today @ 2:36 AM
Points: 7,933, Visits: 14,357
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

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
Post #1399356
Posted Wednesday, January 9, 2013 3:21 AM

Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 3, 2016 5:19 AM
Points: 1,144, Visits: 3,432
I would definitely build clustered indexes on 1GB+ heap tables and would seriously consider applying compression to those indexes.

The SQL Guy @ blogspot


About Me
Post #1404643
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse