October 15, 2010 at 3:47 am
Hi!
On production sql server with 8 cores (2x quadcore) and 16GB memory running window server 2003 R2/ sql server 2005 9.00.4035.00 occasionally happens that CPU goes to 100% (all cores are on 100%). In activity monitor it can be seen that all processes are blocked by a process which is listed in several rows (8 or more rows). Almost every row (maybe each row - don't know exactly) for this process has wait type CXPACKET.
I set MAXDOP (general server setting) to 4 and it didn't help. Now when this happens CPU goes to 50% (4 cores are on 100%) everything else is the same.
Statistics and indexes are updated/rebuilt daily.
Any idea?
bye and thanks in advance,
Damijan
October 15, 2010 at 4:27 am
Is it OLTP or OLAP?
OLTP databases that use 100% CPU equals (most often) evil query. Find the offending query and fix it.
With a typical OLTP workload, CPU should stay low.
If it's a DataWarehouse DB, I think you should get used to having high CPU load.
Huge tables and huge aggregations need CPU.
-- Gianluca Sartori
October 15, 2010 at 4:36 am
It's OLTP and CPU is usually low... What would be the best way to find out offending query?
October 15, 2010 at 4:49 am
It will probably be listed in the top 20 queries by CPU:
SELECT TOP 20
query_stats.sql_handle,
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
FROM (SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset / 2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
) as query_stats
GROUP BY query_stats.sql_handle
ORDER BY 2 DESC ;
-- Gianluca Sartori
October 15, 2010 at 4:54 am
To find the most expensive queries you could for example use code like this:
SELECT top 20
max_elapsed_time/1000000.0 as max_duration,
total_elapsed_time/1000000.0 as total_duration,
execution_count,
char(13)+char(10)+
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle))
+char(13)+char(10)+'------------'+char(13)+char(10)+char(13)+char(10) AS query_text
FROM sys.dm_exec_query_stats
where max_elapsed_time > 1000000
ORDER BY 1 DESC
It will return the 20 queries with the longest elapsed time that are in the cache.
October 15, 2010 at 6:06 am
Just remember that the DMV referenced above, while an excellent approach to finding the offending query, is dependent on that query being in cache. If you want to be sure of capturing the long running queries and start to tune the right thing, I'd suggest putting a server-side trace to work. It's time & effort to manage the data collected, but worth it in the long run.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 15, 2010 at 6:49 am
Yup, here's where to start and be done in 1-2 hours TOPS :
October 15, 2010 at 7:29 am
My suggesting is
set:
sp_configure 'max degree of parallelism', '1'
Best,
Best regards,
Michał Marek
October 15, 2010 at 7:42 am
michal_marek (10/15/2010)
My suggesting isset:
sp_configure 'max degree of parallelism', '1'Best,
But, what if a given query really benefits from parallelism? Now you've just taken it away.
However, I do notice that more queries are made parallel than should be. Rather than turning off parallelism completely, I usually set the Cost Threshold for Parallelism higher. The default, 5 I think, is WAY too low for OLTP systems. I bump it up to 25 or 35, depending on the system, the queries running against it, etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 16, 2010 at 1:44 am
You are will avoid high number of wait type CXPACKET.
sp_configure 'max degree of parallelism', '1' << set global MAXDOP.
Other thing:
SET CPU affinity mask for SQL Server, uncheck 1 CPU.
Best,
Best regards,
Michał Marek
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply