Solved

SQL Server 2008 Performance issue

Posted on 2013-12-23
10
356 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query with cast 38 51
SQL Insert parts by customer 12 42
MS SQL + Insert Into Table - If Doesnt Exist 9 47
Index and Stats Management-Specific tables 8 21
In this article I will describe the Backup & Restore 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 article I will describe the Copy Database Wizard 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.

809 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