Solved

SQL Server 2008 Performance issue

Posted on 2013-12-23
10
362 Views
Last Modified: 2014-01-11
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
Comment
Question by:AXISHK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 10

Assisted Solution

by:Ramesh Babu Vavilla
Ramesh Babu Vavilla earned 125 total points
ID: 39737531
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39738336
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
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 125 total points
ID: 39738667
@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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:AXISHK
ID: 39738738
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
 

Author Comment

by:AXISHK
ID: 39738745
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 125 total points
ID: 39739557
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
 

Author Comment

by:AXISHK
ID: 39739602
TKS
Beside memory, how to estimate disk and cpu performance?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39740086
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
 
LVL 13

Accepted Solution

by:
geek_vj earned 125 total points
ID: 39741361
>>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
 

Author Comment

by:AXISHK
ID: 39741381
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question