I’ve been back doing SQL performance tuning work this week. This morning I had a good reminder that whenever you measure something you have an effect on it. The procedure I was working on was at the head of 46% of all blocking chains on the site yesterday. When I looked into it, the #1 reason it was blocking was the call:– DEV ENV ONLY : Log this query call
EXEC p_XXX_DAF_LogQueryRequest ‘p_XXX_DAF_L2M_CTSGetGroup’
(name modified to keep details private). This call is at the beginning of every procedure on the site. It counts the number of times the proc has been called at that nesting level and updates a datetime value that remembers when the proc was last called.
But the irony is that 75% of the work in the proc I was working on and the #1 reason for it blocking, was that call and nothing to do with the real work the proc was doing. And by the comment, it’s only meant to be used in the dev environment anyway.