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 |
|
Shriroop
Starting Member
2 Posts |
 Posted - 2008-10-23 : 05:39:48
|
| Hi All,Not sure it this is the correct section to post the problem, so advise me if it is the wrong section.We are facing a problem on the SQL Server 2005 of high cpu and memory usage.The installation of SQL Server 2005 Enterprise Edition is on Windows Server 2003 SP2. It sits on a VM machine and the VM Ware version is ESX 3.5.0.Now there are few things we already tried, like as the VM Ware support if there are any problems from their end. They checked everything and said there is nothing wrong at the VM front.Second step taken was running the SQL trace and then asking the Database Tunning Advisor using the trace file about the performance improvement. There were a few indexes suggested on the few of the biggest tables of the DB. The indexes were created and there was a change in performance. The CPU that stayed high constantly started coming down in usage. The other steps that we took were ticking the option of "Use AWE to allocate Memory" option in the Memory section of the SQL Server Instance's properties. We also increased the Virtual Memory of the Drive on which SQL Server is installed. The paging file size is between 2046 - 4092 MB.It was all fine for a week but than it has started to stay high more often. During this week the average CPU usage was around 60%.We are running a live DB of our production system on this server and quite crucial to get this sorted out. The production system is a Intranet based application which also hosted on the same server through IIS 6.0 and there are about 30 users who can be accessing the application simultaneously.Please can anyone help me by giving me any other suggestions that would help improve the situation. Thanks to advise. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-23 : 07:19:17
|
| Is this 64-bit SQL Server? Look at the standard reports for top 5 queries by CPU usage and diagnose it.Look at perf mon for CPU counters and correlate with SQL profiler to track down what is causing it. |
 |
|
|
buzzi
Starting Member
48 Posts |
Posted - 2008-10-23 : 17:37:14
|
| do what sodeep was saying, also go through this article that i got from nethttp://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspxsee the query below, that will tell you cpu intense queries, select top 50 sum(qs.total_worker_time) as total_cpu_time, sum(qs.execution_count) as total_execution_count, count(*) as number_of_statements, qs.plan_handle from sys.dm_exec_query_stats qs group by qs.plan_handle order by sum(qs.total_worker_time) descyou can use this to get the query from the sql handle displayed by running above querySELECT text FROM sys.dm_exec_sql_text (<sql-handle>hope this helps |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-23 : 17:54:51
|
| Here is what I do to track exact culprit.http://www.mssqltips.com/tip.asp?tip=1212 |
|
|
|
|
|
|
SQL Server 2005 - High CPU & Memory Usage