7

we support a large number of developers and analysts who either do not know much or don't care at all about query performance. We have every day hundreds of poorly conceived queries (also due to poorly designed db architecture) that, each, suck away dozens of gigabytes of RAM from our servers and have execution plans in the range of megabytes stored in SQL cache.

We would like to restrict certain users from using SORTs and HASH MATCHes, specifically ORDER BY, SELECT DISTINCT and JOINs of any kind. Our intention is to let users pull their data, but work on it only on their side.

Could this be achieved in SQL Server? Is there any DB engine that supports that? What would be your thoughts about this?

I know this would beat the principle of a relational database, but we're looking for some way to enforce that the engine is properly used and not abused by uneducated users.

We are already considering the implementation of Resource Governor to allocate a certain amount of machine resources for these bad queries.

6

The answer by kevinsky is one path towards a solution for you.

A different path for your specific issue is to create a second reporting (and maybe a third dev) server(s). With copies of the production data. Your reporting people and development people can do all kinds of stupid, and not impact production.

There are multiple ways of achieving this, depending on how fresh your report/dev data needs to be.

A common solution is a nightly backup and restore to the secondary server. If you need really current data (like no lag or one second) look into SQL Server Always On

  • yes, if an extra server and database license is not an issue then this is the best solution – kevinsky Apr 17 at 14:28
  • 1
    @Kevinsky just use developer edition - no license needed for non-production usage. Unless of course it is production usage. – George.Palacios Apr 17 at 14:40
2

You can create views of the data they need and create a role that only allows select.

Or you can create stored procedures that return a result set that can be any combination of tables accessed with a query that performs well.

I do not know of any way to restrict the use of basic commands in SQL short of some hacky database level trigger and suggest that if you have that many users who don't know how to write a good query then some education is a more effective way to do this. Lunch time sessions?

  • 1
    Having a DBA stop by their desk/cube in the middle of a long running SQL could help to focus the Lunch & Learn session. A quick discussion could help that person learn something (eg "you know, you really should add the partition keys to your WHERE clause"). It could also help the DBA to learn something too (eg that table really does need that index) – Michael Kutz Apr 17 at 15:09

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.