April 20, 2011 at 3:40 am
Experts,
I have a server with 32 GB RAM and it has 3 instances on it. As a new DBA for this project that did not look good to me in the first place as one instance is for OLTP and another for sharepoint. All of the instances are using dynamic memory and chances are fair that sometimes my OLTP instance suffesr due to that.
I need to allocate a fixed memory to my OLTP instance. but how to determine that is something tricky.
Is there any counter that I can watch for a couple of weeks so that I know how much the OLTP needs on an average.
Thanks in advance
April 20, 2011 at 7:11 am
Can anyone help me please.
April 20, 2011 at 8:56 am
Not really - there are some wait types that would be reduced by having more available memory, but the question is, so what?
Which instance needs more memory is more a question of what's acceptable performance from a user perspective for each application.
I'd try using the average level that the dynamic memory management has ended up with during peak times for your OLTP application as a starting point for your fixed values and adjust as necessary.
April 20, 2011 at 10:51 am
Have a look at this link, it's for SQL 2005, but also applies to SQL 2008:
http://technet.microsoft.com/en-us/library/cc966401.aspx
"...Consistently low average page life expectancy. See Average Page Life Expectancy Counter which is in the Perfmon object SQL Server Buffer Manager (this represents is the average number of seconds a page stays in cache). For OLTP, an average page life expectancy of 300 is 5 minutes."
So "page life expectancy" is a good counter to go by.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 20, 2011 at 11:03 am
Look at Target Server Memory for each instance. This is what each SQL instance would like to have. Chances are it will be high, but you might get a proportional idea of what each one wants and then divvy things up from there.
April 21, 2011 at 12:30 pm
How many processors in a server , did u set the max_worker_thread,OS version 32bit or 64bit,All databases are on different HDD or drive not partition
select st.text,sp.* from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
where sp.cpu > 100
order by sp.cpu desc
paste result of this query
which one is more critical OLTP,Sharepoint,third one?
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 25, 2011 at 4:49 am
I had collected target server memory for oltp instance and page life expectancy for few days. Target server memory was pegged at around 18 GB on an average. Page life expectancy is nice in non-business hours but drops to 60 sec in business hours.
So I am going to throw 22 GB of memory to the OLTP instance. SQL will acquire all of it slowly and will not release it unless and until something else requests it. For me the key will bt to keep an eye whether the buffer cache hit ratio and pahe life expectancy has improved.
April 25, 2011 at 7:25 am
Make sure you set max memory for all instances in there and leave 2GB for the OS. Watch the OS for paging too much (page file activity), as that can be a sign the OS needs more. The max for each instance is a guess, but you can tweak it over time to see if you can balance the load.
Don't change the max worker thread setting. Despite the post above, the default of 0 is fine for most systems, as SQL Server will automatically determine what's appropriate. This is typically an advanced setting you change if you have isolated performance problems to a lack of enough threads for the workload. It doesn't appear that's the case here.
May 2, 2011 at 6:56 pm
Follow the great advise from Steve. He is correct.
First make sure that each instance has been configured with Max memory otherwise if one instance uses max memory on the box, the rest of them will starve for the memory.
You need to monitor the following counters
SQLServer:Memory Manager -- Target Server Memory (KB)
SQLServer:Memory Manager -- Total Server Memory (KB)
SQL Server Buffer Manager : PageLifeExpectancy
Don't rely on SQL Server Buffer Manager : BufferCache Hit Ratio. It could be misleading.
Also if it's a dedicated SQL Server then leave 2-4 GB to OS and then share the rest among the sql instances.
Also make sure that you assign Lock Pages in memory to the SQL Server instances along with the Max memory setting.
Use PAE and AWE if its a 32 bit system. They are not needed on 64-Bit systems.
To monitor the paging on the server use the following counters
PagingFile: %Usage
PagingFile: %Usage Peak
Also make sure that the Page File has been sized appropriately.
You memtioned... One instance is OLTP, One is MOSS , What abt the other ?
Thank You,
Best Regards,
SQLBuddy
May 2, 2011 at 11:36 pm
Check for Process:Working set counter for each SQL server instance process. If this value is less than SQL Server's Memory Manager:Total Server memory counter , than you have an issue that the memory is getting trimmed, which should not happen.
Also see to it that Memory: Pages Output/sec should be as close to zero as possible.
Tweak the max server memory of sql server , till you achieve a optimum option
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply