December 17, 2018 at 9:40 am
Hi expert,
Can someone please post here some quires (or simulate recompilation) to practice compilation/recompilation? I would really appreciate.
Thanks,
December 17, 2018 at 10:38 am
Tac11 - Monday, December 17, 2018 9:40 AMHi expert,
Can someone please post here some quires (or simulate recompilation) to practice compilation/recompilation? I would really appreciate.
Thanks,
Check out DBCC FREEPROCCACHE (not in production).
You can use it to force all queries to recompile.
What information are you hoping to obtain?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 18, 2018 at 6:10 am
Or, since we're talking 2016, there's always:
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE
However, same rules apply. Don't do this in production.
You can also try using the WITH RECOMPILE hint on statements or on procedures.
Or, you can always interleave data definition language (DDL) statements with data manipulation language (DML) statements. This leads to compiles and recompiles.
For a whole bunch of content on RECOMPILE events, I'd suggest my blog.
"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
December 19, 2018 at 10:02 am
@phil, just play with AdventureWorks database.
December 19, 2018 at 10:04 am
Thanks Grant, Any particular script do you suggest for compilation/recompilation trouble shoot?
December 19, 2018 at 1:16 pm
Tac11 - Wednesday, December 19, 2018 10:04 AMThanks Grant, Any particular script do you suggest for compilation/recompilation trouble shoot?
Nope. Any script will compile or recompile using any of the methods we've outlined. Follow the link to my blog. I show how to monitor compiles & recompiles using extended events.
"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
December 20, 2018 at 7:21 am
Tac11 - Monday, December 17, 2018 9:40 AMHi expert,
Can someone please post here some quires (or simulate recompilation) to practice compilation/recompilation? I would really appreciate.
Thanks,
What do you mean by "practice compilation/recompilation"? Are you trying to detect when procs or scripts are being recompiled or ???
--Jeff Moden
Change is inevitable... Change for the better is not.
December 24, 2018 at 10:04 am
Yes, Jeff.
December 24, 2018 at 11:09 am
Tac11 - Monday, December 24, 2018 10:04 AMYes, Jeff.
It's pretty easy to do such a thing. All you need to do is pick a query and change even the white space in it and it'll cause a recompile. Writing a bit of simple dynamic SQL to change a literal in the WHERE clause would generate a bunch of code, each of which would cause a recompile.
As a bit of a sidebar, there was some GUI ORM code on one of our systems that did such a thing. When I checked the code in profiler, it was only taking about 100ms to execute but the return to the screen was taking between 2 and 22 seconds each and every time. Everyone was blaming everyone including the database/server (server or database must be slow. Must be bad stats or fragmented indexes. Etc, etc.) and the folks in OPs (must be a network problem).
When I looked at the code from the ORM, you could see that it was changing literals in the executed code each and every time and I knew that (causes recompiles) had to be the problem. Of course, no one would believe me (especially the well meaning but incorrect folks on the front end) and so I had to prove it. Once I proved it, they were able to make the necessary changes to properly parameterize the SQL thereby preventing the constant recompiles and reusing the parameterized code, instead.
To save you some time in proving such a thing, here's the article and the code I used to prove it. Special thanks go out to Jonathan Kehayias because it allowed me to find problems that no one even perceived of but it explained a whole lot about the background "noise" I was seeing for CPU, Memory block usage, and Disk I/O on PerfMon.
https://www.sqlskills.com/blogs/jonathan/identifying-high-compile-time-statements-from-the-plan-cache/
As a bit of a sidebar, it also helped to increase security because a lot of the non-parameterized code was created using concatenation of user inputs. Translation... they were a source of possible SQL Injection attacks that no one knew of and needed to be repaired for that if nothing else.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply