April 11, 2012 at 1:51 am
I've a procedure which insert data into a table from the join of two tables based on the search criteria as per few parameteres. There are multiple such insert statements in the procedure from same set of tables. Now this procedure we run in parallel for different set of parameters. I am experiencing some issue where any one instance of the procedure gets stuck at one point and does not progress further. After rebuilding the stats at times it works and at times it doesn't. When it doesn't we go for rebuild indexes and then it works. However i guess this gets sorted because of regenerating the better execution plan and it should not be because of rebuilding indexes as we do a weekly reindexing of all tables and the amount on inserts, updates and deletes in these tables are usual as it happens in Prod environment.
Here is this "with recompile" option with procedure which i think we should not use. Any suggestions on that ?
April 11, 2012 at 1:58 am
We need sample data and your SP to troubleshoot more on this case. Actually recompile what I understand every time execution will create a new query plan. It can better used with situation. Looking at your scenario recompile may be a better idea. But still need to validate with sample data and SP.
---- Babu
April 11, 2012 at 3:36 am
with recompile i think you can go ahead in case your procedure is the way like it going to create new execution plan most of time
Raj Acharya
April 11, 2012 at 4:50 am
Too many guesses without seeing the actual code involved. In general, I don't recommend using a hint like RECOMPILE unless there's a driving reason for it. Do you know what it was put there? I can't say more without seeing the query & execution plan. It sounds like resource contention of some kind.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 11, 2012 at 5:49 am
Hi Grant. As per my understanding the 'with recompile' option to be used only when there are different range of parameters passed to the procedure. As long as there are fixed data params, it's not required. Correct me if I am wrong.
April 11, 2012 at 6:03 am
sqlnaive (4/11/2012)
Hi Grant. As per my understanding the 'with recompile' option to be used only when there are different range of parameters passed to the procedure. As long as there are fixed data params, it's not required. Correct me if I am wrong.
I wouldn't say that it's a range of parameters that requires recompile, rather it's that your data is unevenly distributed that might require a recompile on each run. The classic example is that most of your values return a very few number or rows (or very large) and there are a few that return very large numbers of rows (or very small, it's the skew, or uneven distribution that's the key) where using recompile might be a good option. But, you have to weigh that with the cost of the compile itself.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 13, 2012 at 4:47 am
Thanks for clearing my point Grant. That certainly says that in my scenario, "with recompile" option is required. However in this case how can I stop SQL to create efficient plan always ? At times, it seems it creates bad execution plan and gets stuck at one point. While during same time, parallel executions work perfectly. [This means definitely it's not matter with table's indexes and stats]
April 13, 2012 at 10:17 am
If you're getting bad plans, it can still be because of the stats.
If the plans you're getting are working in an inconsistent manner, then recompile might not be the best solution. Other solutions for bad parameter sniffing include: using local variables to get a consistent but generic plan, using OPTIMIZE FOR some value that gets you a consistent and specific plan, or OPTIMIZE FOR unknown, which goes back to consistent but generic.
You'll have to test with parameter values that are generating a bad plan for you.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply