asked on

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

SOLUTION

Ramesh Babu Vavilla

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

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.

SOLUTION

Surendra Nath

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AXISHK

ASKER

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

AXISHK

ASKER

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

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AXISHK

ASKER

TKS
Beside memory, how to estimate disk and cpu performance?

Scott Pletcher

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.

ASKER CERTIFIED SOLUTION

geek_vj

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AXISHK

ASKER

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.