SQL Server 2008 Performance issue

SQL Server 2008 is slow occasionally during the day. Is it possible to collect some data and identify the bottleneck for the problem ?

Tks
SQL-2008-Performance.png
AXISHKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ramesh Babu VavillaCommented:
Available MBytes are 727 which must be 10% of installed RAM ,32 GB is the Initial RAM in it.
did you place the memory CAP on the Server. if yes then how much.
check the Page Life Expectancy of the Server which must be more than 300.

Check for missing indexes and rebuild or reorganise the required Indexes
check the processor queue lengt
Scott PletcherSenior DBACommented:
Based on that picture, you need more RAM.  You could theoretically tune SQL and maybe get away with the same amount of memory, but tuning will be a lengthy process, so during that time you'd need more RAM anyway.

32GB is not much RAM nowadays.  Try going to at least 64GB and then cap SQL at, say, 44 initially and see how that works.
Surendra NathTechnology LeadCommented:
@Scott, @SQL

I think the DBA may have set the Minimum server memory to be 31GB, in that case it will always all the memory in that system.

Can you please follow the below article and find the minimum memory and maximum memory used
http://technet.microsoft.com/en-us/library/ms191144(v=sql.105).aspx

If the minimum memory is set to 31 GB as I suspected, please change it to 29 GB and give the system a breathing space, so OS will not interfere with the SQL work.

THanks
Surendra
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

AXISHKAuthor Commented:
Attached please find our current memory setting.

Frankly, I want to evaluate the current hardware of the SQL server.  eg. Should I need more CPU, memory or faster disk ?

How to collect these data to tell which parts of hardware should I invest ?

Tks
Processor.png
Memory.png
AXISHKAuthor Commented:
Attached please find the screenshot for SQL server during non business hour. CPU has dropped to lower level but memory is still stay at 31GB.

Will there be a possibility that some tasks taking up the resource(I/O, CPU, memory) during the office hours ? Again, need to identify the root causes in order to present the budget upgrade to management.

Tks
Window-Performance.png
Scott PletcherSenior DBACommented:
The RAM staying at 31GB is not suprising, since SQL will not give up RAM just because its own RAM use drops; it only gives up RAM if something else in the system needs it.  This is for better efficiency, since releasing and acquiring memory is a fairly expensive process.

You also need to be aware that SQL can actually more memory that the amount specified by the "max server memory".  That setting actual applies to SQL buffer memory only; SQL can also allocate some memory outside buffers, although buffers will be most of SQL's memory usage.

I still maintain my view that this SQL box needs more RAM.  You need to leave a minimum of 3GB that SQL is not allowed to use.  Although my preference if you can afford it is still as above: upgrade to 64GB, and cap SQL at 44 initially.
AXISHKAuthor Commented:
TKS
Beside memory, how to estimate disk and cpu performance?
Scott PletcherSenior DBACommented:
You can use the system view "sys.dm_io_pending_io_requests", a tool such as SQLIO, and a performance trace, particularly: PhysicalDisk Object:Avg Disk Queue Length; Avg Disk Sec/Read & /Write; Disk Reads Per Sec & Writes Per Sec.
geek_vjCommented:
>>Beside memory, how to estimate disk and cpu performance?
Task manager is not the right rool to review the memory usage of SQL Server.
Instead, try to get the values from the below Perfmon counters
1. SQLServer:Memory Manager: Total Server Memory (KB)
2. SQLServer:Memory Manager: Target Server Memory (KB)

Please post the values of the above two counters so that we can help you better.

Reg, CPU & Disk performance, the best way is to collect perfmon counters again which correspond to CPU Utilization & Physical, Logical disk reads & writes and analyze them using a standard tool called PAL which automatically analyzes and provides you the details of where there are memory/cpu/disk issues

You can find more details here -
http://technet.microsoft.com/en-us/library/cc768048.aspx

Hope this helps!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AXISHKAuthor Commented:
Thanks.

Have more information on using system view "sys.dm_io_pending_io_requests" , SQLIO, and a performance trace to tell more about the SQL performance.

Great thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.