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!

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

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!

Ah 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.

Otis wrote:

Ah 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? I thought of it, but it does put some performance penalty on db and my target db is Azure SQL 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 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!

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.

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 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...

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.

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!

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.

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!