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)
4 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
Hi Penal
As per my experience, it is nether a bug, nor a broken connection.
It does not have anything with you execute a query, but when you create Index or rebuilt an index, then it gets timed out based on this value you set.
For example, if you SET this value to 30, then your index create will get timed out after 30 second if your table is large enough.
Thanks.