When you use ODBC or SqlClient to access data from SQL Server, by default the query will be cancelled if there is no response from the server within a certain period of time (30 seconds by default). ODBC or SqlClient will start a timer after sending the query to SQL Server and if there are no results from the server within 30 seconds, the query will be cancelled and a timeout error message will be sent to the application. In case a network packet (containing query results) was received within the 30 seconds, and if we are expecting additional network packets (additional rows that couldn't fit in the first network packet), then the timer is restarted, if the next network packet is not received within this 30 second period, the query will be cancelled.
The common reasons for SQL Command timeout are non-optimal schema or inefficient queries that execute for a long period of time or that miss indexes or lock wait issues. It is important to tune the queries rather than increase the SQL Command timeout settings.
In the video an example is demonstrated where decreasing the network packet size eliminates the timeout, this demonstration is to understand how network packets are involved in resetting the timer and to develop better understanding of timeout. Changing the network packet size is not a solution to avoid timeout, tuning non-optimal schema or inefficient queries or adding indexes or avoiding lock waits are the proper solution.
To identify the command that leads to SQL Command timeout error, you can use Extended Events and monitor event 'sqlserver.attention'. The extended event script used in the video is available at www.sqlvideo.com/xevents.
In SQL Server Management Studio, the SQL Command timeout can be changed using 'Options' in the connection dialog. By default, this is set to '0', which means no timeout. When a query or stored procedure is executing, if you click the 'Cancel' button (the red square), the same attention will be sent to the SQL Server (like SQL Command Timeout). And this will in-turn generate 'sqlserver.attention' event in Extended Events.
In the below video you can see an example of the SQL Command timeout. SQLTest Tool simulates the hands-on labs for you, no registration necessary. You can practice the online hands-on example while watching the Video.