execution plan question

Question

execution plan question

bluepaperbags

SSCommitted

Points: 1738
More actions
April 8, 2008 at 10:06 am

#180900

Do you guys see something wrong with this execution plan. Any input are welcome.

Viewing 7 posts - 1 through 6 (of 6 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

A screen capture by itself is not sufficient to provide feedback:

1) The plan when displayed in QA has context-sensitive windows that pop up over each step - these aren't captured in the screen capture, so we can't see the necessary details.

2) Whether or not a query plan is valid depends on the query. You might look at a step that has "Table Scan" as the access method and think it's a bad thing, but if the query is not selective and requires a large number of rows from that table, then maybe a full table scan is the optimal path.

We'd need to see the SQL of the query, and the output from SET SHOWPLAN

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

PW is right, it is hard to tell without knowing the SQL and the database structure, etc... Over all the plan is not that bad. It is using indexes which is good. Obviously the biggest place to improve performance is to get the Index Scan on Workflow_Tasks to convert to a seek, but you may not be able to do that.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

Do you mean the total cost is 99% when you wrote anything wrong in the execution plan?

bluepaperbags SSCommitted Points: 1738 More actions · Answer 4

I hope this helps.

exec dbo.asp_UPDATE_INSTRUMENT_WORKFLOW 2008, 1, 1

/****** Add modified update instrument workflow stored procedure ******/ create PROCEDURE dbo.asp_UPDATE_INSTRUMENT_WORKFLOW @year INT, @item_id INT, @workflow_id INT AS DECLARE @task_id INT DECLARE @in_setup INT DECLARE @task_status

|--Snapshot Cursor(task_Cursor, Read Only)

|--Population Query(Synch...

|--Compute Scalar(DEFINE:([Expr1016]=If ([WORKFLOW_SETUP].[TASK_ID]=NULL) then 0 else 1, [Expr1017]=If ([IW].[TASK_STATUS]='C') then 'C' else If ([WORKFLOW_SETUP].[TASK_ID]=NULL) then NULL else If (isnull([Expr1003], 0)<=isnull([Expr1011],

|--Parallelism(Gather Streams, ORDER BY:([WT].[TASK_ID] ASC))

|--Merge Join(Left Outer Join, MERGE:([WT].[TASK_ID])=([IW].[TASK_ID]), RESIDUAL:([IW].[TASK_ID]=[WT].[TASK_ID]))

|--Merge Join(Left Outer Join, MERGE:([WT].[TASK_ID])=([WORKFLOW_SETUP].[TASK_ID]), RESIDUAL:([WT].[TASK_ID]=[WORKFLOW_SETUP].[TASK_ID]))

| |--Parallelism(Distribute Streams, PARTITION COLUMNS:([WT].[TASK_ID]))

| | |--Nested Loops(Left Outer Join, WHERE:([WT].[TASK_ID]=[WORKFLOW_SETUP].[TASK_ID]))

| | |--Clustered Index Scan(OBJECT:([GG_ATRECORDING_QAT].[dbo].[WORKFLOW_TASKS].[PK_WORKFLOW_TASKS] AS [WT]), ORDERED FORWARD)

| | |--Compute Scalar(DEFINE:([Expr1003]=Convert([Expr1050])))

| | |--Stream Aggregate(GROUP BY:([WORKFLOW_SETUP].[TASK_ID]) DEFINE:([Expr1050]=Count(*)))

| | |--Index Seek(OBJECT:([GG_ATRECORDING_QAT].[dbo].[WORKFLOW_SETUP].[IDX_WORKFLOW_SETUP2]), SEEK:([WORKFLOW_SETUP].[WORKFLOW_ID]=Convert([@Param1002])) ORDERED FORWARD)

| |--Compute Scalar(DEFINE:([Expr1011]=Convert([globalagg1045])))

| |--Stream Aggregate(GROUP BY:([WORKFLOW_SETUP].[TASK_ID]) DEFINE:([globalagg1045]=SUM([partialagg1044])))

| |--Parallelism(Repartition Streams, PARTITION COLUMNS:([WORKFLOW_SETUP].[TASK_ID]), ORDER BY:([WORKFLOW_SETUP].[TASK_ID] ASC))

| |--Stream Aggregate(GROUP BY:([WORKFLOW_SETUP].[TASK_ID]) DEFINE:([partialagg1044]=Count(*)))

| |--Sort(ORDER BY:([WORKFLOW_SETUP].[TASK_ID] ASC))

| |--Bookmark Lookup(BOOKMARK:([Bmk1004]), OBJECT:([GG_ATRECORDING_QAT].[dbo].[WORKFLOW_SETUP]))

| |--Nested Loops(Inner Join, OUTER REFERENCES:([INSTRUMENT_WORKFLOW].[TASK_ID]))

| |--Filter(WHERE:([INSTRUMENT_WORKFLOW].[TASK_STATUS]='C'))

| | |--Bookmark Lookup(BOOKMARK:([Bmk1007]), OBJECT:([GG_ATRECORDING_QAT].[dbo].[INSTRUMENT_WORKFLOW]))

| | |--Index Scan(OBJECT:([GG_ATRECORDING_QAT].[dbo].[INSTRUMENT_WORKFLOW].[IDX_INSTRUMENT_WORKFLOW4]), WHERE:([INSTRUMENT_WORKFLOW].[YEAR]=Convert([@Param1009]) AND [INSTRUMENT

| |--Index Seek(OBJECT:([GG_ATRECORDING_QAT].[dbo].[WORKFLOW_SETUP].[IDX_WORKFLOW_SETUP3]), SEEK:([WORKFLOW_SETUP].[WORKFLOW_ID]=Convert([@Param1002]) AND [WORKFLOW_SETUP].[PRECEDING_TAS

|--Sort(ORDER BY:([IW].[TASK_ID] ASC))

|--Parallelism(Repartition Streams, PARTITION COLUMNS:([IW].[TASK_ID]))

|--Bookmark Lookup(BOOKMARK:([Bmk1013]), OBJECT:([GG_ATRECORDING_QAT].[dbo].[INSTRUMENT_WORKFLOW] AS [IW]))

|--Index Scan(OBJECT:([GG_ATRECORDING_QAT].[dbo].[INSTRUMENT_WORKFLOW].[IDX_INSTRUMENT_WORKFLOW4] AS [IW]), WHERE:([IW].[YEAR]=Convert([@Param1009]) AND [IW].[ITEM_ID]=Convert([@Param1010])))

-------------------------------------------------------------------------------------

OPEN task_cursor

FETCH NEXT FROM task_Cursor INTO @task_id, @in_setup, @task_status, @task_exists

WHILE @@FETCH_STATUS = 0

BEGIN IF @in_setup = 1

BEGIN IF @task_exists = 1

BEGIN IF @task_status <> 'C'

UPDATE INSTRUMENT_WORKFLOW SET TASK_STATUS = @task_status WHERE YEAR = @year AND ITEM_ID = @item_id AND TASK_ID = @task_id

|--Clustered Index Update(OBJECT:([GG_ATRECORDING_QAT].[dbo].[INSTRUMENT_WORKFLOW].[PK_INSTRUMENT_WORKFLOW]), SET:([INSTRUMENT_WORKFLOW].[TASK_STATUS]=RaiseIfNull([Expr1004])))

|--Compute Scalar(DEFINE:([Expr1004]=Convert([@task_status])))

|--Filter(WHERE:([INSTRUMENT_WORKFLOW].[TASK_ID]=Convert([@task_id])))

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([GG_ATRECORDING_QAT].[dbo].[INSTRUMENT_WORKFLOW]))

|--Index Scan(OBJECT:([GG_ATRECORDING_QAT].[dbo].[INSTRUMENT_WORKFLOW].[IDX_INSTRUMENT_WORKFLOW4]), WHERE:([INSTRUMENT_WORKFLOW].[ITEM_ID]=Convert([@item_id]) AND [INSTRUMENT_WORKFLOW].[YEAR]=Convert([@year])

-------------------------------------------------------------------------------------

END ELSE BEGIN INSERT INTO INSTRUMENT_WORKFLOW (YEAR, ITEM_ID, TASK_ID, TASK_STATUS) VALUES (@year, @item_id, @task_id, @task_status)

|--Assert(WHERE:(If (NOT([Pass1011]) AND ([Expr1010] IS NULL)) then 0 else NULL))

|--Nested Loops(Left Semi Join, WHERE:([Expr1002] IS NULL)OUTER REFERENCES:([Expr1002]), DEFINE:([Expr1010] = [PROBE VALUE]))

|--Clustered Index Insert(OBJECT:([GG_ATRECORDING_QAT].[dbo].[INSTRUMENT_WORKFLOW].[PK_INSTRUMENT_WORKFLOW]), SET:([INSTRUMENT_WORKFLOW].[ON_HOLD]=RaiseIfNull(0), [INSTRUMENT_WORKFLOW].[QUEUE_ID]=NULL, [INSTRUMENT_WORKFLOW].

| |--Compute Scalar(DEFINE:([Expr1000]=Convert([@year]), [Expr1001]=Convert([@item_id]), [Expr1002]=Convert([@task_id]), [Expr1003]=Convert([@task_status]), [Expr1004]=getidentity(2039678314, 47, NULL)))

| |--Constant Scan

|--Clustered Index Seek(OBJECT:([GG_ATRECORDING_QAT].[dbo].[WORKFLOW_TASKS].[PK_WORKFLOW_TASKS]), SEEK:([WORKFLOW_TASKS].[TASK_ID]=[Expr1002]) ORDERED FORWARD)

-------------------------------------------------------------------------------------

END END ELSE BEGIN IF @task_status IS NULL OR @task_status <> 'C'

DELETE FROM INSTRUMENT_WORKFLOW WHERE YEAR = @year AND ITEM_ID = @item_id AND TASK_ID = @task_id

|--Clustered Index Delete(OBJECT:([GG_ATRECORDING_QAT].[dbo].[INSTRUMENT_WORKFLOW].[PK_INSTRUMENT_WORKFLOW]))

|--Filter(WHERE:([INSTRUMENT_WORKFLOW].[TASK_ID]=Convert([@task_id])))

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([GG_ATRECORDING_QAT].[dbo].[INSTRUMENT_WORKFLOW]))

|--Index Scan(OBJECT:([GG_ATRECORDING_QAT].[dbo].[INSTRUMENT_WORKFLOW].[IDX_INSTRUMENT_WORKFLOW4]), WHERE:([INSTRUMENT_WORKFLOW].[ITEM_ID]=Convert([@item_id]) AND [INSTRUMENT_WORKFLOW].[YEAR]=Convert([@year])) ORDERED

-------------------------------------------------------------------------------------

END FETCH NEXT FROM task_Cursor INTO @task_id, @in_setup, @task_status, @task_exists

END CLOSE task_Cursor

DEALLOCATE task_Cursor

GSquared SSC Guru Points: 260824 More actions · Answer 5

What I'm seeing in the execution plan is mainly a cursor that should probably be replaced with an update command. If you're having trouble with the query, most likely it needs to dump the cursor, and the execution plan has very little to do with the problems.

Please post the text of the query, and the definitions of the tables it runs on. Then we can probably help you turn this thing around.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

I'll second what GSquared has said. The cursor is definitely slowing you down. Also the fact that you are doing 3 different actions based on the cursor means that the cached plan may not be the best plan so you may want to do a recompile each time you run the sp.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question