Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Query which ran fine now runs very slow with no output

Odpovědět Query which ran fine now runs very slow with no output

  • Tuesday, May 01, 2012 10:19 AM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Tuesday, May 01, 2012 10:35 AM
    Answerer
     
     
    Sign In to Vote
    0
    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/

     
  • Tuesday, May 01, 2012 10:50 AM
     
     
    Sign In to Vote
    0

    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

     
  • Tuesday, May 01, 2012 12:23 PM
     
     
    Sign In to Vote
    0

    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, esquel@sommarskog.se
     
  • Tuesday, May 01, 2012 12:30 PM
    Answerer
     
     
    Sign In to Vote
    0

    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/


     
  • Wednesday, May 02, 2012 5:57 AM
     
      Has Code
    Sign In to Vote
    0

    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

     
  • Wednesday, May 02, 2012 6:40 AM
    Answerer
     
      Has Code
    Sign In to Vote
    0

    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/

     
  • Wednesday, May 02, 2012 9:46 AM
     
     
    Sign In to Vote
    0
    Sorry, using EXEC sp_executesql as you explain does not help.

    Namnami

     
  • Wednesday, May 02, 2012 9:47 AM
    Answerer
     
     
    Sign In to Vote
    0
    What error are you getting?

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

     
  • Wednesday, May 02, 2012 9:52 AM
     
     
    Sign In to Vote
    0
    no error, it just runs endlessly

    Namnami

     
  • Wednesday, May 02, 2012 9:58 AM
     
     
    Sign In to Vote
    0
    Sorry, no blk values (in SP_who nor in SP_who2).

    Namnami

     
  • Wednesday, May 02, 2012 10:36 AM
     
     
    Sign In to Vote
    0

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

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Wednesday, May 02, 2012 11:28 AM
     
     
    Sign In to Vote
    0
    can you paste your query here..?
     
  • Thursday, May 03, 2012 5:18 AM
     
     Answered
    Sign In to Vote
    0

    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

     
  • Thursday, May 03, 2012 5:40 AM
    Answerer
     
     
    Sign In to Vote
    0
    Glad you solved the problem

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

     
  • Thursday, May 03, 2012 9:09 AM
     
     
    Sign In to Vote
    0

    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