[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Sybase IO vs execution time

Posted on 2010-03-24
7
Medium Priority
?
609 Views
Last Modified: 2012-05-09
Hello experts,

Sometimes I have seen that some queries in Sybase takes very little amount of IO, but takes huge time to execute. What could be the reason for this? Any input with proper explanation would be helpful.

Thanks
0
Comment
Question by:joy_m
  • 3
  • 2
6 Comments
 
LVL 13

Expert Comment

by:alpmoon
ID: 28462857
It depends of what IO you are looking at. Are you checking only physical IO? Or is there any RPC going to another server?

Maybe you should provide the nature of query as well.
0
 
LVL 3

Author Comment

by:joy_m
ID: 28507387
Hi,

I am considering the logical as well as physical IOs here. Its not possible to put the query over here, because I am not considering any particualr query.

So, will it be possible to give some input on this?

Thanks
0
 
LVL 13

Expert Comment

by:alpmoon
ID: 28632990
If it is the case, I think you should run sp_sysmon and work on the output:

sp_sysmon '00:10:00' -- for a 10 minute intreval

Also have you checked whether there is blocking during these periods? Or if it includes updates, deadlocks with some other transactions?
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
LVL 3

Author Comment

by:joy_m
ID: 28634633
Hi,

How do I check whether there are blockings or updates or deadlocks during the period as you said? Could you please explain a little bit more?

Thanks
0
 
LVL 13

Expert Comment

by:alpmoon
ID: 28697894
If you set server configuration parameter 'print deadlock information' to 1 to monitor deadlocks:

sp_configure 'print deadlock information', 1

Then you can see deadlocks in errorlog.

To monitor blockings is not that easy. You can check it by sp_who or some other tools when the queries are running. sp_who output has a blk_spid colum. If it has a spid, it means that process is blocking the process on that line. Or you can write a script to check sysprocesses table regularly and report blockings.
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 2000 total points
ID: 28880721
Let's take a step backwards. I/O is not the only work ASE must do to run a query.

The query must be scheduled onto a CPU. If there are many more processes than CPUs (engines) - which is usually the case - even a query issuing only a single I/O might still wait a while before it can run. You can see this at runtime by running a simple sp_who... processes in a "runnable" state are waiting to get onto an engine.

Even once on an engine, the query must not be blocked on the I/O(s) it wants to do by locks held by other processes. You can see this at runtime with sp_lock, also sp_who will show you if a process is waiting on a lock held by another process. Note that whenever a process does a lock wait, it is immediately thrown off the engine, and so might wait a while before it will run again. Sp_object_stats will show you the worst lock waits in your server.

Even if it isn't blocked waiting on a lock, it may have to do a physical I/O, that is, read from disk rather than from memory. This also causes the process to be thrown off the engine, and it may way a while before it will run again. Sp_sysmon will show you how much this is happening (Cache section, cache misses vs cache hits).

Even if it didn't have to read from disk, it may be blocked trying to grab the "spinlock" that protects the cache from two processes trying to do things to the same page or row at once. In a very busy server this spinlock contention may cause even just a one-I/O query to wait a bit. Again, sp_sysmon will report on spinlock contention per cache.

Even once it has read the page from cache, it has to sent a result over the network to the client. This will almost certainly throw the process off the current engine and it will wait to get a chance to run on its "network engine" (one picked at random to load balance when the process first connected). Once on its network engine, it will then issue its network send, where it may wait or be delayed by any network congestion or event on the path from the server to the client. It will also be thrown off its network engine as soon as it sends a packet, and will wait in a queue to run again on it if there are more to send.

Basically, while it's true that physical I/Os to disk are one of the slower things that Sybase might need to do, there are many many other steps before all query results have been sent to the client that issued the query. In a busy and/or under-resourced and/or misconfigured server, these other waits could be significant - like waiting several seconds for a query that only has to do a single I/O. If you get stuck behind a blocking lock you will (by default) wait forever. Some application code forces all processes to single-thread through one table or one section of code.

Since you said it takes a "huge" time to execute, the more likely explanation is blocking locks. Run sp_object_stats for a reasonable period of time (say, 5 or 10 minutes) during a busy period. It may tell you some interesting things in terms of total time spent blocked by locks.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Can you run Linux on a Windows system?  Yep.  Here's how.
This article explains how to move an Exchange 2013/2016 mailbox database and logs to a different drive.
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question