Hi There, Recently I inherited a system to fine tune a daily process. We have some daily jobs, kicked through Ctrl+M. The ctrl+M inturn kicks of some SPs in SQL Server to start the process of loading data from some staging tables.
For some months now we notice a bizzare behaviour of the SP.On an average basis the SP takes around 5 to 10 mins max to complete but some days it takes unexpectedly high time (more than 30 mins) to complete.This happens 4/5 times per month. There is no particular trend to this.
I started digging into it and found below
wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
LAZYWRITER_SLEEP 3637502 489851781 17908843 158343 SQLTRACE_BUFFER_FLUSH 61214 244863156 4296 8921 BACKUPIO 16992369 195891265 5171 858656 BACKUPTHREAD 15514 128407281 18270578 1671 CXPACKET 4166094 71388812 234609 3149218 BACKUPBUFFER 14674481 62259406 2093 1369171 ASYNC_IO_COMPLETION 109 60945187 11564156 0 PAGEIOLATCH_SH 2173302 12225234 1531 120812 ASYNC_NETWORK_IO 2062746 8822296 2109 186062 WAITFOR 52 4812000 120000 0 SOS_SCHEDULER_YIELD 11632693 3859937 1015 3859765 SLEEP_TASK 11386335 3023468 1156 2173656 WRITELOG 829663 2892625 1750 27406 LATCH_EX 880043 1991796 3453 496156 PAGELATCH_EX 17933572 1684875 703 1504421 PAGEIOLATCH_EX 255506 1069765 968 4390 OLEDB 13712448 705671 12171 0 SLEEP_BPOOL_FLUSH 292189 603656 203 19390 IO_COMPLETION 172834 280375 859 2687 LOGBUFFER 8536 131531 765 4906
BufferCacheHitRatio Page life expectancy 99.41537562 842
Lazy writes/sec 190177
Checkpoint pages/sec 2403696
I do not think there are any issues with the SP since its running 80% of time fine. I suspect there are some resource issue in the server.
I will appreciate if anybody can throw any kind of light to go forward and fix the issue.
It does sound like you're seeing contention between resources, aka, blocking. There are a number of ways that you can monitor for blocked processes. Your question doesn't say which version of SQL Server you have, but if you're looking at 2005/2008, you could start with the Blocked Process Report. There are a number of other ways that you can monitor blocking through scripts. This search on the scripts in SQL Server Central shows a number of them.
Unfortunately the formatting on the output from sys.dm_os_waiting_tasks is pretty hard to read. I'd suggest you might take a look at this white paper from Microsoft that shows how to use waits and queues as your primary means of identifing slow performance.
Once you figure out the problem, start working on the execution plans to figure out how to fix it.
Another approach is to set up a server side trace, gather statistics and then identify your problem queries by run time, resources uses, most frequently called, etc.
answered Mar 30 '10 at 08:40 AM
Grant Fritchey ♦♦
Is it possible to monitor the server and capture what is running when the SP is performing badly, i.e. is it during office hours, or is this run in the middle of the night?
If you can, then it may be easier to see what is blocking or what the proc is waiting on, as it runs. If not, then a different approach may be needed.
answered Mar 30 '10 at 06:25 AM
Kev Riley ♦♦
the server is sql 2005. The jobs are scheduled midnight.but i can say except the backup process no other jobs are running at that time.
major waiting stats are BackupIO/BACKUPTHREAD /Backup buffer/cxpacket/ASYNC_IO_COMPLETION.. Since this is a very secured system, running trace needs some approval...Apart from that can we do/conclude anything?
answered Mar 30 '10 at 02:19 PM