Question

有时我的申请进展缓慢。主要问题是,一些昂贵的报告正在运行。我如何看待这些报告以及如何当即杀害这些报告?

Answer 1

I always use sp_WhoIsActive from Adam Machanic for finding long running queries. sp_WhoIsActive is described in detail on dba.stackexchange.com.

虽然你也可以书写自己的文字或使用sp_who2。

Update
You are interested in the first 2 columns of the output of sp_WhoIsActive. The first column defines how long the query is running. The second column is the session_id (or SPID) of the query.
You can use KILL 60 to kill session_id 60 for example.
Have a look over here for a detailed explanation of the stored procedure.

Answer 2

你可以利用以下指挥系统来获得长期查询。

SELECT r.session_id,
       st.TEXT AS batch_text,
       qp.query_plan AS  XML Plan ,
       r.start_time,
       r.status,
       r.total_elapsed_time
FROM sys.dm_exec_requests AS r
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
     CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE DB_NAME(r.database_id) =  {db_name} 
ORDER BY cpu_time DESC;

然后,你可以使用

KILL 60

例如,杀人罪可达60岁。

Answer 3

我向各位提出几点意见,但并非都是适合你们的。

Reporting and CRUD operations must be separated. At least you can use nolock or something or run them at night and can work offline.
Check your queries because if the data amount less then the 2 000 000, the main problem is queries for many time.
Analyse the report types and if suitable for offline work, use offline system for reporting
can use mirroring or other techniques for reporting.
Best practice is always separate the databases for reporting and CRUD operations.

友情链接