0

I am trying to tune a query and would like to test a few pieces during after hours, when there isn't a lot of traffic on the server. I am going to benchmark the individual queries and write the results to a table to check in the morning, i.e.

waitfor time '22:00';

declare @timer datetime
declare @time float
declare @query nvarchar(max)

set @query = N'select ...'
set @timer = getdate()
exec sp_executesql @query
set @time = datediff(ss,@timer,getdate())

The problem is, I won't be able to manually monitor the queries, and I don't want to risk having something run for hours. Is there any way, using T-SQL to set a timeout so that this query cancels after, say 10 minutes?

Other questions on SE suggest that there is now way to do this, or that it can be done for a scheduled job, which I do not have access to create, but I am searching for any alternative or creative solutions.

Sam Cohen-Devries
  • 2,025
  • 2
  • 18
  • 35
  • Possible duplicate of [Is it possible to set a timeout for an SQL query on Microsoft SQL server?](http://stackoverflow.com/questions/3091783/is-it-possible-to-set-a-timeout-for-an-sql-query-on-microsoft-sql-server) – Stavr00 Apr 07 '16 at 15:22
  • Something like this, http://stackoverflow.com/a/7654068/3131696 – M22an Apr 07 '16 at 15:24
  • have a look at @@LOCK_TIMEOUT. if it works for you. https://msdn.microsoft.com/en-us/library/ms182729(v=sql.90).aspx – ClearLogic Apr 07 '16 at 15:26
  • Possible duplicate of [T-SQL: Stop query after certain time](http://stackoverflow.com/questions/7653880/t-sql-stop-query-after-certain-time) – paparazzo Apr 07 '16 at 16:12
  • Pretty sure nothing on the server side to do this. Even in SSMS the timeout is a client side thing. Why not make a dev copy of the database for testing? – paparazzo Apr 07 '16 at 16:21

0 Answers0