0
I was recently working on a report against several primary tables that have about 1.8 million records. Several iterations of the report would take many minutes to return. That wouldn't work for our clients so I would hit F5 (browser refresh) to cancel the report. After several times doing this the server started to noticibly slow down. The Windows app event log shows what I consider the typical "request active since..." message.

7327123,Application,Serena Business Mashups Application Engine,FAILURE AUDIT,xxxx.yyy.com,4/29/2016 12:28:37 PM,3,None,"Message text not available. Insertion strings: Audit Failure occurred in file: 'CacheMgr.cpp', line 552. The following request from user 'pthompson' at address 'aa.bb.cc.dd' has been active since 04/29/2016 10:38:14: Thread number 15, 4 active, 20 total Request: 'ReportPage&Template=reports%%2Flist&projectid=4&querytype=2&reportid=32000&reporttype=25&tableid=1016' Db Query Start time Not Set, End Time Not Set Query: '' Flist&projectid=4&querytype=2&reportid=32000&reporttype=25&tableid=1016' Db Query Start time Not Set, End Time Not Set Query: '' "

Is the db query still going on and if so is there any way to effectively kill the query without restarting SQL server or IIS? It is, after all, the production server.
Responses (2)
  • Accepted Answer

    Jeff Malin
    Jeff Malin
    Offline
    Wednesday, May 11 2016, 04:21 PM - #Permalink
    0
    View running SQL processes using the following code. Issue a "Kill" command once you're sure which is the culprit.

    A "Cancel" button on reports would be a great Idea if not already submitted:


    SELECT s.session_id
    ,r.STATUS
    ,r.blocking_session_id 'blocked by'
    ,r.wait_type
    ,wait_resource
    ,r.wait_time / (1000.0) 'Wait Time (in Sec)'
    ,r.cpu_time
    ,r.logical_reads
    ,r.reads
    ,r.writes
    ,r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)'
    ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
    (
    CASE r.statement_end_offset
    WHEN - 1
    THEN Datalength(st.TEXT)
    ELSE r.statement_end_offset
    END - r.statement_start_offset
    ) / 2
    ) + 1) AS statement_text
    ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
    Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
    ,r.command
    ,s.login_name
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.last_request_end_time
    ,s.login_time
    ,r.open_transaction_count
    FROM sys.dm_exec_sessions AS s
    INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
    WHERE r.session_id != @@SPID
    ORDER BY r.cpu_time DESC
    ,r.STATUS
    ,r.blocking_session_id
    ,s.session_id
    Like
    The reply is currently minimized Show
  • Accepted Answer

    Wednesday, May 18 2016, 10:42 AM - #Permalink
    0
    Unless you are seeing a performance degradation, I would just let it run. Killing the SQL connection might mess up the next thread that is trying to use the db connection pool. Both SQL and SBM have other threads to process additional requests. If you decide it needs to be stopped, restarting IIS would be sufficient.

    For speeding up your report query, make sure you include fields that are indexed or add new indexes on key fields of your query to ensure your query does not have to search the entire set of records to obtain the results.

    Note that there is a trade off, since adding additional indexes on a database table will slow down submits into that table.
    The reply is currently minimized Show
Your Reply

Recent Tweets