Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
I'd consider fixing the query first... –  gbn Jun 12 '12 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. –  Joseph Sturtevant Jun 12 '12 at 22:17
    
Enterprise Edition? –  Aaron Bertrand Jun 12 '12 at 22:19
    
@AaronBertrand - Yes. (2008 R2 Enterprise Edition) –  Joseph Sturtevant Jun 12 '12 at 22:26

1 Answer 1

up vote 5 down vote accepted

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 constraint 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 constraint 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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