Return
-
Title
Why does Spotlight SQL Activity sometimes show “procedure call” and sometimes see the “SQL Statements?” -
Description
Why does Spotlight SQL Activity sometimes show “procedure call” and sometimes see the “SQL Statements?” -
Resolution
This is how “SQL data” is retrieved.1. When we select a row in the “Sessions Grid”, we get the “Session ID” - SPID.2. Use the “spid” to query the “sql_handle” from the “sysprocesses” system table.3. If the “sql_handle” is not null, use the “sql_handle” as parameter and call the “fn_get_sql(sql_handle)” to get the “SQL Statement.”In this step we can’t ensure the information returned is not null, because the sql_handle may be no longer exists as the SQL Server could clear the information from the cache.4. If the “sql_handle” is null or the step 3 get empty information, use “dbcc inputbuffer(spid) with no_infomsgs” to get the last statement sent of Microsoft SQL Server.Here the results are determined by the “event type” logged by Microsoft SQL Server:RPC Event - Show procedure callLanguage Event - Show the first 4000 characters of the SQLThis is why you some times see the “procedure call” and some times see the “SQL Statements.”In other words, it is not up to Spotlight. Microsoft SQL Server logs the information according to the event which triggers the SQL execute task.