SSMS – Bug – Execution Time-Out

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)