0

I'm not sure if this is the right place to ask this question, so apologies in advance if not.

Problem I'm facing is: One of my server's signal wait floats at around %35 and won't go down.

Top 3 waits are:

SOS_SCHEDULER_YIELD (55%)

OLEDB (10%)

ASYNC_NETWORK_IO (6%)

CPU utilization of the instance is at around 22%.

What might be causing this to happen?. Where should I look at to investigate further? I'm on SQL Server 2012.

Thanks all.

  • 1
    What leads you to believe this is abnormal? I assume there is a workload running on the server, right? – Jon Seigel Nov 4 '14 at 15:15
  • Hey Jon.From what I gathered from online.. Sure there is a workload. ~350 transactions/sec and ~100 batch requests/sec. The db size is rather small (6 gb) and we have a very large memory installed. – Stackoverflowuser Nov 4 '14 at 15:21
  • My first question is are you really facing performance issue or its just that you see high SOS scheduler waits ? – Shanky Nov 4 '14 at 18:46
  • None of the wait types are a problem intrinsically. You don't need to "fix" them if you are happy with the current performance. All that SOS_SCHEDULER_YIELD means is that tasks exceed their 4ms CPU quantum from time to time. If your queries run for >4ms that's to be expected. There is no fix except for tuning queries. – usr Nov 4 '14 at 19:30
2

Based on your comment about the "unwieldy" SP I'd almost guarantee that is at least part of your problem. Try running the following query replacing DBName and SPName as appropriate.

USE DBName
GO

SELECT SUBSTRING(text, statement_start_offset/2,
        CASE WHEN statement_end_offset = -1 THEN 999999 ELSE
            (statement_end_offset-statement_start_offset)/2-1 END)
    ,*
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) query
WHERE query.dbid = db_id('DBName')
  AND query.objectid = object_id('SPName')

This will break down your SP into pieces and you can sort by one of the elapsed_time, logical_reads columns, or total_worker (cpu) columns to decide what specific commands you want to work on first. The actual command will be the first column.

By breaking it down like this you can start picking off the low hanging fruit and speed the over all SP a bit at a time. This doesn't of course take the place of reviewing the whole thing at once. For example it will help to reduce the speed of a query that's being run a dozen times in the SP, it will help even more if you can figure out a way to run it only once (personal recent experience).

Now to be fair this doesn't answer your original question and it is more meant as a response to the comment and a helpful way to deal with a "problem" sp.

  • Thanks Kenneth. Much helpful. Not a complete resolution for my problem but still it helped. Marking as answer to close the thread. – Stackoverflowuser Nov 5 '14 at 9:44
1

Starting with the highest and moving down:

SOS_SCHEDULER_YIELD

This wait type is usually caused when you have queries performing huge scans. I'd suggest checking to make sure you have the proper indexes in place and you don't have any out-of-date statistics resulting in an inefficient query plan.

OLEDB

OLEDB waits indicate that your query has to reach out somewhere remotely, usually a linked server. If you're seeing performance issues with this, consider figuring out if there are performance issues on the remote server or making some attempt to bring that data local (replication, etc.).

ASYNC_NETWORK_IO

This one signals that SQL Server is waiting for a go-ahead to send data across the network. Your culprit is likely to be some application pulling data row-by-row instead of pulling the entire result set at once.


Obviously, you're going to want to focus on SOS_SCHEDULER_YIELD. Like I said, checking your indexes and statistics and analyzing your query plans are probably the best way to go about reducing your waits in this case.

You can find more info about all of these in this wonderful post by Paul Randal and more in-depth information about SOS_SCHEDULER_YIELD in this wonderful post also by Paul Randal.

  • Thanks Neghtsro. Helpful comment indeed. But I have already gone through these steps. Nearly all data of the db is in memory at the moment. Those articles are in my bookmarks as well :) – Stackoverflowuser Nov 4 '14 at 15:25
  • Are you seeing performance issues? If nobody's complaining, these may not actually be issues. Waits don't necessarily have to be bad. – Neghtasro Nov 4 '14 at 15:27
  • Man I had to replicate the db for load balancing :( There are 2-3 of them on separate physical servers. – Stackoverflowuser Nov 4 '14 at 15:29
  • You may want to edit information about your environment and workload into the question. These things can have very different meanings depending on the context, and the more info to work with the better. – Neghtasro Nov 4 '14 at 15:35
  • Thanks mate. The db is unwieldy. A rather cumbersome sproc runs very (very) frequently and I highly suspect it is the culprit but optimizing it is out of the question as it has grown out of control over the time. – Stackoverflowuser Nov 4 '14 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.