Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
learntsql
524 Posts |
 Posted - 2009-12-14 : 02:35:57
|
| Hi..Todya i moved my database from one server to another;i.e Now i have dedicated Application and Database serversbut the problem is when i look into CPU Usage memory its showing1,702,169K(hitting its peak continously) and users are complaining that application is slow and causing time out error.and Currenetly My report server is running on application server only(its also installed SQL Server).AS a SQL DBA how to analyze the performance issues as these.How can i estimate min and max usgae memories of any process especially SQL Server.Is my report server causing the application to run slow.How to identify what backend processes taking more CPU memory continously.?Should i enable AWE option currently its disabled?.Plz. help me in this.. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-12-14 : 14:01:05
|
| The SQL Server will, by default, grab as much memory as it can so the "continuous peak" is not surprising. How much memory do you have in the system?For the CPU, what percentage is is showing as a normal and what is the peak? If it consistently is above 65-70%, you are looking at a bottleneck. Also, you can use Task Mamager (Processes Tab) to see which processes are consuming how much CPU.For AWE, my first question is are you using 64 bit processors? If yes, then you don't NEED AWE though some will suggest turning it on anyway.=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
 |
|
|
learntsql
524 Posts |
Posted - 2009-12-14 : 23:53:29
|
Thank you Bustaz Kool,This is my windows System configurationMicrosoft Windows Server 2003Standard EditionService Pack 2Intel(R) Xeon(R) CPU5120 @ 1.86GHz1.86GHz,4.00 GB of RAMand for SQL Machine i am using Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86).it's hitting its peak(100%) starting from 1%. on SQL Machine.How can i exactly find avg. cpu usage.When I open Task manager CPU usage  .SQLserver Process is taking 2GB of RAM (approx.)next is W3wp.exe-->89,000K,ReportingServices.exe-->79,000.Please tell me is there any built in procedures/function/tables where can i analyze my SQL Server Performance.Plase help me ASAP.Thank you. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-12-15 : 14:31:15
|
| I am a little confused about what your actual error is. Does your statement of "it's hitting its peak(100%) starting from 1%. on SQL Machine" refer to the Memory or the CPU usage? But let's move forward anyway...SQLServer is taking 2GB of memory - This is exactly what I'd expect and it does NOT indicate a problem. As I mentioned before, SQL will grab as much memory as it can. In a 4GB environment, the default configuration reserves 2GB for the OS and let's everyone else fight over the remainder. On a dedicated SQL Server this is fine but if there are other applications contending for resources, this can cause problems. You can limit the max memory that SQL will use but I thought that you had a dedicated server.There is a trick you can do to limit the OS to 1GB of memory but if you are hardware bound, it's probably better to just buy more resources rather than massage things at the edges.4GB - The good news is that with 4GB you do not need/want to enable AWE. 4GB is the maximum natural addressing range of a 32-bit processor so you are using all of the memory available to you. The bad news is that 4GB is not a lot of memory for a busy SQL Server. If you end up adding more memory you will need to enable AWE since it looks like you have 32-bit processors. Before we do that, however, let's find out if you are limited memory-wise.You want to run the Performance Monitor (AKA PerfMon) and add some specific counters. Under the SQLServer:Buffer Manager heading, add the Page Life Expectancy counter. You want to see if it is consistently at or above 180. Some will use 300 as the limit here but let's not dicker. It's okay if it dips below that number as long as it can recover. (BTW, you'll hear people say to use the Buffer Cache Hit Ratio counter but I've never found it to be useful.)While we have Performance Monitor open, let's add the Processor: % Processor Time counter. Monitor this number over time to see if it stays consistently below 65%. Again, spikes of activity are okay as long as it returns to a normal range.You can also add the Sytem: Processor Queue Length counter. This also returns information on how "busy" the CPUs are. It should reinforce the findings you get from the % Processor Time counter.HTH=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
|
|
|
learntsql
524 Posts |
Posted - 2009-12-16 : 01:34:29
|
| Thnaks Bustaz Kool,The only thing now left my side is i have to work around all these scenarios.I should be more in depth myself in analyzing performance counters. |
|
|
|
|
|
|
|
|
Performance counters