How can we restrict tempdb usage for specific users or queries on SQL Server?

Question

In our SQL Server production environment some users run ad hoc queries that run huge data extracts into tempdb and fills it up, causing issues on a production server. And as these reports/queries run from the front end, they sometimes mess up with the date range which pulls millions of rows to temp tables. Yes, the users need to be educated and also tuning is required, which we have done, but still occasionally they are creating problems.

And now they say that in SAP ASE (Sybase) there is a feature to create multiple tempdb's and redirect users and restrict usage, by not bloating up the single tempdb (as like in SQL Server) and bring the server down.

Do we have anything in SQL Server like this?

Answer 1

As you mentioned, users need to be educated, and queries need a rewrite to be more efficient to use less of tempdb.

SQL Server does not have a concept of multiple tempdb, nor can you restrict a user by how much space they can use from tempdb.

One solution will work, and it is not the best. Monitor tempdb usage by each session and make a decision based on the amount. You can decide to terminate a session, but you need to consider the business impact? Do you suggest they rerun at a later time when the server has less activity? How long can a business wait to rerun before it impacts the bottom line of the business?

A few suggestions in Possibility to implement time to execute / TempDB usage restrictions on some role groups in SQL Server? this Q&A if you decide to chose this route.

Answer 2

It isn't possible to restrict TempDB use in the way you want, other than time-to-execute limits and simply blocking it completely, as already suggested.

If it is possible to separate the sort of queries that might produce this pattern, either because you control the application and can give it a little smarts in this regard or people are using other tools to log more directly into the database, then you could run potentially nasty reports against a replica/mirror/other instead of the main database. This would at least stop the problematical queries impacting the main application.

There is nothing to stop this reporting instance being on the same server(s), but it will want to be in its own SQL Server instance at least so it doesn't share TempDB with the main application DBs. The "extra" TempDB can have its own growth limits and perhaps be on different drives (or a different IOPs pool if using a SAN arrangement for storage) and you can artificially limit the CPU and memory use of the new instance too (to give the main app(s) priority) if they share resources. The main gotcha with this idea, aside from the extra admin, is it will have licensing implications unless your data is small enough to fit in express edition's limits (which I think unlikely if it is big enough for this issue to regularly be a problem for you) so might not be a practical option for that reason.

You might still want to impose other limits even on this reporting instance, to stop really nasty queries blocking the simply just-a-bit-nasty ones or pulling the copy down so hard that effort is required to bring it back into sync later.

As an aside on the other suggestions: When blocking TempDB access to stop temporary tables being created, be careful of "clever" users writing ad-hoc SQL who will potentially do worse things than they are right now to work around that limit!

Answer 3

Outside of what was already said, the only other option you can do is remove access to the TempDB database (in your Production server) to certain users until they are better educated. This will prevent them from executing any queries that directly involve TempDB usage, such as queries that use temp tables. You can achieve this a few different ways, one being by explicitly using the DENY Database Permissions command. Using the ALL parameter may be helpful here too.

As SqlWorldWide mentioned, you should consider the business impact of doing this as well.