I have a set of queries against views that run just fine in SSMS. They completed in SSMS in, at most, 3 minutes and not all that much longer in Excel (via Power Query). I recently added some more PARTITION BYs to one of the views on which they depend and now, perhaps coincidentally, they're now not much slower in SSMS but they now take so long to query in Excel that it times out.

The query is far too big to post here. My question is only this: How do I begin to diagnose such issues and find the cause of this huge disparity?

CodePudding user response：

This behaviour - "fast in SSMS, slow in application" can be caused by a feature of SQL Server called Parameter sniffing.

SQL Server uses a process called parameter sniffing when it executes stored procedures that have parameters. When the procedure is compiled or recompiled, the value passed into the parameter is evaluated and used to create an execution plan. That value is then stored with the execution plan in the plan cache. On subsequent executions, that same value – and same plan – is used.

This process works for any query, not just stored procedures. Also, the engine "sniffs" not just values of parameters, but cardinality estimates (how many rows the tables have). This means that my suggestion below may help even if your query does not have parameters.

The article Slow in the Application, Fast in SSMS by Erland Sommarskog explains this and other related areas in much more details. I would highly recommend reading it regardless of whether my suggestion below helps you or not.

I hope you use SQL Server 2008 or later. To start with, simply try to add OPTION(RECOMPILE) to your query, like this:

SELECT * 
FROM [MY_FINAL_VIEW] 
OPTION(RECOMPILE)
;