If the cache filling up is the cause - the following should provide a useful
look at whats feeling it up -
SELECT total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS
statement_text,qs.creation_time,last_execution_time,execution_count
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.creation_time desc
Your looking for the "same" query listed many times in the above's output -
by "same" I mean queries that only differ by the values that would
be parameterized... Your wanting to see as few rows as possible
with execution_count = 1. According to my notes, on the system I had this
happen, before adding cfqueryparam, the average(execution_count) was 1.2 -
afterwards the average was in the thousands.
On Mon, May 16, 2011 at 1:13 PM, Scott Weikert <li...@alphageek.cc> wrote:
>
> What I'm finding now, in the last little bit, is that in the case of a
> content-save triggering the hang, the content *does* seem to save - the
> queries being the "larger" ones as far as raw data being moved around.
>
> I'm in the process of poking about some older code that executes after
> those queries and param-ing them up, to see that helps.
>
> Errors: When a hang hits, I don't seem to get any SQL errors - no
> timeouts and the like - just running a simple query in the query
> analyzer tool, it just spins until it finally resolves, however long it
> takes.
>
> As far as the sql logs, my server guy hasn't indicated anything special
> showing up.
>
> On 5/16/11 10:48 AM, Charlie Stell wrote:
> > Other than an eventual timeout thrown by CF - are you getting any SQL
> errors
> > when this situation arises? (from either cf trying to run a query or when
> > trying to execute a query via analyzer) Do any errors show up in the SQL
> > logs?
> >
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344550
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
