Solved

SQL Server 2008 Performance issue

Posted on 2013-12-23
10
354 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
10 Comments
 
LVL 10

Assisted Solution

by:sqlservr
sqlservr 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

803 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