August 25, 2011 at 4:33 am
hi Friends,
Recently i am facing the issue in production server which 100% cpu by sqlservr.exe .
we have enough server memory is 20GB
i have run the profiler seems maximum CPU by object is 1312
Please suggest in finding the root cause and help to solve this immediately.
Much appreciation in advance.
Rajo.
August 25, 2011 at 5:18 am
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2011 at 5:20 am
If it's THAT urgent, then I recommend hiring someone. Those articles are amazing but it takes time to assimilate them.
August 25, 2011 at 5:24 am
Ninja's_RGR'us (8/25/2011)
If it's THAT urgent, then I recommend hiring someone. Those articles are amazing but it takes time to assimilate them.
I'm available (though international work is difficult). Usually takes me 2 days to come up with a set of recommendations for performance problems and I tend to get massive improvements every time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2011 at 5:30 am
My best run was 90% + improvement on cpu, reads and duration across a normal day of prod.
The articles Gail wrote are clearly the way to go (which I used to get those results), but this might be a case where consulting with the author is the best thing to do.
It's somewhat easy to spot the problem, usually much harder to fix it.
August 25, 2011 at 6:48 am
You have to monitor the server to identify what is running slow. As a quick and dirty start point, take a look at the wait states to see what things are waiting for:
SELECT * FROM sys.dm_os_wait_stats AS dows
ORDER BY wait_time_ms DESC
After that, it's a huge, huge, topic. Books have been written on it.
"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
August 25, 2011 at 6:57 am
thanks for your replies.
yes , i executed sys.dm_os_wait_stats and found the following has high wait time
CXPACKET
LAZYWRITER_SLEEP
SQLTRACE_BUFFER_FLUSH
SOS_SCHEDULER_YIELD
how to clear these wait types
thanks
August 25, 2011 at 7:07 am
No offense, but the info you posted is useless to troubleshoot any further.
What do these 2 queries return? And DON'T do anything untill we say it's ok (easy to screw something up here).
SET IMPLICIT_TRANSACTIONS OFF
GO
CREATE TABLE #configs
(
name nvarchar(35),
minimum INT,
maximum INT,
config_value INT,
run_value INT
)
GO
EXEC sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
INSERT INTO #configs (name, minimum, maximum, config_value, run_value)
EXEC sp_configure 'cost threshold for parallelism'
GO
EXEC sp_configure 'show advanced options', 0;
GO
reconfigure;
GO
SELECT
optz.counter
, optz.occurrence
, CONVERT(DECIMAL(18,2), optz.value) AS AvgValue
, conf.name
, conf.config_value
, conf.run_value
, Uptime.DaysUptime AS [Days Server UPTIME & Last Stats Reset]
, CASE WHEN Uptime.DaysUptime < 45 THEN 'You may not have very meaningful stats because of a recent restart' ELSE NULL END AS [Stats Warning]
, CASE WHEN optz.value > conf.config_value THEN 'Cost threshold for parallelism might be too low' ELSE NULL END AS [Cost Threshold Warning]
, CASE WHEN conf.run_value <> conf.config_value THEN 'Server needs to be restarted for the setting to take effect' ELSE NULL END [Restart Warning]
FROM
sys.dm_exec_query_optimizer_info optz
CROSS JOIN #configs conf
OUTER APPLY(
SELECT
CONVERT(DECIMAL(18 , 2) , DATEDIFF(hh , create_date , GETDATE()) / 24.0) AS DaysUptime
FROM
sys.databases
WHERE
name = 'tempdb'
) Uptime
WHERE
optz.counter = 'final cost'
GO
DROP TABLE #configs
Please note the 3 4 articles I link to in this script.
/*
Cost threshold for parallelism (CXPACKET) http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/19/tuning-cost-threshold-of-parallelism-from-the-plan-cache.aspx
Paul White: Understanding parallelism http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/
Microsoft White Paper on waits http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
Next query by Paul Randal http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx
*/
WITH Waits AS
(SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'BROKER_RECEIVE_WAITFOR')
)
SELECT
W1.wait_type AS WaitType,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.WaitS * 1000 / W1.WaitCount AS DECIMAL(14, 2)) AS AvgWait_MS,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 97 -- percentage threshold
AND W1.WaitCount > 0;
GO
August 25, 2011 at 7:09 am
Rajo (8/25/2011)
thanks for your replies.yes , i executed sys.dm_os_wait_stats and found the following has high wait time
CXPACKET
LAZYWRITER_SLEEP
SQLTRACE_BUFFER_FLUSH
SOS_SCHEDULER_YIELD
how to clear these wait types
thanks
CXPACKET waits are usually an indication of parallelism running in queries. I'll bet your cost threshold is set to the default of 5. This will NOT fix your problem, but it might help some, bump the threshold up to a higher number. I'd suggest 40.
"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
August 25, 2011 at 7:12 am
Grant Fritchey (8/25/2011)
Rajo (8/25/2011)
thanks for your replies.yes , i executed sys.dm_os_wait_stats and found the following has high wait time
CXPACKET
LAZYWRITER_SLEEP
SQLTRACE_BUFFER_FLUSH
SOS_SCHEDULER_YIELD
how to clear these wait types
thanks
CXPACKET waits are usually an indication of parallelism running in queries. I'll bet your cost threshold is set to the default of 5. This will NOT fix your problem, but it might help some, bump the threshold up to a higher number. I'd suggest 40.
It might help, but I've got a gut feeling since the first post that he needs to get someone in there to trouble shoot his whole server. And my gut is rarely off :-P.
August 25, 2011 at 7:18 am
Ninja's_RGR'us (8/25/2011)
Grant Fritchey (8/25/2011)
Rajo (8/25/2011)
thanks for your replies.yes , i executed sys.dm_os_wait_stats and found the following has high wait time
CXPACKET
LAZYWRITER_SLEEP
SQLTRACE_BUFFER_FLUSH
SOS_SCHEDULER_YIELD
how to clear these wait types
thanks
CXPACKET waits are usually an indication of parallelism running in queries. I'll bet your cost threshold is set to the default of 5. This will NOT fix your problem, but it might help some, bump the threshold up to a higher number. I'd suggest 40.
It might help, but I've got a gut feeling since the first post that he needs to get someone in there to trouble shoot his whole server. And my gut is rarely off :-P.
Oh no, you're 100% dead on. This one desperately needs some assistance.
"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
August 25, 2011 at 7:33 am
please suggest can we run
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR); in production hour
or
what is the best solution to clear the wait stats
August 25, 2011 at 7:40 am
Rajo (8/25/2011)
please suggest can we runDBCC SQLPERF("sys.dm_os_wait_stats",CLEAR); in production hour
or
what is the best solution to clear the wait stats
The only reason to do that is if you've made a change in a setting, which I specifically told you not to.
Grant's advice might be good, might be completely wrong for YOU, for THAT server, for the CURRENT load.
That's why I posted 2 queries so that we could better help you.
Now keep in mind that at best we're putting a bandaid on a torn off leg. I still think you need a full medical checkup on that server(s).
August 25, 2011 at 8:17 am
Rajo (8/25/2011)
please suggest can we runDBCC SQLPERF("sys.dm_os_wait_stats",CLEAR); in production hour
or
what is the best solution to clear the wait stats
All that's going to do is wipe out the accumulated statistics, it's not going to help the performance in the slightest.
That would be like burning a bill and thinking it'll change the amount of money that you owe.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2011 at 8:20 am
GilaMonster (8/25/2011)
Rajo (8/25/2011)
please suggest can we runDBCC SQLPERF("sys.dm_os_wait_stats",CLEAR); in production hour
or
what is the best solution to clear the wait stats
All that's going to do is wipe out the accumulated statistics, it's not going to help the performance in the slightest.
That would be like burning a bill and thinking it'll change the amount of money that you owe.
Tried that once... just got a bigger bill next month and in big red letters.
Awesome example Gail 😀
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply