Seesion ids are suspended

Question

Seesion ids are suspended

SQL Galaxy

One Orange Chip

Points: 29472
More actions
July 28, 2011 at 6:13 am

#254477

Hi,
I got a call from client for application going to very slow performance,
sp_WhoIsActive (version who_is_active_v10_00)- return more than 20 session ids were suspended,
Suspended – a query which is waiting due to any reason.
also checked index fragmentation and stats everything is normal and all index below 25%, stats are upto date updated.
Please tell me, how to troubleshoot for suspended sessions ids.
Thanks
ananda

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Check what they're waiting for. The wait_type column.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

GilaMonster (7/28/2011)
Check what they're waiting for. The wait_type column.

Thanks for reply...

I checked wait_type column as below, too much wait stats and display related Query and stored procedure, Program name (.Net SqlClient Data Provider)

(142987ms)LCK_M_IX

(143161ms)LCK_M_IX

(143565ms)LCK_M_IX

(206027ms)LCK_M_IX

(300904ms)LCK_M_IX

(303399ms)LCK_M_IX

Can I kill these session Ids?

thanks

ananda

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

They're waiting for locks. Hence you have some blocking.

You should never just kill something unless you know what impact is will have. What if it's something critically important.

Check what they're blocked by (blocking_session). Check what that session is doing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

GilaMonster (7/28/2011)
They're waiting for locks. Hence you have some blocking.
You should never just kill something unless you know what impact is will have. What if it's something critically important.
Check what they're blocked by (blocking_session). Check what that session is doing.

those locks(LCK_M_IX) are intent exclusive lock has locked DML statement on table, so other user cannot access.

I checked blocked session column, there is no blocked by any session.

also checked sp_who2 blocked column is 0.

thanks

ananda

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

That's over 5 minutes of blocking. Is someone [re]building an index or something like that?

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

Ninja's_RGR'us (7/28/2011)
That's over 5 minutes of blocking. Is someone [re]building an index or something like that?

No one can run rebuilding index..,

Query life cycle

Running - a query which is being executed on a CPU is called a running query. This query is responsible for CPU time.

Runnable – a query which is ready to execute and waiting for its turn to run is called a runnable query. This query is responsible for Signal Wait time. (In other words, the query is ready to run but CPU is servicing another query).

Suspended – a query which is waiting due to any reason (to know the reason, we are learning wait stats) to be converted to runnable is suspended query. This query is responsible for wait time. (In other words, this is the time we are trying to reduce).

please tell me, how to reduce and avoid the suspended query?

thanks

ananda

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

ananda.murugesan (7/28/2011)
GilaMonster (7/28/2011)
They're waiting for locks. Hence you have some blocking.
You should never just kill something unless you know what impact is will have. What if it's something critically important.
Check what they're blocked by (blocking_session). Check what that session is doing.
those locks(LCK_M_IX) are intent exclusive lock has locked DML statement on table, so other user cannot access.

I'm well aware what they are, thank you.

I checked blocked session column, there is no blocked by any session.
also checked sp_who2 blocked column is 0.

The sessions that are suspended with a lock-related wait are not blocked? Absolutely sure you're looking at the right sessions?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

ananda.murugesan (7/28/2011)
Query life cycle
Running - a query which is being executed on a CPU is called a running query. This query is responsible for CPU time.
Runnable – a query which is ready to execute and waiting for its turn to run is called a runnable query. This query is responsible for Signal Wait time. (In other words, the query is ready to run but CPU is servicing another query).
Suspended – a query which is waiting due to any reason (to know the reason, we are learning wait stats) to be converted to runnable is suspended query. This query is responsible for wait time. (In other words, this is the time we are trying to reduce).

Yes, Ninja and I are quite familiar with how queries run I assure you.

please tell me, how to reduce and avoid the suspended query?

You look at what it's waiting for and you take steps to reduce or eliminate that.

In the case of lock-related waits you find what's holding the locks that the blocked session wants (and you either kill it or wait for it to finish), then you optimise the queries to reduce the length of time locks are held.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Find out what the query is waiting on.

Figure out a way for that process to work faster or not during prod hours or not at all.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Here's a script I recently found here (sorry didn't save the credits so I can't give the original url).

It gives you the head blocker and way more info on what's gonig on. Com eback to us if you still don't know how to solve it with that info.

SELECT

[Session ID] = s.session_id,

[User Process] = CONVERT(CHAR(1), s.is_user_process),

[Login] = s.login_name,

[Database] = ISNULL(db_name(p.dbid), N''),

[Task State] = ISNULL(t.task_state, N''),

[Command] = ISNULL(r.command, N''),

[Application] = ISNULL(s.program_name, N''),

[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),

[Wait Type] = ISNULL(w.wait_type, N''),

[Wait Resource] = ISNULL(w.resource_description, N''),

[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),

[Head Blocker] =

CASE

-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others

WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'

-- session is either not blocking someone, or is blocking someone but is blocked by another party

ELSE ''

END,

[Total CPU (ms)] = s.cpu_time,

[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,

[Memory Use (KB)] = s.memory_usage * 8192 / 1024,

[Open Transactions] = ISNULL(r.open_transaction_count,0),

[Login Time] = s.login_time,

[Last Request Start Time] = s.last_request_start_time,

[Host Name] = ISNULL(s.host_name, N''),

[Net Address] = ISNULL(c.client_net_address, N''),

[Execution Context ID] = ISNULL(t.exec_context_id, 0),

[Request ID] = ISNULL(r.request_id, 0)

-- [Workload Group] = ISNULL(g.name, N'')

FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)

LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)

LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)

LEFT OUTER JOIN

(

-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as

-- waiting for several different threads. This will cause that thread to show up in multiple rows

-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,

-- and use it as representative of the other wait relationships this thread is involved in.

SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num

FROM sys.dm_os_waiting_tasks

) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1

LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)

--LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005

LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)

ORDER BY s.session_id;

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Got it //

Source link : http://sqlfrndz.com/2011/06/09/activity-monitor-script-to-find-head-blocker-sql-server-2005-2008-and-later/

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 12

Hi,

Thanks for this script for monitor head blocker session, it is very useful.

I checked this script and display system process are suspended, please tell me, Is there any issues?

I think once completed these process after that it will be clear.

Database Task State CommandWait Time (ms)Wait Type

masterSUSPENDEDXE TIMER8016XE_TIMER_EVENT

masterSUSPENDEDLAZY WRITER918LAZYWRITER_SLEEP

masterSUSPENDEDLOG WRITER3608LOGMGR_QUEUE

masterSUSPENDEDLOCK MONITOR1843REQUEST_FOR_DEADLOCK_SEARCH

masterSUSPENDEDSIGNAL HANDLER1667860307KSOURCE_WAKEUP

masterSUSPENDEDTRACE QUEUE TASK358SQLTRACE_BUFFER_FLUSH

masterSUSPENDEDCHECKPOINT1307873CHECKPOINT_QUEUE

masterSUSPENDEDBRKR TASK1016SLEEP_TASK

masterSUSPENDEDTASK MANAGER1667873518ONDEMAND_TASK_QUEUE

masterSUSPENDEDBRKR EVENT HNDLR31464617BROKER_EVENTHANDLER

masterSUSPENDEDBRKR TASK1667872460BROKER_TRANSMITTER

Thanks

ananda

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

System processes are supposed to be waiting most of the time. They're waiting for stuff to do, when the find stuff to do, they wake up, do it and wait again.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 14

Thanks for reply & help, I understood why system process waiting long time.

Could you give me, Script for receiving email alert which are user process staying long wait time, suspended, sleeping and got this blocked.

I don't have any 3rd party tools for monitoring SQL Database.