Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
select task_state,count(*)
from sys.dm_os_tasks
group by task_state

I ran the above statement on a SQL Server instance, and found it had about 633,000 records.

task_state     
-------------- -----------
RUNNABLE       2
RUNNING        32
DONE           633115
SUSPENDED      99

How can I Close/Kill the useless tasks?

The MDW data collector have about 4000 page allocate in tempdb per time.

And this cause the IO pressure when server in busy time.

This is production server, We do not want to restart the service. And the version number is 11.0.3000.

The Max Worker Count is :1216
CPU Count:48
Hyperthread ratio:12
MaxDop: 8

Scheduler_Id is 0 - 47 and the amount of rows are average. and other columns are null.

share|improve this question
1  
Better ask: "What causes those tasks?". The remedy will follow from the root cause. –  usr Aug 16 '13 at 11:57
1  
Can you check what is the MAXDOP setting and total number of processors (you can use coreinfo utility from sysinternals) + max worker thread on the server instance and if this instance is standalone or shared ? –  Kin Aug 16 '13 at 12:08
1  
Make sure you are logged in when editing your question. –  Paul White Aug 16 '13 at 13:00
1  
Do the majority of the "Done" rows have a value in session_id/request_id or do they have NULL in those columns? –  Kenneth Fisher Aug 16 '13 at 14:17
1  
@GrantWu check this out sqlperformance.com/2012/11/system-configuration/… and mssqlwiki.com/2012/11/22/sql-server-numa-load-distribution. Also are there any errors logged into the error log ? –  Kin Aug 16 '13 at 17:05
show 3 more comments

migrated from stackoverflow.com Aug 16 '13 at 11:56

This question came from our site for professional and enthusiast programmers.

1 Answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.