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.