0

I'm debugging a parameter sniffing issue in SQL Server 2014.

My application performs a query that along the lines of: (drastically reduced for simplicity)

SELECT somefields FROM MyTable Where anotherField = @myParameter

Where @myParameter is added through the command.Parameters.Add() method, this query takes too long to run and times out.

When trying to replicate the issue, I went to SSMS and tried the following query:

DECLARE @myParameter int = 10 --same value as in the code
SELECT somefields FROM MyTable Where anotherField = @myParameter

And the query runs fast with a different execution plan from the code.

I want to try some tweaks to the query to see if I can improve my code's execution plan, but I need a ways to make SSRS behave the way my code does.

How can I write my query in SSRS, so that SQL Server calculates the execution plan in the same way it does for my application?

Eduardo Wada
  • 2,606
  • 19
  • 31
  • Have a look at this [`Fast query runs slow in SSRS`](https://stackoverflow.com/questions/2283943/fast-query-runs-slow-in-ssrs) – M.Ali Jul 05 '18 at 11:56
  • Firstly, may you attach plans or describe difference between? Secondly, try to use as workaround in SSRS statement: ` @query = N"SELECT somefields FROM MyTable Where anotherField =" + @myParameter exec sp_executesql @my_query ` – Meow Meow Jul 05 '18 at 12:08
  • Wrap the query in an SP, then however you call it will likely lead to the same execution plan. – Davesoft Jul 05 '18 at 13:52
  • @meowmeow thanks, I was able to replicate the performance issue by using sp_executesql with parameters, with regards to the query itself, I think the problem is that it has a subquery that returns 7 million rows, then gets filtered, when ran from SSMS, sql server optimizes it and moves the filter to the subquery, but that's subject for a different question. – Eduardo Wada Jul 06 '18 at 08:24
  • @EduardoWada thanks for the response. I didn't get it enough from the query in the head of the topic. There it looks simply and ф subquery doesn't exist. So, I wanted to suggest trying to use hints (table hints or smth like this) for optimizing query plan. https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017 – Meow Meow Jul 06 '18 at 09:55
  • @Davesoft hi. In some cases, parametrization doesn't allow use fields as search arguments. therfore, a perfomance issue will arise. A search argument definition https://www.sqlconsulting.com/news1109.htm – Meow Meow Jul 06 '18 at 09:58

0 Answers0