Menu
I am having some issues when running sp_executesql on a database table. I am using an ORM (NHibernate) that generates a SQL query that queries one table in this case. This table has about 7 million records in it and is highly indexed.
When I run the query that the ORM spits out without the sp_executesql, it runs very quickly and profiler shows it has 85 reads. When I run the same query using sp_executesql, it has around 201,828 reads.
Is there something I need to do on my SQL Server to improve the performance of running the query without sp_exectuesql? It seems as though its not using my Indexes.
What is the best way to fix this issue? If possible, I would rather not change the way the ORM is generating the SQL but instead fix the problem at the SQL Server/database level because it seems that is where the problem is. I'm guessing I need to do more optimization on the database to fix this problem I just don't know what.
exec sp_executesql N'SELECT top 20
this_.Id as Id0_0_,
this_.Application as Applicat2_0_0_,
this_.[Context] as column3_0_0_,
this_.Logger as Logger0_0_,
this_.Message as Message0_0_,
this_.Exception as Exception0_0_,
this_.Thread as Thread0_0_,
this_.[Level] as column8_0_0_,
this_.LogDate as LogDate0_0_,
this_.SessionId as SessionId0_0_
FROM LogMessages this_
WHERE this_.[Context] = @p0',
N'@p0 nvarchar(2)',
@p0 = N'55'
Context is a varchar(255). This field is very free form. It isn't always an integer and the length may very. In this case, I am querying for a value of '55' but it could just as easily be querying for 'Foooooobaaaarrr'
827
sp_executesql is slow with parameters.
WAITING: Queries can be slow because they're waiting on a bottleneck for a long time. See a detailed list of bottlenecks in types of Waits. RUNNING: Queries can be slow because they're running (executing) for a long time. In other words, these queries are actively using CPU resources.
Your application itself changed, and it's not able to digest the results as quickly. Someone patched, and it had an unexpected side effect. You have the same plan, but different memory grants. Someone's modifying more rows at a time, so you're hitting lock escalation.
Queries can become slow for various reasons ranging from improper index usage to bugs in the storage engine itself. However, in most cases, queries become slow because developers or MySQL database administrators neglect to monitor them and keep an eye on their performance.
what is the data type of .[Context] use the same datatype
right now you are using nvarchar(2) but that seems odd for something like 55, if you are not using the same data types you will get conversions which then cause scans
based on your updated question, it looks like it is varchar(255), then do this
WHERE this_.[Context] = @p0',N'@p0 varchar(255)',@p0='55'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
