August 3, 2009 at 6:00 am
i m having performance degradation(CPU utilization going 100%)because of excessive wait ,
following is the detail of wait
Wait CategoryNumber of WaitsWait Time (sec)% Wait Time
Sleep33904601 3901274.842 60.46%
Wait TypeNumber of WaitsWait Time (sec)% Wait timeMax Wait Time (ms)Avg Wait Time (ms)
LAZYWRITER_SLEEP22480611920187.75 49.22% 1328 854.2
SQLTRACE_BUFFER_FLUSH478981191595149.11%43904000.1
SLEEP_TASK2814930043830.7961.12%50001.6
SLEEP_BPOOL_FLUSH302825821300.50.55%5007.0
SLEEP_SYSTEMTASK14.7960.00%47964796.0
what is the soln for this?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
August 3, 2009 at 7:04 am
You wouldn't by chance be running the Profiler GUI against this server? If so, I'd suggest turning that off and using scripts to create a server-side trace routine. I say this because of the trace buffer event in the wait list that, if I'm reading it correctly, is taking about 50% of the wait time.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 3, 2009 at 10:43 pm
No, i m running the trace from my local pc.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
August 3, 2009 at 10:55 pm
Actually wait type OLEDB is taking more time,
that is because of use of linked server,
but how to resolve this?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
August 4, 2009 at 2:46 am
Try to identify the Costliest Queries, processes. I also suffered alot because of this CPU - 100%.
Fine tune the queries and procedures for better performance as per SQL ANSI.
I did the same and am good to make that decrease. Try to use some scripts to find the costliest processes.
Try below Query
-------------------------------------------------------------------------------------------
select top 50
qs.total_worker_time / execution_count as avg_worker_time,
substring(st.text, (qs.statement_start_offset/2)+1,
((case qs.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
order by
avg_worker_TIME desc
----------------------------------------------------------------------------------------------
-
Win.
Cheers,
- Win.
" Have a great day "
August 5, 2009 at 5:45 am
sanketahir1985 (8/3/2009)
No, i m running the trace from my local pc.
Through the graphical user interface? If you're running a trace through TSQL, it's not running on your local machine, it's running on the server. If you're running a trace through the graphical user interface, the GUI, then you're going to be creating performance problems as you monitor. Read this for pointers[/url] to all the research explaining exactly what the problem is.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 24, 2009 at 8:26 am
Sanket,
More CPU utilization tells that you are lacking of strong index key selection. Just look at your explain plan and see whether all table fetching is going for Index Scan or Index Seek. Try to modify or add index based on your key columns , certainly you will have very less percentage of cpu utilization
-- Harish
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply