Long Running Query Alert - What Does It Capture

EdCardenEdCarden Posts: 114 Bronze 3
edited April 15, 2014 5:31AM in SQL Monitor Previous Versions
WHy does the LRQ (Long Running Query) alert sometimes capture the actual T-SQL code as shown in #1 below and at other times captures the sturcture and not the values as shown in #2 below? The second one does not show you the actual values being inserted.

#1:
INSERT INTO [dbo].[MYTABLE]( @col1, @col2, @colN)
VALUES (1, 'a', 'WhatThe')

#2:
(@1 int,@2 varchar(1),@3 varchar(256) ) INSERT INTO [MYTABLE]( @col1, @col2, @colN)
VALUES (@1, @2, @3)

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    I have noticed that sometimes running queries results in a parameterized query being submitted behind the scenes. You can see the same behavior if you run a MS SQL Profiler session against the database. Particularly, I think ODBC connections (as opposed to connections that use the native MSSQL driver) transform the simple version of the query into one with parameters.

    Hope this helps.
  • EdCardenEdCarden Posts: 114 Bronze 3
    I have noticed that sometimes running queries results in a parameterized query being submitted behind the scenes. You can see the same behavior if you run a MS SQL Profiler session against the database. Particularly, I think ODBC connections (as opposed to connections that use the native MSSQL driver) transform the simple version of the query into one with parameters.

    Hope this helps.

    Brian,

    If the client were using ado.net insetad of ODBC would help? Do you know if there is anything that can be done at the SQL Server end to change this or is this just one of those things you're stuck with?


    Thanks
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    It's the way that the client works. I do not know enough about the internals of SQL native client and/or ADO to say how to avoid it.
Sign In or Register to comment.