locked
Select quert is pretty slow. RRS feed

  • Question

  • Sql Server 2014. I have a table. Even using a simple query in management studio it is still slow.

     select count(*)  FROM MyTable with (nolock)

    It returns 178858. If using

     select count(*)  FROM MyTable

    or

     select * FROM MyTable

    Then after 20 minutes it still hangs there.

    Run the query
    SELECT @@TRANCOUNT
    It returns 1 then.



    • Edited by ardmore Monday, August 29, 2016 8:45 PM
    Monday, August 29, 2016 8:40 PM

All replies

  • Some process is blocking you.

    I am guessing that you have begun a transaction in the window you are running the above queries in.  I'm guess you did an update in that window or insert.

    Another process is trying to update the above table but is waiting for your window to commit or rollback the transaction you began.  It is blocked and that other process is now blocking your window.


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed as answer by Enric Vives Tuesday, August 30, 2016 12:26 PM
    Monday, August 29, 2016 9:22 PM
  • I think you can check on, if any query is blocking yours on Sp_who2 or sysprocesses table.

    You can close that window and open another window to execute query again and see.

    Select * will  take some time, you can try select count(1) too.

    As shared above, you can use begin tran and end tran to see too.

    Check also lock details of the query too, how locks are getting locked and released.


    Santosh Singh

    Monday, August 29, 2016 9:28 PM
  • Caveat: You cannot rely on the result when using NOLOCK. The number can be to low or to high, depending on the reason why your SELECT is blocked.
    Monday, August 29, 2016 9:43 PM
  • Generally if you try to output all the columns and all the rows of a huge table, then it will be slow because it needs to perform lot of IO operations. Try to use a WHERE clause for indexed columns so that your queries can be faster. 

    Monday, August 29, 2016 10:07 PM
  • Hi 

    You check for any open transaction in your database by using DBCC command

    DBCC OPENTRAN()  - 

    SELECT TOP 10 * FROM sys.dm_tran_database_transactions  (if you got admin privilages)

    Identify the SPID and check what process running against it. Check for the importance of the process, so that proceed with kill or wait.

    Generally SSMS query result window, binding of all the data in that results will take time , ram and etc. We need to consider the first record loading is the execution time.

    Blocking of your query by another process is identified by using Sp_who2  mentioned by Santhosh. 

    Thanks 

    Nandha

    Tuesday, August 30, 2016 10:54 AM
  • I tried
      begin transaction
      select count(1) from MyTable
     
    or
    begin transaction
      select * from MyTable where USER='me'
    It is still slow. I can't find Sp_who2 or sysprocesses table. I don't what is it.

    Tuesday, August 30, 2016 11:59 AM
  • It may occur under default (read committed isolation level as well) 

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx


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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, August 30, 2016 12:10 PM
    Answerer
  • Run

    EXECUTE sp_who2;

    in a separate SSMS query window.

    Tuesday, August 30, 2016 12:11 PM
  • Yes. I run

    EXECUTE sp_who2;

    In the Status column, there are different values.

    "BACKGROUND", "sleeping", "SUSPENDED" and "RUNNABLE". So what I have to do?

    Tuesday, August 30, 2016 12:52 PM
  • To know which session is locking you : 

    1- Get  session id of the long running query with @@SPID

    SELECT @@SPID
    GO

    select * FROM MyTable

    2- on a new query window, get session which is locking the long running query:

      select  waiting_task_address, wait_duration_ms, wait_type, session_id, blocking_session_id, request_mode, request_type , request_status from sys.dm_os_waiting_tasks as wt
     join  sys.dm_tran_locks as TL on TL.lock_owner_address = wt.resource_address
     where TL.request_status = 'WAIT'and session_id = put here the number return by point 1

    The blocking session id is in blocking_session_id column.

    Tuesday, August 30, 2016 2:24 PM
  • Basically I already found the id. But I can't kill it. I got an error.

    Process ID 122 is not an active process ID.
    I find in the ProgramName column, it shows an web application name. In this web application, I have the insert query. However I already stopped the application. So how to kill it?

    Tuesday, August 30, 2016 5:30 PM
  • EXEC sp_who2 <spid>

    Check whether the above statement returns anything for the spid which you're looking for?


    Tuesday, August 30, 2016 5:52 PM
  • Yes. It returns one row. But the DBName column shows the database name is not my working DB. The status is "sleeping". The Command column is "AWAITING COMMAND". The Login column is not me, it seems to be a test account.
    • Edited by ardmore Tuesday, August 30, 2016 8:21 PM
    Tuesday, August 30, 2016 8:20 PM
  • there is kill action in DB, have you tried it?
    Thursday, September 1, 2016 10:25 AM