May 22, 2013 at 1:16 am
Hi
In a web app (linq to sql),I have a query with some parameters I took the actual query with specific params from sqlprofiler.
The problem is that I have time out when I run this query from App but when I run the query with this format (that I gave from profiler) EXEC sp_executesql N'select statement ',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 datetime,@p5 datetime',
@p0 = 1,
@p1 = 2,
@p2 = 1,
@p3 = 1003,
@p4 = '2012-02-07 00:00:00',
@p5 = '2012-06-10 07:00:00' directly from SSMS ,it takes 0 second to run.
I know that if I run the query in this format with parameter from sp_executesql the plan in both app and sql is the same,so I am sure that both of them are using the same plan(and there is not parameter sniffing problem).
How can I check the reason of time out that convince me the problem is not from sql ?
May 22, 2013 at 7:16 am
Could you post the execution plan, from both application query and query from Query analyzer.
May 22, 2013 at 7:52 am
Here's a pretty good page with tons of gory details about plans and maybe some differences between SSMS and applications.
http://www.sommarskog.se/query-plan-mysteries.html
Bottom line for me is that its just not your query text that makes a plan unique, there are also some "SET" options that are either set or defaulted in with your connection that could also cause a plan to be generated that might not match the one you caused to be saved with SSMS, or vice versa.
Ie., you still really could have two plans and parameter sniffing might still be the issue. Just thought I'd mention the possibility!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply