May 27, 2024 at 5:45 am
I tried to set the execution timeout in SSMS using multiple methods.
Initially, I tried changing the query execution time through the tools option, as described on the website
https://stackoverflow.com/questions/1137190/changing-the-commandtimeout-in-sql-management-studio
Subsequently, I tried a second method from the link.
In the tools design settings, we can configure values for the execution timeout in seconds.
https://stackoverflow.com/questions/1137190/changing-the-commandtimeout-in-sql-management-studio
However, these methods didn't work even after restarting SSMS.
I have also tried to set the execution timeout under the connection properties in the options tab. Unfortunately, this method also failed.
The below link says that this setting has some issue
I want to know if there is any bug in this Execution timeout Setting .If yes , What are the other ways to set this execution timeout?
I want to stop all the query that I run in SSMS that exceeds the given execution timeout value.
May 27, 2024 at 3:13 pm
My first thought is that something is not right at the source!
😎
Question; have you analysed the offending queries?
May 27, 2024 at 3:33 pm
I think the problem is that you are trying to do something that doesn't make sense. The timeouts are configured on a session by session basis, not on an instance level. So if you configure it in SSMS, it applies to all sessions in that instance of SSMS. It won't apply to any apps that you built or bought.
As far as I am aware, query timeouts can ONLY be configured by the application issuing the query. This is by design and I doubt that anyone would want that changed.
There is no configuration value on SQL Server to stop all the queries that exceed a given execution timeout value and you shouldn't need to. IF the query is running very long AND causing issues (long running queries don't always cause issues), then you should tune the query to make it faster. If that isn't an option, then the application should be configured (if possible) to have a shorter timeout. C# (via .NET) to SQL Server (as an example) has a default timeout of 30 seconds if I remember right, but there is nothing stopping you from adjusting your code to have it be 300 seconds or even wait until complete (ie no timeout).
One reason it doesn't make sense to configure this at the SQL Server level is that if one application expects the query to complete in 1 second or less and the app is handling it so that if it runs longer than 1 second, it stops the query and does error handling while application 2 needs 30 seconds for some things and application 3 takes several minutes (ETL loads for example), how do you configure that "globally"? AND if the global timeout is reached, should that override the application level setting OR should the application level setting override the server setting?
The TL;DR version - the query timeout is configured at the APPLICATION level, not the SERVER level. EACH application can have its own query timeout value. Defaults can be different per application as well. SSMS (for example) I believe defaults to unlimited, whereas .NET apps default to 30 seconds.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 29, 2024 at 7:02 am
Apologies for the confusions caused . I am planning to set the execution timeout for all queries that I will be running in SSMS. My goal is to stop the execution of long-running queries on an instance of SQL Server.
May 30, 2024 at 2:56 pm
Apologies for the confusions caused . I am planning to set the execution timeout for all queries that I will be running in SSMS. My goal is to stop the execution of long-running queries on an instance of SQL Server.
That, as far as I am aware, is not possible. AND even if it was, the application could override the server setting... or would the server setting override the application setting?
Either way, there is no such setting that would be global to SQL Server for execution timeout and I wouldn't want one anyways. SOME queries need to run a long time. PLUS, when that timeout hits, a rollback occurs and those take just as long (or longer) than the original query. So, for example, if you had a 1 minute timeout on a query, IF the query was going to run for 1 minute and 10 seconds, that timeout is hit and now the query is going to run for 2 minutes (or longer).
My advice, rather than "stopping" long running queries (and potentially making them longer), tune them. FIND the long running queries and make them faster.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply