Hi folks,

I am a system analyst, trying to find the effect of DB latency in our systems. Is there a way to generate DB latency in the order of milli seconds.. ?

My application & db are in separate boxes. Same switch.

I have tried the following.
1. Disabled disc write cache.
-was able to generate db latency of 28ms

2. Tried to play around with the memory & cpu/processor available for the SQL server.
3. Disabled in memory caching.
-Generates latency in the order of secs. But I need it it in milli secs.

4. Introduced network latency to the DB machine.
-delaying response & simulating DB latency. This works but is not accepted by the developers.

So I have to either find a configurable parameter in DB which creates latency in millisecs or a 3rd party tool which may generate latency in DB.

Any inputs are appreciated .

Cheers,
-Vinod

So.. you want to build in some controlled delay on purpose?

WAITFOR (Transact-SQL)

Yes, that is right..

Well... I wouldn't go for changing memory/cache settings or any of that: usually they are set to maximize performance while maintaining stability/integrity. I don't know how things work for you, but here the admins are usually quite pleased after delivering a server. You may find yourself in some predicament while trying to return to the original settings.

It also sounds like it's a temporary (test) situation with a specific predicted or at least controlled delay, instead of delay that could be anywhere between this and that.
Also, for measuring and reproducing it's nice to have something that you can predict and re-use at a later time on another platform while still having the same abilities.

I guess I'd go for WAITFOR in the stored procedures: I'd just jam the statement in there. As for the tables and views... don't know.

Do you have a specific issue you are working on?