SQL Server query taking up 100% CPU and runs for hours

Question

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.

The SQL is:

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.

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

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.

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

Answer 1

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.

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

Answer 3

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.

Answer 4

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.

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

Answer 5

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

Answer 6

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

Answer 7

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)

Answer 8

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.

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.

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.

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

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