12/27/2023 0 Comments Tsql option recompileIf we expand the query_sql_text column (middle text removed for space reasons) you can see that the text includes OPTION (RECOMPILE). If we check the plan cache, you’ll notice that there is no evidence that this query has executed:ĬROSS APPLY sys.dm_exec_sql_text () ĬROSS APPLY sys.dm_exec_query_plan () īut if we look in Query Store we do see the query: I also don’t recommend freeing procedure cache in production – this is just for demo purposes.ĪLTER DATABASE SET QUERY_STORE = ON ĪLTER DATABASE SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL) ĪLTER DATABASE SET QUERY_STORE CLEAR ĭROP PROCEDURE IF EXISTS p_GetOrderInfoįirst, execute an ad hoc query, one that is not part of a stored procedure, that has the OPTION (RECOMIPLE) hint: Note that adding the RECOMPILE option to a stored procedure is not something I recommend – it means that the entire stored procedure will recompile every time it is executed. Lastly, we’ll create a stored procedure to use for testing that is created with RECOMPILE and then completely free procedure cache. I don’t typically recommend that you clear out Query Store, but we’re restoring a demo database, and this is a demo, so I want to make sure we start fresh. The code below will enable Query Store, set QUERY_CAPTURE_MODE to ALL (to understand the different various and what’s recommended for production, check out my Query Store Settings post), and then clear out anything that’s in Query Store. I’m running the latest CU for SQL Server 2017, but this is applicable for any version of Query Store (SQL Server 2016 and higher) and Azure SQL Database. We are using the WideWorldImporters database, which you can download from Github. TL DR In case you’re too busy to keep reading, the answer is yes to both. ![]() ![]() Specifically: Do queries that have the OPTION (RECOMPILE) hint go into Query Store, AND do queries in a stored procedure created with the RECOMPILE option go into Query Store? I knew the answer to the first question, and was pretty sure I know the answer to the second one, but I wanted to test to verify. Last week in our IEPTO2 class I was asked about queries with OPTION (RECOMPILE) and Query Store.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |