September 14, 2010 at 6:53 am
Hi All,
I set a job to run every morning, for the first few days it worked fine but from yesterday morning, when I run the query that checks if there are any jobs that are blocks, it showed that the job starts running but stops after a second then blocks it's self. The jobs wait type is CXPACKET and also PAGEIOLATCH_SH.
Would someone let me know how I can resolve this please?
Thank you
September 14, 2010 at 7:21 am
These wait types look like the T-SQL is running in parallel and result sets are being combined. However, I am curious what you are looking at that is showing that a session id is being blocked. You have some options, but the easiest might be to set the maxdop (max degree of parallelism) for the SQL statement to 1.
Personally what I would do is identify the offending T-SQL and analyze it independently of the job. I would look at the execution plan and address any issues.
While the maxdop hint might help you, my guess is that there is no blocking because a process won't block itself. My guess is that statistics/volume was such that the first couple times it ran there was a decent execution plan, but now the execution plan is suboptimal.
September 14, 2010 at 7:25 am
Please provide more information on Job, what does it do?
Abhijit - http://abhijitmore.wordpress.com
September 14, 2010 at 7:31 am
Toby White (9/14/2010)
These wait types look like the T-SQL is running in parallel and result sets are being combined. However, I am curious what you are looking at that is showing that a session id is being blocked. You have some options, but the easiest might be to set the maxdop (max degree of parallelism) for the SQL statement to 1.Personally what I would do is identify the offending T-SQL and analyze it independently of the job. I would look at the execution plan and address any issues.
While the maxdop hint might help you, my guess is that there is no blocking because a process won't block itself. My guess is that statistics/volume was such that the first couple times it ran there was a decent execution plan, but now the execution plan is suboptimal.
Thank you for the response!
I'm not sure but I think it is to do with not having Indexes created, but the only problem is I have a large Database so I would need to find out which tables don't have one, or it might be something else.
September 14, 2010 at 7:33 am
Abhijit More (9/14/2010)
Please provide more information on Job, what does it do?
What the code does is, it looks for any data that has been inserted one day before in one table then it inserts the data into another table then it goes through another condition then updates another table....on and on so there are lots of INSERTS and UPDATES in this particular query. I hope that gives a bit more detail in what the job is trying to achieve.
Thank you
September 14, 2010 at 8:17 am
The execution plan will show you what indexes are in play and which ones are missing. If you are on SSMS 2008/2008R2 it will show you which indexes are missing and what the impact is. If you want a quick way of looking at waiting and blocking while the job is running you can run the following in query window in SSMS:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = 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)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50 -- Ignore system spids.
AND session_Id NOT IN (@@SPID) -- Ignore this current statement.
ORDER BY 1, 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
September 14, 2010 at 8:23 am
tt-615680 (9/14/2010)
Hi All,I set a job to run every morning, for the first few days it worked fine but from yesterday morning, when I run the query that checks if there are any jobs that are blocks, it showed that the job starts running but stops after a second then blocks it's self. The jobs wait type is CXPACKET and also PAGEIOLATCH_SH.
Would someone let me know how I can resolve this please?
Thank you
A job showing as blocking itself is not necessarily hung, but it could be. Are you sure it's not still processing ?
Microsoft explains it here regarding 2000 SP4. Not sure if it applies to 2005.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906344
Here's a good MS paper on blocking in general:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply