How to control a long-term request
We are currently managing a web application that runs stored procedures, one of which runs whenever the user searches for a specific item. Unfortunately, one day we ran into a very serious problem for this SP as it was lasting for a long time, so it made the database work really badly. This ended up causing many problems for our applications. I found out that a long running request was causing the problem by running this database script:
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
where
sqltext.[Text] NOT LIKE '--''check%'
ORDER BY req.cpu_time DESC
So we did this to perform KILL [SESSION_ID], and after a few seconds our application was back to normal. Now we would like to proactively deal with this type of problem, so when I talk about management, I would like to know if it is possible for the web application to terminate this session gracefully (without causing subsequent problems) after a certain period or time, or is it handled in SQL itself Server?
If anyone else needs further clarification, please feel free to comment.
source to share
You really need where sqltext. [Text] NOT LIKE '-' 'check%' 1.sys.dm_exec_requests also has a start time column where you are not currently using a where clause. Go during startup so that it doesn't go to the beginning of all data 2. Look at the data and try to change the where clause accordingly. 3. Pull the execution plan for proc to make sure it does a table scan, which is not good.
Here are the steps to get the execution plan Step 1 Please change the dates, proc db name
select distinct top 1000 qs.plan_handle,o.name,d.name--,--ps.database_id
from sys.dm_exec_query_stats qs
,sys.dm_exec_procedure_stats ps
,sys.objects o
,sys.databases d
where qs.last_execution_time > '2017-03-29 17:06:42.340'
and qs.last_execution_time < '2017-03-30 18:19:45.653'
and ps.sql_handle = qs.sql_handle
and o.object_id = ps.object_id
AND o.name = 'Your proc name here'
AND d.database_id = ps.database_id
AND d.name = 'database name '
Step 2 Set the output to grid and save as .sqlplan You get a link, if you have enough permissions you can click on it and it will open. Make sure the query output options are set so there is enough space for the xml output.
select query_plan
from sys.dm_exec_query_plan (copy your handle here from step 1, do no use quotes)
source to share