• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

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
0
AXISHK
Asked:
AXISHK
4 Solutions
 
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
0
 
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.
0
 
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
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
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
0
 
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
0
 
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.
0
 
AXISHKAuthor Commented:
TKS
Beside memory, how to estimate disk and cpu performance?
0
 
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.
0
 
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!
0
 
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.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now