What should I do when SQL Server query is stuck ?( it Help for Solve SQL Query Time Out Error)

First, open SQL Server Activity Monitor, you should wait some time before data is loaded

The Activity Monitor is available in SQL Server 2008 Management Studio is a great tool which can be used to get a quick overview of SQL Server 2008 system performance. The Activity Monitor tool in the previous version of SQL Server used to display information related to processes, lock by objects and locks by process. There are many enhancements in Activity Monitor in SQL Server 2008 like a graphical display of processor time, waiting tasks, database I/O's, batch requests, processes, resource waits, data file I/O's and also information about the most expensive queries.
To view Activity Monitor in SQL Server 2005 and in SQL Server 2008, a user must have VIEW SERVER STATE permission

Plz Show Recent Expensive Queries( it Help for SQL  Time Out Error Solve)

One of the first lines of defense in determining why a query is stuck is to use sp_who2. It will shows all the sessions that are currently established in the database. These are denoted as SPID’s, or server process Id’s.

sp_who2

The first 50 results are system SPIDs. Generally these do not effect slowdowns of the system. These system events include the Checkpoint writer, Log writers and task schedulers. User processes are SPID numbers 50 and over. In diagnosing slowdowns it is these SPIDs that are the potential resource hogs.

There are four main things to look for when when diagnosing slowdowns:

• Blocking
• High CPU usage
• High IO usage
• Multiple entries for the same SPID (representing parallelism)

When a number is shown in the column named BlkBy, this represents the SPID that is currently stopping the SPID in the row shown. Sometimes many rows will show SPID numbers in the BlkBy column. This is because there is a chain of blockers. The way this occurs usually starts with one “lead” blocker blocking another process. In turn, the process that is being blocked, blocks others. This occurs down a chain. It can be a messy situation. In order to rectify, you may have to kill the lead blocker. If it happens often, you will want to research why this particular process is blocking. So, before you kill any process, find out what statement it is running first. To do this, execute DBCC INPUTBUFFER

DBCC INPUTBUFFER(500) -- 500 represent the SPID you see in the result of sp_who2

Once you find the blocker, you may need to kill it. To do so use the kill command (replace the number below with the blocking SPID)
IMPORTANT: before the kill, keep a copy of the SQL statement being run for later investigation

KILL 500

sp_who2 does provide limited information regarding slowdowns.

For more information use the following script that shows the SQL statement being run, only sessions that have a current executing request, reads and writes for the current command, along with the number of reads and writes for the entire SPID and the protocol being used (TCP, NamedPipes, or Shared Memory)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT  SPID = er.session_id ,
        BlkBy = er.blocking_session_id ,
        ElapsedMS = er.total_elapsed_time ,
        CPU = er.cpu_time ,
        IOReads = er.logical_reads + er.reads ,
        IOWrites = er.writes ,
        Executions = ec.execution_count ,
        CommandType = er.command ,
        ObjectName = OBJECT_SCHEMA_NAME(qt.objectid, dbid) + '.'
        + OBJECT_NAME(qt.objectid, qt.dbid) ,
        SQLStatement = SUBSTRING(qt.text, er.statement_start_offset / 2,
                                 ( CASE WHEN er.statement_end_offset = -1
                                        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                                             * 2
                                        ELSE er.statement_end_offset
                                   END - er.statement_start_offset ) / 2) ,
        Status = ses.status ,
        [Login] = ses.login_name ,
        HOST = ses.host_name ,
        DBName = DB_NAME(er.database_id) ,
        LastWaitType = er.last_wait_type ,
        StartTime = er.start_time ,
        Protocol = con.net_transport ,
        transaction_isolation = CASE ses.transaction_isolation_level
                                  WHEN 0 THEN 'Unspecified'
                                  WHEN 1 THEN 'Read Uncommitted'
                                  WHEN 2 THEN 'Read Committed'
                                  WHEN 3 THEN 'Repeatable'
                                  WHEN 4 THEN 'Serializable'
                                  WHEN 5 THEN 'Snapshot'
                                END ,
        ConnectionWrites = con.num_writes ,
        ConnectionReads = con.num_reads ,
        ClientAddress = con.client_net_address ,
        Authentication = con.auth_scheme
FROM    sys.dm_exec_requests er
        LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
        LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
        OUTER APPLY ( SELECT    execution_count = MAX(cp.usecounts)
                      FROM      sys.dm_exec_cached_plans cp
                      WHERE     cp.plan_handle = er.plan_handle
                    ) ec
ORDER BY er.blocking_session_id DESC ,
        er.logical_reads + er.reads DESC ,
        er.session_id

The following script will show the blocking processes (lead blocker)

   
SELECT  spid ,
        sp.status ,
        loginame = SUBSTRING(loginame, 1, 12) ,
        hostname = SUBSTRING(hostname, 1, 12) ,
        blk = CONVERT(CHAR(3), blocked) ,
        open_tran ,
        dbname = SUBSTRING(DB_NAME(sp.dbid), 1, 10) ,
        cmd ,
        waittype ,
        waittime ,
        last_batch ,
        SQLStatement = SUBSTRING(qt.text, er.statement_start_offset / 2,
                                 ( CASE WHEN er.statement_end_offset = -1
                                        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                                             * 2
                                        ELSE er.statement_end_offset
                                   END - er.statement_start_offset ) / 2)
FROM    master.dbo.sysprocesses sp
        LEFT JOIN sys.dm_exec_requests er ON er.session_id = sp.spid
        OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE   spid IN ( SELECT    blocked
                  FROM      master.dbo.sysprocesses )
        AND blocked = 0

The following script will find most expensive queries (remark the needed ORDER BY)


SELECT DISTINCT TOP 10
        t.TEXT QueryName ,
        s.execution_count AS ExecutionCount ,
        s.max_elapsed_time AS MaxElapsedTime ,
        ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime ,
        s.creation_time AS LogCreatedOn ,
        ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM    sys.dm_exec_query_stats s
        CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
ORDER BY s.max_elapsed_time DESC

LETS CHECK, do the following steps and run the above scripts when needed

-- open new query window (SESSION A) and run the following 3 lines USE [AdventureWorks] BEGIN TRANSACTION UPDATE  Sales.SalesOrderDetail SET     [UnitPrice] = 1  -- this update takes ~30sec -- while the above update in progress, open a new query window (SESSION B) and run sp_who2 -- open another query window (SESSION C) and run the following line, it will be blocked by SESSION A because the transaction there is not committed yet UPDATE  Sales.SalesOrderDetail SET     [UnitPrice] = 2 -- while the above update in progress, go to SESSION B query window and run again sp_who2, you will see the blocking -- return to SESSION A query window and rollback the transaction. This will free the blocking in SESSION C (after the update finishes, takes ~30sec) ROLLBACK

If you find this solution useful,. Please Like and comment of page. Thanks!