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.