So we have a situation where an application does an item lookup. On average that item lookup calls 3 or 4 stored procedures.
On average each of those stored procedures runs for about 5-10ms. However, intermittently and for no apparent reason, sometimes they run for a much longer period. Generally when this happens all 4 run for a longer period. And by longer I mean anywhere between 1 and 2 seconds. Therefore the total time for an item lookup that is usually a few milliseconds suddenly becomes 6 to 8 seconds.
Things we have looked at so far:
- I am not a DBA but my trusted DBA's tell me that all indexes are in place, all have been rebuilt, stats updated, etc.
- CPU/Memory contention - there does not seem to be any memory contention on the servers. These servers have 4GB RAM. SQL max memory is set to 3GB but appears to only be using about 1.5GB based on some analysis we have done by tweaking this number and monitoring the available memory in the OS. Even with the max memory set to 3GB the OS shows around 800MB of RAM available so it seems there is plenty for SQL to claim if it required it. While observing a trace and perfmon together no significant CPU or memory spikes are observed during periods where we see this behavior.
- Disk contention - Average disk queue lengths are not getting above 2 with any regularity and disk sec/read and disk sec/write are both below 0.003. in addition running a perfmon with a profiler trace we don't observe any disk metrics spiking at the time of this behavior.
This behavior occurs across over 1000 of the same servers with the same kind of workload. (These are branch servers). If on a given day there are 1000 lookups we might observe somewhere between 20 and 50 of them displaying this behavior. Many of these servers are a good few years old but without being able to identify the root cause I don't want to blame this on the underlying hardware. I don't believe this is related to any recent changes. The problem has likely been around for a long time but has only recently become a priority in terms of getting it resolved.
Any thoughts and ideas on where to look next would be appreciated. I am not a DBA but I have both DBA's and Sysadmins available to look at any aspect of this.