1

For SQL 2008 R2, data views.

Looking for a timeout control using Microsoft SQL Server Management Studio (SSMS) that is NOT at the Server Level, and/or is NOT dependent on query timeout as set by application initiating the query.

I have been unable to find timeout controls by View, User or Role using SSMS.

There are server level timeouts (remote query timeout http://technet.microsoft.com/en-us/library/ms189040.aspx ), but as I understand it would also impact the main applications use of the database, which lives on a different server (main application gets to define its own limits).

I found DBPROP_COMMANDTIMEOUT http://msdn.microsoft.com/en-us/library/windows/desktop/ms712980(v=vs.85).aspx but not seeing any way to control it by View. And this https://serverfault.com/questions/242300/set-command-timeout-from-sql-server-2005-rather-than-through-the-code says "Command timeouts are always set by the client"

Considerations: These are connections for reporting from the production database of a major application, where the archived datasets (midnight last night) are not sufficiently current. We have a requirement to allow some access; we have a responsibility to not let that access adversely impact the application.

Improve this question
  • 1
    You answered your own question, timeouts are determined by the client application. Sql server doesn't support ending client queries based on duration. – StrayCatDBA Apr 24 '13 at 11:33
  • @StrayCatDBA - (thank you) I was beginning to suspect that a coding solution was not going to be available. I will let the question ride for a couple days. If nothing else comes up will post my alternative solution; SLA/OLA with trace for validation. – James Jenkins Apr 24 '13 at 11:39
  • In the future, please avoid cross-posting. Thanks. – Jon Seigel Apr 24 '13 at 13:33
  • @Jon - Got it. It is only posted here now. – James Jenkins Apr 24 '13 at 13:51
  • 1
    If you are using enterprise edition, then resource governor can limit users based off ofthe expected cost of queries. This can prevent users from starting queries that have a high cost. It's not prefect, but is better than nothing. – StrayCatDBA Apr 25 '13 at 2:08
3

Given the constraints and in addition to query optimizations and tuning.

Three stage approach, to limit potential conflicts when reporting from production server –

  1. Use Resource Governor to limit report logins to Max 20% Min 0
  2. Use an Operating Level Agreement (OLA) to require applications reporting from production databases to a 5 second timeout (DBPROP_COMMANDTIMEOUT)
  3. Use a trace to track SQL queries against production database and confirm OLA compliance (filter by login)

Rationale:

  1. Resource Governor, does not have any contra-indications when limiting occasional report queries against OLTP with constant connect
  2. OLA is not an iron clad guaranty, but if the timeout is used, it limits the potential exposure to the main application (OLTP)
  3. Trace permits follow-up and validation of solution.

References

Managing SQL Server Workloads with Resource Governor
http://msdn.microsoft.com/en-us/library/bb933866(v=sql.105).aspx

White paper “Using the Resource Governor” from the Microsoft Download Center http://msdn.microsoft.com/en-us/library/ee151608%28v=sql.100%29.aspx

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.