Sql-server – Prevent a single query from hogging all resources

performancequery-performancesql serversql-server-2008-r2

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?

Best Answer

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.

Related Solutions

Sql-server – Single query sending and retreiving duplicate data on MS SQL Server

Try running a sql profile trace on the server activity (http://technet.microsoft.com/en-us/library/ms175047.aspx) - you may have some table locking issues caused by concurrently running queries locking tables/pages/indexes.

Try running a perfmon - there may be something/s running that is using all the available disk/IO/CPU/Memory resources when things slow down. Look for any memory swapping - this will slow things down. If it is one of these then you will need to track down the cause. Note CPU at 100% for short period of times is OK (actually can indicate optimum performance).

Also check for fragmentation and consider rebuilding indexes.

Turn off services/applications that are not needed - they use up resources that are better used for your application.

Mysql – Reviewing all new MySql queries on staging environment

These are some tools suggested by MySQL Performance blog post: http://www.mysqlperformanceblog.com/tools/
pt-query-digest can be useful to review queries as well It can review a slow log, saving results to the test.query_review table in a MySQL server running on host1. See --review for more on reviewing queries:

pt-query-digest --review h=host1,D=test,t=query_review /path/to/slow.log
Using Show Profiles also you can review query performance

http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html

SET PROFILING = 1; 
SET profiling_history_size = 0; 
SET profiling_history_size = 15; 
<Your own query>; 
SHOW PROFILES; 
select state, round(sum(duration),5) as `duration (summed) in sec` from information_schema.profiling where query_id = 11 group by state order by `duration (summed) in sec` desc; 
SET PROFILING = 0;

EXPLAIN EXTENDED [Your own query];

When EXPLAIN is used with the EXTENDED keyword, the output includes a filtered column not otherwise displayed. This column indicates the estimated percentage of table rows that will be filtered by the table condition. In addition, the statement produces extra information that can be viewed by issuing a SHOW WARNINGS statement following the EXPLAIN statement. The Message value in SHOW WARNINGS output displays how the optimizer qualifies table and column names in the SELECT statement, what the SELECT looks like after the application of rewriting and optimization rules, and possibly other notes about the optimization process.

Best Answer

Related Solutions

Sql-server – Single query sending and retreiving duplicate data on MS SQL Server

Mysql – Reviewing all new MySql queries on staging environment

Related Question