We are doing some regression testing in SQLA17. I am executing the same query in both versions, in SQLA12 the execution time is 7 seconds but in SQLA17 is 13 seconds (almost the double of the time). I already ran the sa_flush_cache procedure and updated the statistics of the tables involved in the query. The database in SQLA17 is the same database that was upgraded from version 12 to 17 and both are running in the same server.

It is assumed that version 17 has enhancements, should not degrade response time.

Any idea why this? What else could I do to improve the time in 17?


asked 18 Oct '18, 09:55

lferreira's gravatar image

accept rate: 0%

Can you please upload the execution plan?

(18 Oct '18, 14:53) Vlad
Replies hidden

How can I upload it? It's so big to put in the comments.

(18 Oct '18, 15:50) lferreira

In my humble experience, some queries run (sometimes considerably) slwoer with v16/v17 compared to v12 when intra-query parallelism comes into play. On those cases, I have usually tested with disabling that (i.e. via setting option max_query_tasks to 1).

For example, see that question...

To disable intra-query parallelism for your test query, you can easily do that via

SET TEMPORARY OPTION max_query_tasks = 1;
(19 Oct '18, 03:02) Volker Barth
Replies hidden

thanks for the comment, it's good to know but in my case the query is not using intra-query parallelism.

(19 Oct '18, 14:36) lferreira
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 18 Oct '18, 09:55

question was seen: 135 times

last updated: 19 Oct '18, 14:36