Yesterday I shared a real story of my consulting engagement Comprehensive Database Performance Health Check, today, we will continue the same blog post on a different topic but from the same SSMS options screen. While the new SSMS has fixed many bugs (read here, here) I believe there is one configuration setting about execution Time-out either does not seem to work or I do not know how to use it.
Execution Time-Out
First, go to SQL Server Management Studio (SSMS) and open Options from the Tools menu bar. Now over here you will see the option of Execution time-out. What I remember in the past whenever I set this option to any value, it will stop any query in SSMS after that many seconds.
For example, if I am running a query which takes over 30 seconds and I set this setting as 5 seconds. My all queries in SSMS will timeout after 5 seconds. This setting is there to avoid any such issues where users have run a query that runs a longer period of time or just stays blocked for a longer period of time.
During the recent consulting engagement, my client and I tried to change the value in this setting and see its impact on the query but unfortunately, we noticed no impact of these settings on the query. As it was a production system, we did not try much.
Later on, I tried the same experience on my own machine and also on my friend’s machine which was not a production system. I tried to restart SSMS, SQL Server and the whole machine itself and could not see any impact at all. I believe that this setting used to work in the earlier version but as I have installed SSMS 18.5, I have no machine available where I can go back and try this one out.
So here are my questions to you –
- Do you see the same behavior on your system?
- What is your SSMS version?
As it has been a long time since (here, here) I have used this feature and if there is a change in the behavior of the feature, can anyone further guide me, please?
Reference: Pinal Dave (http://blog.SQLAuthority.com)
3 Comments. Leave new
Great tips! Thank you for sharing!
Same problem in v17.9.1
did someone report this bug to MS?
It is terrible: all my queries stopped after 9 seconds. I also tried this to change in settings, but it has no effect. Then I found this article here. OK, I am not so stupid, but it is a bug :-)
Then I followed the link to your article: https://blog.sqlauthority.com/2016/01/26/sql-server-timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation-or-the-server-is-not-responding/
It looks like this works, but now I have to setup this every time, otherwise I get the 9 second timeout. And I have no idea how this timeout was set. I never change this and it was always 0