May 12, 2009 at 4:12 am
Dear All,
I have SQL server 2005 in my production server using for one application. Always my db will have 150 concurrent users performing operations.my server configuration :
OS:64 bit , Microsoft SQL Server Standard Edition (64-bit), Total RAM Size is : 12 Gb. out of this 12 Gb i a m using 10 gb for SQL services.exe . in this case during the peak working hours my Server CPU utilization is reaching to 100 % some times.while installing SQL Server i didn't change any default setting in configuration. Now i am facing the time out error "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
can any one suggest me whether do we need to change any configuration chages like : max work treads...
Thanks in advance if some one help me on this it would be helpful to resolve the issue.
Thanks,
Thulasi.
May 12, 2009 at 4:38 am
Is it your dedicated SQL server or shared ? If it is shared then please look into which processes taking more CPU utilization.
If your SQL server it's taking maximum CPU utilization, then try to find out maximum waittype.
During the peak CPU utilization, which SQL statement is executing ? How much CPU is taking by these statements.
Also set the max DOP to 0 in case of OLTP and equal to physical CPU in case of OLAP.
Please update
May 12, 2009 at 4:57 am
hi ajit,
Thanks for ur immediate response..
i have a dedicated SQL server for that particular application.and the waittime is always showing 0 in Activity monitor.there are few queries which is taking cpu utilization at that particular time and Most of the queries in sleeping mode.Those sleeping queries also taking CPU utilization.
thanks,
thulasi.
May 12, 2009 at 5:43 am
Hi Thulsi,
below is the dmv which will gives you total wait time by wait type. If it is indicating CPU bottel neck then you need to look into CPU cause.
Here is link which will explain you.
http://www.sqlworkshops.com/cpupressure.htm
Try to tune the bad queries which is taking highest CPU. Find out those queries by below script.
SELECT TOP 50
qs.total_worker_time/qs.execution_count as [Avg CPU Time],
SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -qs.statement_start_offset)/2)
as query_text,
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY
[Avg CPU Time] DESC
If you find the CXPACKET waittype in top order then you may change your MAX DOP setting.
May 12, 2009 at 6:17 am
hi Ajit,
by suing the same query i have identified that long running queries in the database.now i am in a process to tune those queries.and some times i am gettting the waittype like CXPACKET.
at this point of time you told that hange we need to change MAX DOP setting. could please tell where this option we can change..
Thanks,Thulasi.
May 12, 2009 at 6:24 am
You will get this option by executing sp_configure.
You can reset the value by override. As per me, if you are running OLTP operation then keep it default i.e. or if you are running OLAP operation then make it equal to physical count of processor ( Not logical).
May 12, 2009 at 6:48 am
Hello,
Refer following link for more detail:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_0evalplan/html/62021d0b-1b8d-4276-b373-a01184006d42.htm
You do have two things:
Enable AWE OPTION
or allocate fixed 4 GB to sql server
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 12, 2009 at 7:29 am
hi,
i don't have any AWE enabled in the present senario.beacuse its a 64-bit OS. and i have set max memroty as 10 GB in the sql server properties.
May 13, 2009 at 12:50 am
Yes If it is 64 bit then no need to enable AWE option. But for confirmation, please check perfmon- Total Server memory and perfmon- Target Server memory counters.
I think your issue with CPU and not with memory.
Can you check individual CPU ( Logical) utilization ? I just want to confirm that wether all load on single CPU or it is didived among all.
Also check that when your CPU utilization is high that time what is CPU queue lenght ? At any give point of time CPU Queue lenght should not more than 1-2 .
Let me know if it is helpful.
May 14, 2009 at 6:25 am
ajitgadge (5/12/2009)
Also set the max DOP to 0 in case of OLTP and equal to physical CPU in case of OLAP.
According to "Professional SQL Server 2005 Performance Tuning" published by Wrox (chapter 7 p. 180). "The recommended approach is to set it (MAXDOP) to the number of physical processors minus 1." Of course, this would require testing for your system, or if you didn't want to make a server wide change, you could always change the query with (maxdop option in BOL).
Sometimes CPU bottlenecks can be caused by the disk subsystem. You'll want to check this, as well.
steve
May 14, 2009 at 6:34 am
Hi Stev,
can you just describe how we can test if there is bottelneck in Disk subsysten and CPU utilization is high ?.
It would be great if you can explainthis.
May 14, 2009 at 7:36 am
Use PerfMon with the counter %DiskTime for the disks that your server is using. If this is greater than 20ms, then you need to look deeper into the disk.
Disk I/O (threshold 25 ms)
Add the AverageDiskQueueLength for each drive (consistently > 2 exceeds the ceiling). DO NOT add _TOTAL counter because this will flatten the results and possible hide some problems.
Add AverageDiskSeconds/Read (>20ms exceeds the ceiling)
Cause: actual disk/storage performance
Add AverageDiskSeconds/Write (>20ms exceeds the ceiling)
Cause: actual disk/storage performance
SQLServer:Access Methods Full scans/sec (>1per second exceeds the ceiling)
Cause: poor or missing index
SQLServer:Access Methods Page Splits/sec (excessive)
Cause: index maintenance and fill factor
Based on the results you may need to do several things. Where are the data/log files? What type of RAID? SAN, or array? TempDB configuration (which disk and how large?)
This is a rather vast topic and you really would need to get a reference book to fully understand and correct these issues. I couldn’t possibly cover this in a posting. By determining if DISK I/O is causing the high CPU, you will be able to focus your actions.
Good luck.
May 14, 2009 at 11:06 pm
Thanks Steve
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply