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.
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?
Reference: Pinal Dave (http://blog.SQLAuthority.com)