none
After I upgrade from Managment Studio 18.0 to 18.1, I have an error in my select statements RRS feed

  • Question

  • I am trying to call a table function and it is a query that executes several Selects with union, and it take 0 seconds. I run the function from Managment Studio 17.9.1 and it works perfect.

    The error I have is : "The query has been canceled because the estimated cost of this query (28) exceeds the configured threshold of 3. Contact the system administrator.

    Managment Studio 18 and 18.1 comes with a lot of bugs!!!

    First with Managment Studio 18.1 was the problem of the Database Diagrams node missing, later when changing tabs the problem of accelerate hardware don't permit refresh the windows,  now queries don't run !!!


    Tuesday, June 25, 2019 7:10 PM

Answers

All replies

  • Check your QUERY_GOVERNOR_COST_LIMIT setting under Query-->Query Options-->Advanced. Make sure the value is set to zero.

    Database diagrams should be back in the SSMS 18.1 version. Do you not see the node?


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Tuesday, June 25, 2019 7:36 PM
    Moderator
  • To clarify Dan's post: the message you get has nothing to do with SSMS, but this is a limitation enforced by the DBA through Resource Governor, a component in SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, June 25, 2019 9:48 PM
    Moderator
  • Hi Carlos Cerda,

    The value of QUERY_GOVERNOR_COST_LIMIT is a numeric or integer value specifying the longest time in which a query can run. Specifying 0 (the default) for this option turns off the query governor, and all queries are allowed to run indefinitely.

    Best regards,
    Cathy ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, June 26, 2019 7:41 AM
  • Hi Cathy ji, I check the use query governor to prevent long-running queries and set to  0 (zero) value, but when I run my sql statement in a storeprocedure I get the same error:

    The query has been canceled because the estimated cost of this query (18) exceeds the configured threshold of 3. Contact the system administrator.

    Just if I do this in SSMS it works:

    SET QUERY_GOVERNOR_COST_LIMIT 0;
    exec MyStoreprocedure '994267'

    but I don't want to do this everytime I connect to the server!

    Wednesday, August 7, 2019 5:20 PM
  • Now in 18.1 diagrams are back!
    • Marked as answer by Carlos Cerda Wednesday, August 7, 2019 5:27 PM
    • Unmarked as answer by Carlos Cerda Wednesday, August 7, 2019 5:27 PM
    Wednesday, August 7, 2019 5:22 PM
  • Sure I see it now!
    • Marked as answer by Carlos Cerda Wednesday, August 7, 2019 5:27 PM
    Wednesday, August 7, 2019 5:27 PM
  • Dan Guzman, is it possible to set it (zero value) as default ? because when I open again the SSMS the value is set to 3 !


    Wednesday, August 7, 2019 5:29 PM
  • is it possible to set it (zero value) as default ? because when I open again the SSMS the value is set to 3 !


    Check the setting under Tools-->Options-->Query Execution-->SQL Server-->Advanced. This is the default value used for new query windows but can be overridden for specific windows via the Query menu as I described earlier.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, August 8, 2019 2:16 AM
    Moderator