Query which ran fine now runs very slow with no output

Question

Hi,

I had a query this morning which ran fine. After 30 minutes suddenly without me changing the code, the same query now doesn't return output. Instead it is running endlessly.

When I query each table of this query seperatly I get output quickly.

I checked when the last DB statistics where ran for the tables this query is based on (maybe the statistics had some error causing this), but I saw the last run was yesterday so this shouldn't be the problem.

Any suggestions?

Thanks

---

Namnami

Answer 1

Parameter sniffing? http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

My query is a simple query, not a stored procedure. I have two variables it defines in the begining and uses inside the query. Even after I comment out these variables and use specific values in the where clause of the query, query still runns endlessly.

Also, I ran this query several times in the past with different variables and it never made me problems!

Any advice?

Thanks

---

Namnami

Answer 3

Have you checked for blocking? Use sp_who or sp_who2. Check the Blk column. If there is a value that is the spid which is blocking the spid on that line. Check if the latter spid agrees with the spid for your query window. (You find it in the status bar of the window.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Run DBCC FREEPROCCACHE and re-run the query?

GO

How do you run it ? That way?

declare @i int
set @i = 56004
SELECT *
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = @i
GO

------Try this way

EXEC sp_executesql N'SELECT  SUM(LineTotal) AS LineTotal
FROM Sales.SalesOrderHeader H
JOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', 56000
GO

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 5

I don't understand your suggestion/s.

I just run it simply:

use exampleDB

declare @dateLeft DATETIME;
declare @agentName nVarChar(20);

set @dateLeft =  '2010-11-03' ;
set @agentName = 'Albert Einstein' ;

SELECT ... 
WHERE Agents.modifiedOn = @dateLeft 
AND Agents.AgentName = @agentName 
 

Pls clarify

Thanks

---

Namnami

Answer 6

I was trying to re-write the above query to use sp_executesql to allow SQL Server optimizer to reuse existing plan rather creating a new one each you execute a query....

EXEC sp_executesql N'SELECT ...

WHERE Agents.modifiedOn = @dateLeft 
AND Agents.AgentName = @agentName', N'@dateLeft DATETIME,@agentName nvarchar(20)', '2010-11-03','Albert Einstein

GO

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 7

Sorry, using EXEC sp_executesql as you explain does not help.

---

Namnami

Answer 8

What error are you getting?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 9

no error, it just runs endlessly

---

Namnami

Answer 10

Sorry, no blk values (in SP_who nor in SP_who2).

---

Namnami

Answer 11

Run DBCC CHECKDB(). Your database could suffer from corruption.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 12

can you paste your query here..?

Answer 13

I'm not sure what the problem was but after restarting the server it runs fine now, b"h!

Thanks for all who tried helping me out!

---

Namnami

Answer 14

Glad you solved the problem

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 15

Hi Namnami,

Without any endless help for your endlessly executing query , you got your solution !!

Sometimes it happens when the server is on from a long time. Even the same i suffered. I tried my all option but failed. After re-starting the server, i got my result..!!!

Please close this thread !!!

---

Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com