![]() In my case, this gave me not just the stored procedure, but also the specific statement that triggered the infinite recompile event. ![]() This should help you get to the bottom of your problem. It’s just a matter of taking the above SELECT statement and substituting the right values into the right places:ĭECLARE int = int = varbinary(64) = 0x03001F00E5FFB07CC6F96E019AA400000100000000000000 You still need to decode that binary SQLHANDLE value, so you’ll have to work for it. It gives you all the important bits, but since it’s coming from the log file there’s no DMV to select from. I ran into an issue where the following error was logged in the SQL Server logs: A possible infinite recompile was detected for If you have a sql_handle that doesn’t correspond to currently-executing SQL, then sp_WhoIsActive isn’t much help. SUBSTRING(t.text, (r.statement_start_offset/2)+1, ( Object_name(t.objectid,t.dbid) as ObjectName, I wish the formula were a little more straightforward, but you’ll need to do something like this: If you want to get that statement, you’ll need to use the statement_start_offset and statement_end_offset columns in sys.dm_exec_requests. If you use sp_WhoIsActive, you know that it actually determines the exact statement within that stored procedure that is running. The output of the DMF will give you (for example) the definition of the currently-executing stored procedure. You can just use the DMF sys.dm_exec_sql_text() to convert sql_handle back into SQL:ĬROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t Perhaps you’re on a server that doesn’t have sp_WhoIsActive installed, and you want to see what is running. My friend Mike Kane ( blog| twitter) has an interesting post on parameter combinations he regularly uses when using sp_WhoIsActive. It’s really easy & straightforward–if you’re not familiar with Adam’s utility, download it and check it out. In the context of currently-running processes, most people decode sql_handle with Adam Machanic’s ( blog| twitter) sp_WhoIsActive. ![]() Books Online gives the (un)helpful definition as “Hash map of the SQL text of the request.” sp_WhoIsActive If you’ve ever looked at sys.sysprocesses or sys.dm_exec_requests (or a number of other DMVs), you’ve noticed there is a column called “sql_handle” that contains some binary gobbledygook.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |