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.