March 7, 2011 at 7:03 am
Hi all,
One of my Update query is running continuously with 'sleepTask' wait and there are no other queries running concurrently.
In between it is running a query which is a query ran by us. It is being run by the sqlserver itsself. Below is that query:
update FE_In_Box set FE_work_unit_ID = null where FE_work_unit_ID = 0 /* get the current work unit size, with a default value of 100. */
Update query ran by us sometimes is taking 20 min to complete which is the regular time to complete but sometimes it is goes on running continously with showing sleep task wait and during this time some times the above query (update FE_In_Box) is being ran by sqlserver.
Anyone please suggest what does this query mean and how to resolve this problem.
Thanks,
Vamsy
March 7, 2011 at 2:17 pm
It's hard to know exactly what it might be based on the information at hand. Have you looked at the DMV sys.dm_os_waiting_tasks? You can combine this with other DMVs such as sys.dm_exec_requests and sys.dm_exec_sql_text with the offsets from sys.dm_exec_requests to see exactly which statements are being run and which ones are waiting. You might be suffering from parallelism and waits. Have you looked at the execution plans for the query to see what is happening?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 7, 2011 at 10:54 pm
Thanks for your reply Grant..
I ran the below query while the stored proc is executing:
select w.session_id,
w.wait_duration_ms,
w.wait_type,
w.blocking_session_id,
w.resource_description,
s.program_name,
t.text,
t.dbid,
s.cpu_time,
s.memory_usage
from sys.dm_os_waiting_tasks w
inner join sys.dm_exec_sessions s on
w.session_id=s.session_id
inner join sys.dm_exec_requests r on
s.session_id=r.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) t
where s.is_user_process=1
and the OUTPUT is attached in the excel.
Thanks,
Vamsy
March 8, 2011 at 5:17 am
SLEEP_TASK means it's waiting for some generic task to clear before it continues. This means it's waiting on the lazy writer or something like that. Is this process causing blocking or something on the system? If not, it's just running long. You should drill down on the query itself.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 8, 2011 at 6:28 am
Grant,
Its been resolved .
I ran sp_recompile 'spname' to recompile the stored procedure when ever its been called as this is a report that will run once in a week.
By doing this recompilation the stored procedure has been executed in 3 mins.
Are there any disadvantages for sp_recompile (without considering recompilation)
Thanks,
Vamsy
March 8, 2011 at 8:22 am
Basically you just asked it to create a new execution plan. If this is a seldom called, complex, long-running query, paying the cost for compiling a new plan, especially since it appears that the plan can be skewed towards poor execution (probably a result of bad parameter sniffing), then no, in those situations recompiling is best. In general recompile is not something you want to do because it's added overhead and the SQL Server optimizer will do a fine job creating a plan. But, as you can see, that's not always the case.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply