July 27, 2014 at 5:08 am
I have a Wait Type Suspended Wait Type CXPacket Request Command Insert going on for 7 hours.
It is the only active process hat is active.
I do not know why?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 27, 2014 at 5:55 am
Welsh Corgi (7/27/2014)
I have a Wait Type Suspended Wait Type CXPacket Request Command Insert going on for 7 hours.It is the only active process hat is active.
I do not know why?
Pardon the snarky answer, but you do realize that you really haven't provided us with anything we could remotely work with here. We can't see what you see. We don't know what the active process is doing, or anything.
I may have a query that can help but it will take me a little bit to get it.
July 27, 2014 at 6:12 am
Lynn Pettis (7/27/2014)
Welsh Corgi (7/27/2014)
I have a Wait Type Suspended Wait Type CXPacket Request Command Insert going on for 7 hours.It is the only active process hat is active.
I do not know why?
Pardon the snarky answer, but you do realize that you really haven't provided us with anything we could remotely work with here. We can't see what you see. We don't know what the active process is doing, or anything.
I may have a query that can help but it will take me a little bit to get it.
Snarky not you. lol
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 27, 2014 at 6:51 am
Here is the code I run on our systems to look at performance. Also use it for identifying blocking as that happens more often. Couldn't air gap it so I had to manually copy it from my classified workstation. This query is not classified or owned by me, my company, or the government. I got it from one of my SQL books with some minor mods for my current situation. Unfortunately I can't give proper attribution to the original author at this time.
with baseperfdata(
session_id,
status,
login_time,
login_name,
host_name,
DatabaseName,
blocking_session_id,
command,
cpu_time,
reads,
writes,
logical_reads,
last_write,
last_request_start_time,
last_request_end_time,
program_name,
wait_type,
wait_resource,
TransactionIsolationLevel,
Object_name,
ExecutingStatement,
query_plan
) as (
select
es.session_id,
es.status,
es.login_time,
es.login_name,
es.host_name,
db_name(er.database_id) DatabaseName,
er.blocking_session_id,
er.command,
es.cpu_time,
es.reads,
es.writes,
es.logical_reads,
ec.last_write,
es.last_request_start_time,
es.last_request_end_time,
es.program_name,
er.wait_type,
er.last_wait_type,
er.wait_resource,
case es.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 TransactionIsolationLevel,
object_name(est.object_id,er.database_id) ObjectName,
substring(est.text,(er.statement_start_offset/2) + 1,
((case when er.statement_end_offset = -1
then datalength(est.text)
else er.statement_end_offset
end - er.statement_start_offset)/2) + 1) ExecutingStatement,
eqp.query_plan
from
sys.dm.dm_exec_sessions es
left outer join sys.dm_exec_requests er
on (es.session_id = er.session_id)
left outer join sys.dm_exec_connections ec
on (es.session_id = ec.session_id)
cross apply sys.dm_exec_sql_text(er.sql_handle) est
cross apply sys.dm_exec_query_plan(er.plan_handle) eqp
where
es.session_id <> @@spid -- exclude this session
), rCTE as ( -- this recursive CTE was added to group blocked sessions under the blocking session
select
bpd.session_id,
bpd.status,
bpd.login_time,
bpd.login_name,
bpd.host_name,
bpd.DatabaseName,
bpd.blocking_session_id,
bpd.command,
bpd.cpu_time,
bpd.reads,
bpd.writes,
bpd.logical_reads,
bpd.last_write,
bpd.last_request_start_time,
bpd.last_request_end_time,
bpd.program_name,
bpd.wait_type,
bpd.wait_resource,
bpd.TransactionIsolationLevel,
bpd.Object_name,
bpd.ExecutingStatement,
bpd.query_plan,
cast(bpd.blocking_session_id as varbinary(max)) + cast(bpd.blocking_session_id as varbinary(max)) SortKey
from
baseperfdata bpd
where
bpd.blocking_session_id = 0
union all
select
bpd.session_id,
bpd.status,
bpd.login_time,
bpd.login_name,
bpd.host_name,
bpd.DatabaseName,
bpd.blocking_session_id,
bpd.command,
bpd.cpu_time,
bpd.reads,
bpd.writes,
bpd.logical_reads,
bpd.last_write,
bpd.last_request_start_time,
bpd.last_request_end_time,
bpd.program_name,
bpd.wait_type,
bpd.wait_resource,
bpd.TransactionIsolationLevel,
bpd.Object_name,
bpd.ExecutingStatement,
bpd.query_plan,
r.SortKey + cast(bpd.blocking_session_id as varbinary(max)) + cast(bpd.blocking_session_id as varbinary(max)) SortKey
from
rCTE r
inner join baseperfdata bpd
on (r.session_id = bpd.blocking_session_id)
)
select
session_id,
status,
login_time,
login_name,
host_name,
DatabaseName,
blocking_session_id,
command,
cpu_time,
reads,
writes,
logical_reads,
last_write,
last_request_start_time,
last_request_end_time,
program_name,
wait_type,
wait_resource,
TransactionIsolationLevel,
Object_name,
ExecutingStatement,
query_plan
from
rCTE
order by
SortKey,
session_id;
July 27, 2014 at 10:14 am
You have an expensive, long-running query. That's about all that can be said from the details given. If you want to know more, figure out (from the DMVs) who's running the query and go and ask them about it.
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
July 27, 2014 at 8:44 pm
GilaMonster (7/27/2014)
You have an expensive, long-running query. That's about all that can be said from the details given. If you want to know more, figure out (from the DMVs) who's running the query and go and ask them about it.
I am to blame.
Someone entered a bunch of records. I killed the job after 22 hours.
Thanks.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply