November 3, 2010 at 4:28 pm
Hi All ,I have a stored procedure which calls many procedures.
The issue is this runs long than it is supposed to be.Need performance tuning.
We try to avoid all possible ways(with our knowledge)
When I run this
Select wait_type,waiting_tasks_count,wait_time_ms from sys.dm_os_wait_stats
order by wait_time_ms desc
The results are these.
Wait Type Waiting TaskCountWaiting time ms
CXPACKET 114572972619025546
PAGEIOLATCH_SH 10220928161321953
LAZYWRITER_SLEEP 192419125725453
SQLTRACE_BUFFER_FLUSH31327125308484
SLEEP_TASK 2300988030809531
WRITELOG 44894921746500
SOS_SCHEDULER_YIELD 379086989790484
LATCH_EX 176651598539156
PAGEIOLATCH_EX 12163688096625
IO_COMPLETION 6173907861109
SLEEP_BPOOL_FLUSH5890347611656
LCK_M_S 37237426937
ASYNC_NETWORK_IO1816775382015
LOGBUFFER 1699344440265
BROKER_TASK_STOP4112068812
BROKER_RECEIVE_WAITFOR4755109
PAGEIOLATCH_UP 12204498125
PAGELATCH_SH 82341414656
PAGELATCH_EX 18290974278546
CMEMTHREAD 1859817242343
LCK_M_SCH_M 2910171921
MSQL_XP 32904171250
PAGELATCH_UP 143552143718
EXECSYNC 68562453593
Any advise to improve the performance .??Thanks for your help.
November 3, 2010 at 4:33 pm
Demin, you're aware that's not proc based, right?
To help with the query(s) in question, we'd need to see schema, script, and execution plans. Check out the 'optimization' link in my sig to help us out there.
EDIT:
CXPACKET 114572972 619025546
Reduce your server level MAXDOP to 1/2-1/4 the processors you have available on that server. Look for specific queries constantly hitting this, and possibly reduce those queries further.
PAGEIOLATCH_SH 10220928 161321953
Optimize your drive mechanics. You're hammering your RAID. Look for massive table scans in your procs, procs that grab more data than they need, and double check your RAID build, make sure you've got dedicated spindles and/or aren't on VM and ending up with a data throttle.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 3, 2010 at 10:59 pm
you can also set profiler trace and check other child SPs which might be cause of bad performance.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 5, 2010 at 2:03 pm
Best answer is hire a performance tuning guru to give your system, application, databases, etc a review. It is astounding what a qualified individual can identify in just a few days... plus you can get the benefit of mentoring to learn how to monitor/tune better in the future.
Your waits are aggregate, and as such are not really helpful for tuning. But I would venture a guess that you have a really poor IO subsystem, too little RAM or poor indexing (or perhaps all 3) based on what I see.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply