SQL Server Query occupies 100% of the processor and runs for hours

Question

I have a query that works every day for a little over 2 years and usually takes less than 30 seconds. Suddenly, yesterday, the request began to take 3 hours to complete and used a 100% processor all the time.

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- type BIGINT, and beta.id- type INT. dbo.fnGetStuff()- A simple SELECT statement with 2 INNER JOINs on tables in the same database using WHERE id = @id. The function returns approximately 11000 results.

A view vwSomeDatais a simple SELECT statement with two INNER JOINs that returns about 590,000 results.

10 , . , < 10 .

, ? .

Answer 1

. , . - , .

Answer 2

SQL Server Profiler SQL Server. , , , , , .

Answer 3

SQL Server Management Studio, , "". , . , - .

, .

Answer 4

? , @ID .

, @ID WHERE, .

, , , Query Analyzer , . , Transact-SQL, , , , .

, INDEX, SQL.

, , , , .

Answer 5

, . , . temp .

Answer 6

, , , . , , , , - . , , , - , , , , ? - , . , , - .

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 7

- , :

... Profiler ... , . , - [DifferentDatabase]. , , .

Answer 8

:

SET SHOWPLAN_ALL ON

. , "" . , , . , , ( ..)