none
Query goes into queue

    Question

  • What can be the reason if query goes into queue frequently for a long? How can I optimize query and improve database's performance?

    Tuesday, February 06, 2018 11:32 AM

All replies

  • sorry didnt understand

    What queue query goes into? Do mean process being in suspended state?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, February 06, 2018 11:35 AM
  • Do you mean blocking? 

    http://www.jamesserra.com/archive/2011/09/how-to-prevent-blocking-in-your-sql-server-database/


    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, February 06, 2018 11:47 AM
    Answerer
  • What can be the reason if query goes into queue frequently for a long? How can I optimize query and improve database's performance?

    What do you mean by queue, I suppose this is not the correct word and you want to convey that query gets suspended ??.

    Optimizing a query is not a task which can be given on a reply it is more of a art than knowledge.

    Please read How It Works: What is a Sleeping / Awaiting Command Session


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, February 06, 2018 12:56 PM
    Moderator
  • Hi Amita,

    I suppose you are victim of threadpool starvation, aren't you.

    Threadpool starvation means that there are not enough worker threads to process the query.

    You can check it by usage of sys.dm_os_waiting_tasks; what is the wait_type for the process?


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Tuesday, February 06, 2018 1:37 PM
  • There can be the several reasons and you can start by checking the wait event. You have to start monitoring the database first as there are many things that go into improving database's performance.

    You can start by having a SQL check list and then start from there; 

    https://www.brentozar.com/archive/2012/06/sql-server-poor-performance-checklist/

    Thanks.

    Tuesday, February 06, 2018 1:55 PM
  • Means Queries goes into sleeping state.
    Wednesday, February 07, 2018 7:13 AM
  • Means Queries goes into sleeping state.
    Mostly queries go into sleeping state when they have done there task and the window which fired the query is not  "closed", but such queries do not consume memory or CPU. OTOH is you see lot of queries going in sleeping state it might be problem from application which is not closing the connections properly

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, February 07, 2018 7:20 AM
    Moderator
  • Following is the result of the above query(I have given you the wait type column only)

    wait_type
    LAZYWRITER_SLEEP
    FT_IFTS_SCHEDULER_IDLE_WAIT
    LOGMGR_QUEUE
    WRITELOG
    KSOURCE_WAKEUP
    ONDEMAND_TASK_QUEUE
    WRITELOG
    WRITELOG
    WRITELOG
    BROKER_TO_FLUSH
    WRITELOG
    BROKER_TRANSMITTER
    WRITELOG
    PAGEIOLATCH_SH
    WRITELOG
    WRITELOG
    LCK_M_S
    WRITELOG
    WRITELOG
    XE_DISPATCHER_WAIT
    SQLTRACE_INCREMENTAL_FLUSH_SLEEP
    CLR_AUTO_EVENT
    CLR_AUTO_EVENT
    LAZYWRITER_SLEEP
    WRITELOG
    REQUEST_FOR_DEADLOCK_SEARCH
    WRITELOG
    WRITELOG
    CHECKPOINT_QUEUE
    WRITELOG
    BROKER_EVENTHANDLER
    WRITELOG
    BROKER_TRANSMITTER
    WRITELOG
    LCK_M_S
    WRITELOG
    WRITELOG
    WRITELOG
    LCK_M_S
    XE_TIMER_EVENT


    Wednesday, February 07, 2018 7:34 AM
  • How can I resolve it if application is not closing the connection properly?

    Wednesday, February 07, 2018 9:42 AM
  • How can I resolve it if application is not closing the connection properly?

    Well first we need to check if "really" there is issue. You being new might think this may be issue but it may be that it may not be an issue. So my question would be are you facing some issue, which is "really" casing problems and escalations. For application not closing request you have to talk to Application team/Admin to see if the thread is closed after it has done its task.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, February 07, 2018 9:54 AM
    Moderator
  • You need to be more precise. And please give us as much info as possible, you won't wear out that keyboard from giving us more details. :-)

    A session can be in sleeping state.

    Whenever you execute something (like a query) it is either running (actually using the CPU), runnable (waiting for the CPU) or suspended (waiting for a resource). 

    I.e., a query cannot be in a sleeping state. Again, the more info you give us, with description of your problem, how it appears to you etc, the more likely it is we can help you.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, February 07, 2018 11:02 AM
    Moderator
  • Most applications have a connection pool as well, and if there are bursts of activity the pool can grow, and then a bunch of connections hang around sleeping with nothing to do, before they are eventually closed by the pool.

    Josh

    Wednesday, February 07, 2018 5:05 PM