Pratyus avatar image
Pratyus asked

Performance Tuning

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

  1. The load to SP is same for each day. That means the SP processes almost same numberof records each day.

  2. Apart from the SP, I found we have the Full backup scheduled each day for the same database where the SP is trying to select/Insert data. In SQL 2005 i think this is not an issue as long as we do not perform any DDL operations.The concerned database is in Full Recovery model.

  3. I also found there are some log backups also going on in that same time frame for different databases.

  4. I queried the DMV sys.dm_os_waiting_tasks and below are the results

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

  1. I queried sys.dm_os_performance_counters and found below on an average

BufferCacheHitRatio Page life expectancy 99.41537562 842

Lazy writes/sec 190177 Checkpoint pages/sec 2403696
Target Server Memory (KB) 6291456
Total Server Memory (KB) 6291456

  1. Min memory is configured to 0 and max to 6GB

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image CirqueDeSQLeil commented ·
Good answer Grant +1
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
I hope one day I'll be on the same planet as you mate :)
0 Likes 0 ·
Pratyus 1 avatar image
Pratyus 1 answered


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?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.