Sybase IO vs execution time

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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
joy_mAuthor Commented:

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?

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?
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

joy_mAuthor Commented:

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?

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.
Joe WoodhousePrincipal ConsultantCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.