SQL Server查询占用100％的CPU并运行数小时

I have a query that has been running every day for a little over 2 years now and has typically taken less than 30 seconds to complete. All of a sudden, yesterday, the query started taking 3+ hours to complete and was using 100% CPU the entire time.

我的查询现在每天运行2年多一点,通常需要不到30秒的时间才能完成。突然间,昨天,查询开始需要3个多小时才能完成,并且整个时间都在使用100%的CPU。

The SQL is:

SQL是:

SELECT
  @id, 
  alpha.A, alpha.B, alpha.C, 
  beta.X,  beta.Y,  beta.Z, 
  alpha.P, alpha.Q
FROM 
  [DifferentDatabase].dbo.fnGetStuff(@id) beta 
  INNER JOIN vwSomeData alpha ON beta.id = alpha.id

alpha.id is a BIGINT type and beta.id is an INT type. dbo.fnGetStuff() is a simple SELECT statement with 2 INNER JOINs on tables in the same DB, using a WHERE id = @id. The function returns approximately 11000 results.

alpha.id是BIGINT类型,beta.id是INT类型。 dbo.fnGetStuff()是一个简单的SELECT语句,在同一个DB中的表上有2个INNER JOIN,使用WHERE id = @id。该函数返回大约11000个结果。

The view vwSomeData is a simple SELECT statement with two INNER JOINs that returns about 590000 results.

视图vwSomeData是一个简单的SELECT语句,带有两个INNER JOIN,返回大约590000个结果。

Both the view and the function will complete in less than 10 seconds when executed by themselves. Selecting the results of the function into a temporary table first and then joining on that makes the query finish in < 10 seconds.

视图和函数都会在不到10秒的时间内完成。首先将函数的结果选择到临时表中,然后再加入该函数,使查询在<10秒内完成。

How do I troubleshoot what's going on? I don't see any locks in the activity manager.

如何解决正在发生的事情?我没有在活动管理器中看到任何锁定。

8 个解决方案

#1

Look at the query plan. My guess is that there is a table scan or more in the execution plan. This will cause huge amounts of I/O for the few record you get in the result.

查看查询计划。我的猜测是执行计划中有一个或更多的表扫描。这将导致您在结果中获得的少量记录的大量I / O.

#2

You could use the SQL Server Profiler tool to monitor what queries are running on SQL Server. It doesn't show the locks, but it can for instance also give you hints on how to improve your query by suggesting indexes.

您可以使用SQL Server Profiler工具来监视在SQL Server上运行的查询。它不显示锁,但它也可以为您提供有关如何通过建议索引来改进查询的提示。

#3

If you've got a reasonably recent version of SQL Server Management Studio, it has a Database Tuning Adviser as well, under Tools. It takes a trace from the Profiler and makes some, sometimes highly useful, suggestions. Makes sure there's not too many queries - it takes a long time to build advice.

如果您有一个最新版本的SQL Server Management Studio,它还有一个Database Tuning Adviser,位于Tools下。它需要从Profiler中获取一些内容,并提出一些有时非常有用的建议。确保没有太多查询 - 建立建议需要很长时间。

I'm not an expert on it, but have had some luck with it in the past.

我不是它的专家,但过去运气不错。

#4

Do you need to use a function? Can you re-write the entire thing into a stored procedure in which you pass in the @ID as a parameter.

你需要使用一个功能吗?您可以将整个事物重写到存储过程中,在该过程中将@ID作为参数传递。

Even if your table has indexes because you pass the @ID as a variable to the WHERE clause potentially greatly increasing the amount of time for the query to run.

即使您的表具有索引,因为您将@ID作为变量传递给WHERE子句可能会大大增加查询运行的时间。

The reason the indexes may not be used is because the Query Analyzer does not know the value of the variables when it selects an access method to perform the query. Because this is a batch file, only one pass is made of the Transact-SQL code, preventing the Query Optimizer from knowing what it needs to know in order to select an access method that uses the indexes.

不能使用索引的原因是查询分析器在选择执行查询的访问方法时不知道变量的值。因为这是一个批处理文件,所以只有一个Transact-SQL代码传递,阻止查询优化器知道它需要知道什么才能选择使用索引的访问方法。

You might want to consider an INDEX query hint if you cannot re-write the SQL.

如果无法重写SQL,可能需要考虑INDEX查询提示。

it might also be possible, since this just started happening, that the INDEXes have become fragmented and might need to be rebuilt.

由于这种情况刚刚开始发生,因此INDEX可能已经变得支离破碎,可能需要重建。

#5

I've had similar problems with joining functions that return large datasets. I had to do what you've already suggested. Put the results in a temp table and join on that.

我在加入返回大型数据集的函数方面遇到了类似的问题。我必须做你已经建议的事情。将结果放在临时表中并加入其中。

#6

Look at the estimated plan, this will probably shed some light. Typically when query cost gets orders of magnitude more expensive it is because a loop or merge join is being used where a hash join is more appropriate. If you see a loop or merge join in the estimated plan, look at the number of rows it expects to process - is it far smaller than the number of rows you know will actually be in play? You can also specify a hint to use a hash join and see if it performs much better. If so, try updating statistics and see if it goes back to a hash join without a hint.

看看估计的计划,这可能会有所启发。通常,当查询成本变得更加昂贵时,这是因为正在使用循环或合并连接,其中散列连接更合适。如果在估计的计划中看到循环或合并连接,请查看它希望处理的行数 - 它是否远小于您知道实际将在其中运行的行数?您还可以指定提示以使用散列连接,并查看它是否执行得更好。如果是这样,请尝试更新统计信息,看看它是否在没有提示的情况下返回到散列连接。

SELECT
  @id, 
  alpha.A, alpha.B, alpha.C, 
  beta.X,  beta.Y,  beta.Z, 
  alpha.P, alpha.Q
FROM 
  [DifferentDatabase].dbo.fnGetStuff(@id) beta 
  INNER HASH JOIN vwSomeData alpha ON beta.id = alpha.id

#7

-- having no idea what type of schema is in place and just trying to throw out ideas:

- 不知道什么类型的架构到位,只是试图抛出想法:

Like others have said... use Profiler and find the source of pain... but I'm thinking it is the function on the other database. Since that function might be a source of pain, have you thought about a little denormalization or anything on [DifferentDatabase]. I think you'll find a bit more scalability in joining to a more flattened table with indexes than a costly function.

像其他人一样说...使用Profiler并找到痛苦的根源......但我认为这是其他数据库的功能。由于该函数可能是痛苦的根源,您是否考虑过在[DifferentDatabase]上进行一些非规范化或任何操作。我认为你会发现在使用索引而不是昂贵的函数加入更扁平的表时会有更多的可扩展性。

#8

Run this command:

运行此命令:

SET SHOWPLAN_ALL ON

Then run your query. It will display the execution plan, look for a "SCAN" on an index or a table. That is most likely what is happening to your query now. If that is the case, try to figure out why it is not using indexes now (refresh statistics, etc)

然后运行您的查询。它将显示执行计划,在索引或表上查找“SCAN”。这很可能是您现在的查询正在发生的事情。如果是这种情况,请尝试找出它现在不使用索引的原因(刷新统计信息等)

#1