Memory Settings Help Please

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Memory Settings Help Please

Memory Settings Help Please

Rate Topic

Display Mode

Topic Options

Author

Message

Vertigo44

Posted Tuesday, December 18, 2012 10:32 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:44 AM
Points: 209, Visits: 655

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

Steve Jones - SSC Editor

Posted Tuesday, December 18, 2012 10:58 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:30 PM
Points: 31,436, Visits: 13,751

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

opc.three

Posted Friday, December 21, 2012 5:42 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 6,737, Visits: 11,791

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1399356

Sean Pearce

Posted Wednesday, January 09, 2013 3:21 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 2:27 AM
Points: 350, Visits: 1,340

I would definitely build clustered indexes on 1GB+ heap tables and would seriously consider applying compression to those indexes.

http://thesqlguy.blogspot.com/

Post #1404643

« Prev Topic | Next Topic »

Permissions