September 14, 2013 at 11:52 am
Hi,
I'm noticing very low Page Life expectancy <5 mins very frequently when ever a stored procedure is running.
How to find where the problem inside this procedure. I want to what are steps I should do to find the problem?
Please see the attached the procedure.
September 15, 2013 at 4:09 pm
Presumably the problem is cause from that dynamic SQL statement the procedure produces. The code is very painful to read with all that uppercase, and the programmer does not seem to understand that dynamic SQL should be parameterised to for several reasons, of which one is readability.
Also, presumably the performance depends on the actual parameters, but I could guess that an index or two could help. But that depends on how the actual query looks like - which you need to capture as well as the query plan.
I did note one thing which can be a performance bummer:
SELECT T.c.value('../../@name', 'varchar(200)'),T.c.value('.[1]', 'varchar(200)')
FROM @OFFERSEARCHPARAMETERS.nodes('declare namespace ns="http://www.abc.com/EW";/ns:searchParameters/ns:SearchParameter/ns:values/child::node()') T(c)
The parent-axis notation performs lousy, and this could easily be solved by changing the argument to nodes(). However, since this is only for the search parameters, I doubt that this is your performance killer.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply