question

Leon avatar image
Leon asked

Wrong Execution Plan getting picked sometimes

Hi, The following query sometimes runs efficiently; gives quick results, CPU and IO utilization is also up to the mark. But sometimes the query takes very long time to execute. At that time I do not observe any blocks as well. Also the CPU and IO is too huge for running this query. It seems the engine picks up wrong execution plan some times. This query is fired through some product. So there is no scope to change the query. Any suggestions for the engine to pick up right execution plan? select i . bpd_instance_id as instanceId , i . instance_name as instanceName , bpd . name as bpdName , istatus . name as instanceStatus , t . subject as taskSubject , tpriority . name as taskPriority , t . due_date as taskDueDate , t . attached_form_ref as taskAttachedInfoPathFormRef , t . attached_ext_activity_ref as taskAttachedExtActivityRef , t . task_id as taskId , tstatus . name as taskStatus , tuser . user_name as assignedToUser , tpriority . ranking as taskPriorityRanking from msadmin.lsw_task t with ( nolock ) inner join msadmin.lsw_bpd_instance i with ( nolock ) on t . bpd_instance_id = i . bpd_instance_id left join msadmin.lsw_task_status_codes tstatus on t . status = tstatus . status_value left join msadmin.lsw_bpd_status_codes istatus on i . execution_status = istatus . status_id left join msadmin.lsw_priority tpriority on t . priority_id = tpriority . priority_id left join msadmin.lsw_bpd bpd on i . cached_bpd_version_id = bpd . version_id left join msadmin.lsw_usr_xref tuser on t . user_id = tuser . user_id where ( t . status in ( '11','12' ) and ( t . user_id = 5909 or t . task_id in ( select t . task_id from msadmin.lsw_task t with ( NOLOCK ) inner join msadmin.lsw_usr_grp_mem_xref m with ( NOLOCK ) on t . group_id = m . group_id where m . user_id = 5909 and t . user_id = -1 ) or t . task_id in ( select t . task_id from msadmin.lsw_task t with ( NOLOCK ) inner join msadmin.lsw_grp_grp_mem_exploded_xref x with ( NOLOCK ) on t . group_id = x . container_group_id inner join msadmin.lsw_usr_grp_mem_xref m with ( NOLOCK ) on m . group_id = x . group_id where m . user_id = 5909 and t . user_id = -1 ) ) ) order by taskDueDate , taskPriorityRanking , instanceId , taskId
execution-planoptimizationengine
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leon avatar image Leon commented ·
Is it a good practive to update a stat nightly on all the tables with FULL SCAN? will it help to boost the performance? Also what should be the frequency to rebuild/reindex? (Weekly?)
0 Likes 0 ·
Leon avatar image Leon Leon commented ·
Thanks for the solution
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
It's all hard coded values in the WHERE clause and the JOIN criteria, so you can't be suffering from bad parameter sniffing. It has to just be that your statistics get out of date which leads to a bad plan. I would suggest looking into more manual updates of your statistics on whichever tables and indexes are causing the most problems in the problematic execution plan. You could try [plan forcing][1]. It looks like the query qualifies. But it's hard to do and doesn't always work. You do know that NOLOCK can lead to completely incorrect data, right? Missing rows, extra rows. It's a very dangerous query hint to use. [1]: http://technet.microsoft.com/en-us/library/ms190727(v=SQL.105).aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leon avatar image Leon commented ·
hi. thanks for the comments. Statistics are all up to date. Rather, I disabled the manual update statistics job which was running after every 5 min for a particular table. The table is very heavily updatable. After disabling the manual Update stats jobs on that table, now showing good performance. Do I require to update stats frequently on highly updatable production table? can anybody suggest?
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
It's possible that you need the stats updated that frequently, yes. Although, that's usually indicative of other issues. For example, those IN clauses with an OR in that query are likely leading to all sorts of issues. I know you say you can't modify the query, but the query is the problem. You might be better of breaking it into two with a UNION ALL instead of the OR. Also test turning the IN statements into derived tables and join against them instead of using IN.
0 Likes 0 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
I recommend to use the reindex solution developed by ola hallengren ( http://ola.hallengren.com) His SP only rebuilds indexes which are fragmented at a certain level, so it runs quite quickly. You also have the possibility to do a statistics update with this script. I run this script every night. But as a general catch-all solution: don't you have "auto update statistics" enabled?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.