Mystery speed difference between same query (literally copy/pasted) between ODBC client (faster) and native SSMS

Question

I have a complex query, that was taking about 30 seconds to run, then returning a small number (20) of rows. It's a vendor product. The query underlies a weekly report, and is quite complex, lots of joins, subqueries, etc. to churn out the 20 rows of results.

Summary: Previously, the report ran in about 30 seconds in the ODBC query tool I use most often, AND in SSMS, AND in SSRS, where the report happens to reside. Suddenly the SSRS report got extremely slow last week, which led to some troubleshooting, updating statistics, and the following unusual scenario: Troubleshooting on my machine, one and only machine, the query remains at 30 seconds in my ODBC client, but takes 3 minutes running from SSMS (and/or the SSRS report using almost the same query (SSRS forces using parameters, whereas in both my ODBC client and SSMS, I've hard-coded the where clause.)

Thinking parameter sniffing, I think I've disproven it, because I have 100 percent copied the exact SQL back and forth between the ODBC client and MSSQL - Results are consistent, 30 seconds in ODBC client, 3 minutes in MSSQL. I even updated statistics again, and ran the SSMS query first.

Whether coincidence or not, the March Windows patches were applied to all servers at work between a week ago and this week. Sadly, the reports only run weekly, so I can't say with certainty the updates happened the day/moment after the updates. It's SQL Server 2012, Windows Server 2016, and it was the March 2021 update patch (basically, with whichever specific patches the admins decide not to apply.)

Anyway, as I mentioned above, I've literally copy/pasted the identical query between my ODBC client and SSMS back and forth, and repeatedly, the ODBC version is 30 seconds, the SSMS version 3 minutes.

And finally, I've also cross tested a simple/straight "Select * from Table" of a table with 10,000 rows, and the results are the same speed between SSMS and the ODBC client. (This makes it even more confusing.)

Any suggestions?

Answer 1

I discuss possible reasons for this situation in my article Slow in the Application, Fast in SSMS?.

If I understand this correctly, there is a performance difference for something as simple as "SELECT * FROM tbl". That sounds as if the difference is that MARS (Multiple Active Result Sets) is enabled in one case and not the other.

Answer 2

The following link may be useful to you:

SQL Server stored procedure runs fast in SSMS and slow in application

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

To all, Erland/Echo/Jeffrey, thanks for your time;

I haven't uncovered the specific smoking gun yet. The SQL is absolutely identical between my SSMS and my ODBC query tool through copy/paste, although I haven't yet experimented with eliminating all CR/LFs to guarantee them being absolutely identical, per one thought in Erland's article. (Also a reminder, my situation seems the opposite of most, I've rarely seen articles about SLOWER in SSMS) although that's probably by chance.

It's also Me as the user in both cases (with respect to the queries running from my desktop query tools), although the SSRS report using the same query runs as the SSRS service account. And finally, the previous version of SSRS report itself originally ran in 30 seconds, then (untouched) went to ten minutes (which led to me doing an update statistics step), which changed the speed to the same roughly 3 to 4 minutes that I also saw in SSMS. But not my ODBC query tool, still doing it in 30 seconds.

And finally, a day or two ago, I updated the SQL for a totally different reason and republished the SSRS report (also via copy/paste from SSMS.) Ironically, the updated SQL, still taking the three/four minutes in my SSMS, ran in 30 seconds when republished to SSRS. Two days later, it's still speedy, but since I didn't specifically solve any issues, I'm more or less expecting it to slow down due to the still unknown reason sooner or later. Tomorrow (Friday) will be revealing, since it's generally Fridays that the customer runs the report with varying search parameters. (FYI, the one parameter provided by the user is used in a a messy "LIKE" clause on a text string returned via a table valued function lookup. (It's a vendor view, that in addition to table valued functions, utilizes recursive CTEs in another TVF, and views call other views. And no, I didn't design it!)

I didn't spend much time on the Arithabort topic since Erland's article said that even if it results in a difference, it might be just due to a forced recompile. (Also, a reminder, the SSRS report, unchanged, was fast, then slowed down, Arithabort was unchanged for that sequence of events.)

It almost has to be parameter sniffing, probably caused by the lack of proper indexing somewhere. With the XML plan being 10,000 lines long, I'm sure the answer is in there somewhere, but the information overload makes finding it hard. I'll post if/when I find it.

Answer 4

Thanks again, and sorry if I left it out of earlier description, but there definitely are different execution plans. In my case, the plans (both of them) being about 10,000 lines long and 100 miles (perhaps I exaggerate) of scrolling down and to the right, they were beyond my ability to pinpoint exactly where the differences lied. Early on, there were just two plans. As trying different things grew, more versions of the plan up, mostly new ones written in SSMS, since I wanted to keep the "fast" 30 second one from my other query tool intact for comparison purposes.

I'll try the sys.dm_exec_plan, that's something I haven't tried yet.

And the parameters for my testing purpose, I have kept the same.