Long Running Query Alert - What Does It Capture

SQL Server performance monitoring and alerting

Moderators: Colin Millerchip, priyasinha, Adam, chriskelly

Long Running Query Alert - What Does It Capture

Postby EdCarden » Thu Mar 27, 2014 7:28 pm

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)
EdCarden
 
Posts: 90
Joined: Tue Nov 25, 2008 6:26 pm

Postby Brian Donahue » Mon Mar 31, 2014 2:20 pm

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 Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Re:

Postby EdCarden » Mon Apr 14, 2014 7:18 pm

Brian Donahue wrote: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
EdCarden
 
Posts: 90
Joined: Tue Nov 25, 2008 6:26 pm

Postby Brian Donahue » Tue Apr 15, 2014 10:31 am

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.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Monitor 4

Who is online

Users browsing this forum: No registered users and 0 guests