How can inefficient SQL queries be prevented from slowing a database server

Question

I have an ASP .NET 4.5 application. On a maintenance page is a text box which allows administrative users to write SQL which is executed directly against the SQL Server 2008 database.

Occasionally one of the administrative users writes some inefficient SQL and the SQL Server process starts using up all the memory and CPU cycles on the server. We then have to start and stop the service to get the server responsive again.

Is there any way that I can stop these from queries consuming all the resources? These queries will not return fast enough for the user to see them so it's okay to cancel the query.

Edit I realise it would be better to prevent users from writing SQL queries, but unfortunately I cannot remove this functionality from the application. The admin users don't want to be educated.

Answer 1

You can set the query governor at the server level but not sure about a per user or per connection / application limit.

http://technet.microsoft.com/en-us/magazine/dd421653.aspx

That said, it is likely a poor / dangerous practice to allow users to directly enter SQL queries.

Answer 2

First off I would make sure these queries are not locking any tables (either use NOLOCK or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for each query ran).

As far as resources go, I would look at something like the Resource Governor. We use this for any adhoc reports on our production OLTP system.

Pinal Dave has a great blog entry on it. You can also look at Technet or other MS sites for information on what it is and how to set it up.

http://blog.sqlauthority.com/2012/06/04/sql-server-simple-example-to-configure-resource-governor-introduction-to-resource-governor/

Answer 3

1. Create a user which will have no more and no less privileges than necessary.

2. Create some login data to the user, you will not share this with the admins who will write the queries.

3. Create a panel/page/application where you let them write their queries. Here you can add additional constraints unavailable at RDBMS system-level.

You let your users access this panel/page/application and they will run their queries through this. If they are doing anything undesired uncovered by you, then you just issue your modifications for the privilege and the panel/page/application.