question

JohnQFlorida avatar image
0 Votes"
JohnQFlorida asked ErlandSommarskog commented

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

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?

sql-server-generalsql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Forgot to mention, I've also tried Option(recompile), optimize for unknown, and running with/without populating a variable for the where criteria.

0 Votes 0 ·

Hi @JohnQFlorida ,
Your question involves part of the general content, I helped you add the general tag,so that people there can also provide advice.

Echo

0 Votes 0 ·

Do you have any update?

Echo

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the reply, and the part about the "Select " was a typo, now fixed. A straightforward "select " from tables of any size yields similar speeds, or at least, expected speeds, where normally SSMS pulls data back a bit faster than ODBC.

Regarding fast in ODBC, slow in SSMS, I had always thought the differences wouldn't occur with absolutely identical SQL copy/pasted between the two, created within minutes of one another, both with the same query criteria. I'll re-read your article, looking for clues. Thanks again!

Regarding my typo, I haven't been to the forums since it was the MSDN forum, and I only now saw the little gear to fix the typos.

0 Votes 0 ·

It could be MARS - or it could be ARITHABORT setting (or other connection settings) that are causing different execution plans to be generated. Other reasons could also be related to whether or not you schema-qualify objects and how you setup the individual users. The query could be absolutely the same but generate different execution plans because of these differences.

0 Votes 0 ·

OK, so if Select * from Table runs with the same speed, we can probably rule out MARS. It does sound like a query-plan issue, and I hope that my article will you ideas for further troubleshooting.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JohnQFlorida avatar image
0 Votes"
JohnQFlorida answered EchoLiu-msft commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This all appears to be related to different execution plans being generated - based on the client executing the code. That leads me to believe the differences are based on connection settings being different between each client. Unless the parameters being used are different - based on where they are executed. You stated the code is exactly the same code - are the parameters the same?

If the parameters are different - then most likely the cause is a bad execution plan for the specified parameters. The plan gets generated for a set of parameters - and you get good performance until different parameters are passed. If that is the case, then using a stored procedure with option recompile might be the best option - or using a stored procedure and defining OPTIMIZE FOR.

0 Votes 0 ·

One important thing about the execution plans: are they different for the different scenarios?

And how many entries of this beast can you find in the plan cache? If your query strings are different, this is where this will be revealed.

And sys.dm_exec_plan_attributes will reveal entries that differs on cache keys.

0 Votes 0 ·

Thank you for your detailed reply. We will always follow the progress of the post.

0 Votes 0 ·
JohnQFlorida avatar image
0 Votes"
JohnQFlorida answered ErlandSommarskog commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The fact that execution plans are different is likely to explain why there is a performance difference.

As for why there are different execution plans when testing parameters are the same, it's another story. But without access to the plans or the environment is difficult to say more than what I have said here and in the article.

If you want to share the plans, you can use http://www.brentozar.com/pastetheplan.

0 Votes 0 ·