September 1, 2016 at 12:54 am
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.
September 1, 2016 at 8:59 am
Usually intermittent performance issues are caused by:
* blocking
* bad parameter sniffing
For the first one, I would set up some monitoring. With SQL Server 2005 your options are not many: I would set the blocked process threshold and capture the blocked process report event.
For parameter sniffing, you should look in the cache to see if you have plans with embedded parameters changing over time. If you have a DBA team, they can do that for sure.
-- Gianluca Sartori
September 2, 2016 at 2:59 am
Thanks for suggestions.
We have created some versions of the SP's to mitigate the parameter sniffing - testing already.
Will look into blocking as well - has been investigated before but worth further investigations.
October 20, 2016 at 6:44 pm
kreilly 50203 (9/2/2016)
Thanks for suggestions.We have created some versions of the SP's to mitigate the parameter sniffing - testing already.
Will look into blocking as well - has been investigated before but worth further investigations.
Would you mind sharing the strategy that you used here and if it resolved your issue? I too would guess to a bad cache plan for at least one of the procedures.
----------------------------------------------------
October 20, 2016 at 11:48 pm
:blush:
Sorry - not much useful in our resolution.
This issue was related to a service broker job that was running on the server very frequently. Most of the time it caused no issues but every now and again it seemed to block everything. We never really got to the bottom of why. It didn't need to run as frequently as it was so we could fix the issue with a new schedule. Understanding the details of why it had this intermittent impact lost priority.
October 21, 2016 at 11:03 am
No worries at all.
Thanks for posting back 🙂
----------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply