7

I have a production database server running SQL Server 2008 R2. How can I keep a user query on one database from hogging all the system resources and bogging down all the databases on the server?

The specific issue I'm having is with a query with a long running cursor. Whenever the query is run, CPU usage goes to 100% on all cores and even simple queries on other databases slow way down or time out. Is this normal and what are the best practices to prevent this?

5
  • 1
    I'd consider fixing the query first...
    – gbn
    Jun 12, 2012 at 22:12
  • 1
    @gbn - Normally that would be my focus, but in this case the user who wrote\runs the query isn't even on my team so I'm wondering if it's possible to "sandbox" a database from a performance standpoint. Jun 12, 2012 at 22:17
  • Enterprise Edition? Jun 12, 2012 at 22:19
  • @AaronBertrand - Yes. (2008 R2 Enterprise Edition) Jun 12, 2012 at 22:26
  • I guess 2008 is reaching end of life very soon. Wondering why are you still using that? May 6, 2019 at 3:09

1 Answer 1

9

If you're using Enterprise Edition, you can use the Resource Governor (I wrote a whitepaper for Microsoft on this topic a few years ago). This is especially effective if you can identify just this user (by SUSER_SNAME() or HOST_NAME()). Unfortunately this can't be used to place restrictions on just the one query - it is implemented at login time and affects all queries for the life of their session, but you can certainly constrain their CPU in general. Note that in SQL Server 2008 & R2 this constraint is only enforced when there is other contention on the box. In SQL Server 2012 there is a new setting (CAP_CPU_PERCENT) that allows you to constrain CPU for a resource pool even when they're the only one on the box.

Another way (or an additional way) to attack the problem, assuming you have control over the query text itself (e.g. it's not ad hoc being assembled by the user or their app), is to have that specific query always run with OPTION (MAXDOP 1) - it will still cause high CPU, and the query most likely will take longer, but you can use that setting to limit the number of schedulers it affects. So on a 16-core box, you would only see one CPU spiking as a direct result of this specific query.

1
  • 1
    Note that as of 2016 SP2 you can use Trace Flag 2422: Enables the SQL Server Database Engine to abort a request when the maximum time set by Resource Governor REQUEST_MAX_CPU_TIME_SEC configuration is exceeded. Note: This trace flag applies to SQL Server 2016 (13.x) SP2, SQL Server 2017 (14.x) CU3, and higher builds. learn.microsoft.com/en-us/sql/t-sql/statements/… Dec 4, 2020 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.