Does ReOrg Job change query execution plan?

Question

Does ReOrg Job change query execution plan?

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
March 24, 2017 at 10:44 am

#322024

Hi Experts
I found that suddenly for a procedure the query execution plan changed and it started timing out and taking longer than expected time. There has no aparent change which has happened to the database/proc.
Any suggestion to check about how the procedure execution plan might have been changed and causing this effect? Or any other underlying suggestion?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

SQL-DBA-01 - Friday, March 24, 2017 10:44 AM
Hi Experts
I found that suddenly for a procedure the query execution plan changed and it started timing out and taking longer than expected time. There has no aparent change which has happened to the database/proc.
Any suggestion to check about how the procedure execution plan might have been changed and causing this effect? Or any other underlying suggestion?

Without a third-party tool in place or SQL Server 2016 and it's AWESOME Query Store you are out of luck with finding cause of plan change.

Can you post the query?

I will venture a guess that it is parameter sniffing/data value distribution related. Any other's care to hazard a guess?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

There's a few things that it could be, the first two things that come to mind would be statistics and what's called parameter sniffing.

Statistics play an important part in the engine building an execution plan. An index reorg itself probably wouldn't affect an execution plan because it doesn't affect statistics. An index rebuild also rebuilds statistics so that could affect a query plan.
https://www.simple-talk.com/sql/performance/sql-server-statistics-basics/
Have you seen a big difference in actual vs expected rowcounts in the execution plan? Here's some good advice to troubleshoot:
https://www.sqlskills.com/blogs/kimberly/what-caused-that-plan-to-go-horribly-wrong-should-you-update-statistics/

Parameter sniffing can come into play if the first time a stored proc is executed with a parameter that (again related to statistics) the engine chooses a plan that is optimal for this parameter but is a bad choice for the vast majority of other possible parameter values. A good article on this topic is here:
https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

Thanks guys for your update.

PTackling paramter sniffing by using Option Recompile to recompile the proc everytime could be a costly operation. Any other underlysing advise?

Thanks.

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

SQL-DBA-01 - Friday, March 24, 2017 10:44 AM
Hi Experts
I found that suddenly for a procedure the query execution plan changed and it started timing out and taking longer than expected time. There has no aparent change which has happened to the database/proc.
Any suggestion to check about how the procedure execution plan might have been changed and causing this effect? Or any other underlying suggestion?

This is a part of the reason that I stopped doing any index maintenance at all on my main production server way back on the 17th of January, 2016 (more than a year ago). Rebuilds and reorgs do a very nice job of repacking indexes to their given Fill Factor size. If it's at 0 or 100, there's no room left on pages. For clustered indexes that are following the "ever increasing/immutable" rules (to name 2 of the 5 rules), it's not so bad. For Non-Clustered indexes, it's terrible. Pages are full and the indexes are not in the same order as new inserts and so right after a rebuild/reorg, the system slows down until the page splits stop.

I've never seen an index reorg cause a change in execution plans by itself. Rebuilds are a different story. They inherently rebuild stats on the index which causes a recompile of the procs that use the index. Between the fully packed index pages causing page split on inserts and a possibly bad execution plan caused by the recompile at perhaps the worst time, it can make quite a mess as inserts take a whole lot more time and the selects have to wait on all that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

Is there any possibility to read from extended events to find when recompile occurred sql server @procedure level?

Thanks.

Sue_H SSC Guru Points: 90860 More actions · Answer 6

SQL-DBA-01 - Monday, March 27, 2017 3:24 PM
Is there any possibility to read from extended events to find when recompile occurred sql server @procedure level?

Don't think you can do this after the fact - don't see anything in system_health.
You can create a session to monitor for recompiles using the event sql_statement_recompile. If you want to filter on just stored procedures, filter object_type = proc

Sue

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 7

SQL-DBA-01 - Monday, March 27, 2017 3:24 PM
Is there any possibility to read from extended events to find when recompile occurred sql server @procedure level?

you can look at the DMV: sys.dm_exec_procedure_stats
which has a column: cached_time
so you'll know when the plan was cached into memory.
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-procedure-stats-transact-sql