4

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?

3
5

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.

1

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.

1

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!

4
  • You mentioned users finding workarounds to substitute a temporary table and I just remembered a legacy app where the application was coded to use a normal fixed table as a temporary table. The app would fill the table with hundreds or thousands of records per request and delete them all every 5min or so with a scheduled job causing a lot of blocks when data increased.
    – Ronaldo
    Jun 23 at 10:39
  • @Ronaldo - another bad option would be that they remove the need for the temporary storage completely, but in a way that causes the statements to scan the base tables multiple times (where the bad-but-not-as-bad version was scanning once to produce a filtered set of rows to populate the temporary table). Jun 23 at 10:50
  • good answer, I wonder if 2 SQL instances on the same server, can be replicas in AG ? Jul 9 at 16:34
  • @AlekseyVitsko - I don't see why not. Give it a try... Dev edition is free these days so you can try it with that as an experiment if you are worried about licensing issues. Note that the second instance will need to be correctly licensed in production use even though it is on the same machine as another one. Jul 9 at 17:06

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.