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