September 28, 2010 at 1:58 pm
We have upgraded memory from 16gb to 24 gb for sql (total is 32gb). Now we want to allocate more memory to SQL to see if it helps in anyway. I am told to create a baseline before and after change which would help us in making a decision to allocate more memory to sql or not. Mentioned below are the following counters i will be using.
i)Memory Object: Available Bytes
ii)SQLServer:Memory Manager: Total Server Memory (KB)
iii)SQLServer:Memory Manager: Target Server Memory (KB)
iv)SQL Server Buffer Cache Hit Ratio
v)Processor %Privileged Time
vi)PhysicalDisk\Avg. Disk Queue Length
vii)Physical Disk: %Disk Time
I request other DBA's to suggest if this is good enough or do i need to add or remove any counter here.I always get lost while doing performance monitoring since there is not one solution, so please advice only if you have tested it before. Thanks.
September 29, 2010 at 6:47 am
Yeah, I'd add a few counters to that.
SQLServer:Buffer Manager:Page Life Expectancy
SQLServer:Buffer Manager:Lazy writes/sec
SQLServer:Memory Manager:Memory Grants Pending
Memory:Pages/sec
Memory:Page Faults/sec (really useful for trend analysis)
Memory:Pages Input/sec
Memory:Pages Output/sec
If you want to know if added memory is helping, you need to watch how the memory is managed, not just whether or not memory is available.
"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
September 29, 2010 at 10:12 am
thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply