September 10, 2010 at 3:41 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. Would someone let me know how I can resolve this please?
Thank you
September 14, 2010 at 8:30 am
I'm not really clear what you're looking for in the job? Perhaps you could explain it better and include some code that might help?
September 14, 2010 at 8:49 am
Steve Jones - Editor (9/14/2010)
I'm not really clear what you're looking for in the job? Perhaps you could explain it better and include some code that might help?
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:51 am
It would be extremely useful to post the tsql for this job. An execution plan of the tsql would also be highly useful.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 15, 2010 at 8:33 am
CirquedeSQLeil (9/14/2010)
It would be extremely useful to post the tsql for this job. An execution plan of the tsql would also be highly useful.
ALTER PROC [dbo].[storedprocedurename]
AS
DECLARE @paramnameAS DATETIME
SELECT
@paramname= DATEADD(day, -1, ISNULL(MAX(datetimecol), '1930-01-01'))
FROM
tablename
INSERT INTO
tablename
(
column1,
column2,
column3,
column4,
column5,
column6
)
SELECT column1,column2,column3,column4
FROM table1 LEFT JOIN table2 ON table1.columnname = table2.columnname
LEFT JOIN table3 ON table2.columnname = table3.columnname
LEFT JOIN table3 ON table3.columnname = table4.columnname
AND columnname BETWEEN columnname(datecolumn)
AND ISNULL(columnname(datecolumn), '2050-01-01')
LEFT JOIN table5 ON columnname = columnname
WHERE
(columnname(datecolumn), >= @paramname
AND
columnname IS NULL
-------------------------------------------------------------------------------------------------
UPDATE tablename
SET (columnname(datecolumn), = (columnname(datecolumn),
FROM tablename1 JOIN tablename2 ON tablename1.columnname = tablename2.columnname
JOIN tablename3 ON tablename2.columnname = tablename3.columnname
WHERE columnname = 'test'
AND (columnname(datecolumn), >= @paramname
there are 11 updates all together with similar conditions as the previous query.
I hope this makes it a bit clear of what the job does, please let me know if you need me to explain more.
Thank you.
September 15, 2010 at 10:34 am
I don't see any transaction blocks. Do you have begin trans and commit trans in the original code?
With eleven update statements, it could be useful to isolate each in a transaction and commit that before rolling to the next update.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2010 at 4:23 am
CirquedeSQLeil (9/15/2010)
I don't see any transaction blocks. Do you have begin trans and commit trans in the original code?With eleven update statements, it could be useful to isolate each in a transaction and commit that before rolling to the next update.
I have changed the script to have transaction and commit and I used the gatedate command in each of the transactions therefore I would be able to get the times that each transaction took to run but it doesn't look like if the job is running any faster. Is there a particular Isolation level that I should be using?
September 17, 2010 at 8:30 am
It wasn't to make it run faster, but potentially lower the chance of contention.
How many records in the affected tables? Table ddl? Index structures? Can you provide the actual execution plan?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply