Raw query text

Posts   
 
    
Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 25-Jan-2020 20:00:20   

Hi,

can't seem to find in the UI and if it doesn't exist would it be possible to add a way to copy the raw query, i.e. the exact statement that was submitted to the SQL Server via the

sp_executesql

Thank you!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38454
Joined: 17-Aug-2003
# Posted on: 27-Jan-2020 10:01:45   

that's part of SqlCommand itself, and not available from the outside. Why do you need that exact statement, if I may ask?

Frans Bouma | Lead developer LLBLGen Pro
Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 27-Jan-2020 11:42:49   

Otis wrote:

that's part of SqlCommand itself, and not available from the outside. Why do you need that exact statement, if I may ask?

I'm dealing with parameter sniffing problem if my understanding is correct of what I've read so far. I do have the apparently classic "slow in app fast in SSMS" symptom. I did change the settings of SSMS to reflect the app's SET OPTIONS, basically switch off the ARITHABORT, however, the app uses the sp_executesql command whereas copy-pasted (from ORMProfiler) query has the parameters as constants which leads to new execution plan generation, thus I can't really replicate the problem in SSMS as I do need to have the exact query that app submitted. Was it so that it was possible to extract the generated query via tracing? Curious how would that work with LinqPad...

Thank you!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38454
Joined: 17-Aug-2003
# Posted on: 27-Jan-2020 11:57:00   

Ah simple_smile You could try SSMS' profiler. It will show you the exact query, with parameter declarations and sp_executesql. (Tools -> Sql profiler). You then have to fine tune it (using the filters) a bit so it'll filter out all nonsense except queries from your app.

You could copy/paste these..

Though if a query is slow overall, please check in orm profiler if the '.net time' is huge. That would mean materialization takes a long time.

Also, take into account that ssms keeps the connection open. So executing a query 2 times in the same tab means the result of the previous execution is still available in the cache of that connection and the results will likely be fast.

Frans Bouma | Lead developer LLBLGen Pro
Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 27-Jan-2020 12:06:25   

Otis wrote:

Ah simple_smile You could try SSMS' profiler. It will show you the exact query, with parameter declarations and sp_executesql. (Tools -> Sql profiler). You then have to fine tune it (using the filters) a bit so it'll filter out all nonsense except queries from your app.

You could copy/paste these..

Though if a query is slow overall, please check in orm profiler if the '.net time' is huge. That would mean materialization takes a long time.

Also, take into account that ssms keeps the connection open. So executing a query 2 times in the same tab means the result of the previous execution is still available in the cache of that connection and the results will likely be fast.

Wasn't MS planning to remove the SQL Profiler at some point? simple_smile I thought of it, but it does put some performance penalty on db and my target db is Azure SQL simple_smile I do have a plan B to try to query for all execution plans and try to find that specific query, but being able to fetch the query from LinqPad + tracing of LLBLGen just feels more natural and convenient for this kind of troubleshooting simple_smile Also I think it might be beneficial to be ableto easily track how even new queries are performing while working on them in conjunction with, say, Plan Explorer.

Thank you!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38454
Joined: 17-Aug-2003
# Posted on: 27-Jan-2020 13:54:44   

azure sql... hmm. there are a whole lot of possibilities that could make a query slow there: the easiest is that the disk op/sec number of your db / resource has been saturated, so it'll bottleneck there. Not easy to track down, but the last thing I'd look at is parameter sniffing issues in that case, other factors are much more likely to be slow than that.

Frans Bouma | Lead developer LLBLGen Pro
Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 27-Jan-2020 13:59:02   

Otis wrote:

azure sql... hmm. there are a whole lot of possibilities that could make a query slow there: the easiest is that the disk op/sec number of your db / resource has been saturated, so it'll bottleneck there. Not easy to track down, but the last thing I'd look at is parameter sniffing issues in that case, other factors are much more likely to be slow than that.

query copy-pasted from ORMProfiler works almost instantly against the very same db, but because it's using different query plan it doesn't help me with how app is querying it simple_smile Thus I'd like to be able to extract the generated query so I could use it directly and iterate, it's much faster vs querying for execution plans and finding it for the query in question...

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38454
Joined: 17-Aug-2003
# Posted on: 27-Jan-2020 16:14:40   

then the only way to do that is to run the query on sql azure from your dev computer and collect it with smss' profiler. It logs the exact sp_executesql statement.

Frans Bouma | Lead developer LLBLGen Pro
Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 27-Jan-2020 16:29:27   

Otis wrote:

then the only way to do that is to run the query on sql azure from your dev computer and collect it with smss' profiler. It logs the exact sp_executesql statement.

Just to confirm if I understood correctly: enabling tracing for LLBLGen won't help with getting the original query text.

Thank you!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38454
Joined: 17-Aug-2003
# Posted on: 28-Jan-2020 09:24:00   

Findev wrote:

Otis wrote:

then the only way to do that is to run the query on sql azure from your dev computer and collect it with smss' profiler. It logs the exact sp_executesql statement.

Just to confirm if I understood correctly: enabling tracing for LLBLGen won't help with getting the original query text.

Thank you!

tracing will show you the query text as it is set in DbCommand.CommandText. The sp_executesql string is created inside SqlCommand, and we can't obtain that. To intercept/see this particular string you need to use the SMSS profiler as that profiler sees the strings that arrive at the server end.

Frans Bouma | Lead developer LLBLGen Pro
Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 28-Jan-2020 09:34:09   

Otis wrote:

Findev wrote:

Otis wrote:

then the only way to do that is to run the query on sql azure from your dev computer and collect it with smss' profiler. It logs the exact sp_executesql statement.

Just to confirm if I understood correctly: enabling tracing for LLBLGen won't help with getting the original query text.

Thank you!

tracing will show you the query text as it is set in DbCommand.CommandText. The sp_executesql string is created inside SqlCommand, and we can't obtain that. To intercept/see this particular string you need to use the SMSS profiler as that profiler sees the strings that arrive at the server end.

Aight! Thank you! simple_smile