I have a task which I run from the MS SQL Server Management Studio. I would expect it to finish in an hour and it has finished before in such time frame. The input data has not changed dramatically and so I would expect a similar time to execute. But now it seems that sql server stuck. The task has been running over night, in total like 20hrs.
When I open the task manager on the server I see that one core is running at 100% by the sqlservr.exe. The machine has 8 cores. As usual this process has taken over all of the memory ( 30GB ). The SQL Server version is 10.50.1600.1
I'm the only user on the server.
According to dm_exec_requests the session id is 55. And I can pull the correct query text via dm_exec_sql_text. I checked the dm_os_waiting_tasks and the session_id is not part of results.
Here are some more details on the task:
status: running command: UPDATE start_time: 2013-07-11 14:49:39.533 last_wait_type: SOS_SCHEDULER_YIELD open_transaction_count: 2 open_resultset_count: 1 cpu_time: 69315970 total_elapsed_time: 69594247 reads: 5139 writes: 1181434 text_size: -1
I did run the process monitor also overnight and there is not much file access to the databases in question.
Can someone tell me what more I could do?