MS support confusion... Help!

I am lost... we are having problems with SQL server and the MS tech keeps
telling me to execute this query, but that is all they tell me... that it is
to help tune the database... but then this query never works because the
table is undeclared, and I am not sure how the exactly want it declared or
what to pass into it or what not... and then targetGroupID is undeclared too
and I don't know what to do there.. the support has been very confuseing...
could someone please help me and tell me whats going on?
INSERT INTO @.tbTmpEffectiveDeploymentAction (
UpdateID,
DeploymentID)
SELECT
UpdateID = U.UpdateID,
DeploymentID = dbo.fnGetEffectiveDeployment(AC.DeploymentID,
TG.DeploymentID, AC.ActionID, TG.ActionID)
FROM
dbo.tbUpdate AS U
LEFT OUTER JOIN dbo.tbDeployment AS TG ON TG.RevisionID =
dbo. fn_GetBestRevisionForEvaluateDeployment(
U.LocalUpdateID, targetGroupID)
AND TG.TargetGroupID = @.targetGroupID
LEFT OUTER JOIN dbo.tbDeployment AS AC ON AC.RevisionID =
dbo. fn_GetBestRevisionForEvaluateDeployment(
U.LocalUpdateID,
'A0A08746-4DBE-4a37-9ADF-9E7652C0B421') AND AC.TargetGroupID =
'A0A08746-4DBE-4a37-9ADF-9E7652C0B421'
WHERE dbo.fnGetEffectiveDeployment(AC.DeploymentID, TG.DeploymentID,
AC.ActionID, TG.ActionID) IS NOT NULL
they say it should return something like this, but not sure how to get it to
the point it returns something like this, and every time i ask support how
to, i get the same thing back "execute the insert query" which ive stated
many times now it doesnt work independently of itself with out somehting
declared... could someone please unconfuse me? thanks!
Rows Executes StmtText
StmtId NodeId Parent PhysicalOp LogicalOp
Argument
DefinedValues
EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList
Warnings Type Parallel EstimateExecutions
-- -- --
-- --
-- -- -- --
--
-- -- -- -- --
--
-- -- --
381 1 Table Insert(OBJECT:(@.tbTmpEffectiveDeployment
Action),
SET:(@.tbTmpEffectiveDeploymentAction.[DeploymentID]=[Expr1003],
@.tbTmpEffectiveDeploymentAction.[UpdateID]=[U].[UpdateID]))
0 0 Table Insert Insert
OBJECT:(@.tbTmpEffectiveDeploymentAction)
,
SET:(@.tbTmpEffectiveDeploymentAction.[DeploymentID]=[Expr1003],
@.tbTmpEffectiveDeploymentAction.[UpdateID]=[U].[UpdateID])
38070.5 0.0100355 0.0380705 15 50.3633
PLAN_ROW 0 1
381 1 |--Table Spool
0 1 0 Table Spool Eager Spool
38070.5 0.968432 0.0137056 27 50.3152
[U].[UpdateID], [Expr1003]
PLAN_ROW 0 1
381 1 |--Top(ROWCOUNT est 0)
0 2 1 Top Top
38070.5 0 0.00380705 27 49.3331
[U].[UpdateID], [Expr1003]
PLAN_ROW 0 1
381 1 |--Compute
Scalar(DEFINE:([Expr1003]=[dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
[TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID])))
0 3 2 Compute Scalar Compute Scalar
DEFINE:([Expr1003]=[dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
[TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID]))
[Expr1003]=[dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
[TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID]) 38070.5 0
0.00380705 27 49.3293 [U].[UpdateID], [Expr1003]
PLAN_ROW 0 1
381 1
|--Filter(WHERE:([dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
[TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID])<>NULL))
0 4 3 Filter Filter
WHERE:([dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
[TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID])<>NULL)
38070.5 0 0.0360352 43 49.3255
[U].[UpdateID], [TG].[DeploymentID], [TG].[ActionID], [AC].[DeploymentID],
[AC].[ActionID] PLAN_ROW 0 1
8210 1 |--Hash Match(Right Outer Join,
HASH:([AC].[RevisionID])=([Expr1006]),
RESIDUAL:([AC].[RevisionID]=[Expr1006]))
0 5 4 Hash Match Right Outer Join
HASH:([AC].[RevisionID])=([Expr1006]),
RESIDUAL:([AC].[RevisionID]=[Expr1006])
40949.1 0 0.329403 43 49.2894
[U].[UpdateID], [TG].[DeploymentID], [TG].[ActionID], [AC].[DeploymentID],
[AC].[ActionID] PLAN_ROW 0 1
7557 1 |--Clustered Index
Scan(OBJECT:([SUSDB].[dbo].[tbDeployment].[c0DeploymentRevision] AS [AC]),
WHERE:([AC].[TargetGroupID]=A0A08746-4DBE-4A37-9ADF-9E7652C0B421))
0 6 5 Clustered Index Scan Clustered Index Scan
OBJECT:([SUSDB].[dbo].[tbDeployment].[c0DeploymentRevision] AS [AC]),
WHERE:([AC].[TargetGroupID]=A0A08746-4DBE-4A37-9ADF-9E7652C0B421)
[AC].[TargetGroupID], [AC].[RevisionID], [AC].[DeploymentID],
[AC].[ActionID] 7564.53
0.635086 0.0289242 371 1.32802 [AC].[TargetGroupID],
[AC].[RevisionID], [AC].[DeploymentID], [AC].[ActionID]
PLAN_ROW 0 1
8202 1 |--Compute
Scalar(DEFINE:([Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[LocalUpdateID],
A0A08746-4DBE-4A37-9ADF-9E7652C0B421)))
0 7 5 Compute Scalar Compute Scalar
DEFINE:([Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[LocalUpdateID],
A0A08746-4DBE-4A37-9ADF-9E7652C0B421))
[Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[LocalUpdateID],
A0A08746-4DBE-4A37-9ADF-9E7652C0B421) 17597 0 0.0017597
35 47.6068 [U].[UpdateID], [TG].[DeploymentID],
[TG].[ActionID], [Expr1006] PLAN_ROW
0 1
8202 1 |--Nested Loops(Left
Outer Join, OUTER REFERENCES:([U].[LocalUpdateID]) WITH PREFETCH)
0 8 7 Nested Loops Left Outer Join
OUTER REFERENCES:([U].[LocalUpdateID]) WITH PREFETCH
17597 0 0.0735556 408 47.605
[U].[LocalUpdateID], [U].[UpdateID], [TG].[DeploymentID], [TG].[ActionID]
PLAN_ROW 0 1
8202 1 |--Index
Scan(OBJECT:([SUSDB].[dbo].[tbUpdate].[UQ__tbUpdate__43D61337] AS [U]))
0 10 8 Index Scan Index Scan
OBJECT:([SUSDB].[dbo].[tbUpdate].[UQ__tbUpdate__43D61337] AS [U])
[U].[LocalUpdateID], [U].[UpdateID]
8202 0.0605415 0.0091007 45 0.0696422
[U].[LocalUpdateID], [U].[UpdateID]
PLAN_ROW 0 1
1812 8202
|--Filter(WHERE:([TG].[TargetGroupID]=[@.targetGroupID]))
0 11 8 Filter Filter
WHERE:([TG].[TargetGroupID]=[@.targetGroupID])
2.14546 0 2.45903E-0 371 47.4618
[TG].[DeploymentID], [TG].[ActionID]
PLAN_ROW 0 8202
50347 8202 |--Bookmark
Lookup(BOOKMARK:([Bmk1001]), OBJECT:([SUSDB].[dbo].[tbDeployment] AS [TG]))
0 12 11 Bookmark Lookup Bookmark Lookup
BOOKMARK:([Bmk1001]), OBJECT:([SUSDB].[dbo].[tbDeployment] AS [TG])
[TG].[TargetGroupID], [TG].[DeploymentID], [TG].[ActionID]
5.12297 0.0156249 5.63527E-0 371 47.4417
[TG].[TargetGroupID], [TG].[DeploymentID], [TG].[ActionID]
PLAN_ROW 0 8202
50347 8202
|--Index S(OBJECT:([SUSDB].[dbo].[tbDeployment].[nc1DeploymentRevision]
AS [TG]),
SEEK:([TG].[RevisionID]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[LocalUpdateID],
[@.targetGroupID])) ORDERED FORWARD) 0 13 12 Index
S Index S
OBJECT:([SUSDB].[dbo].[tbDeployment].[nc1DeploymentRevision] AS [TG]),
SEEK:([TG].[RevisionID]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[LocalUpdateID],
[@.targetGroupID])) ORDERED FORWARD [Bmk1001]
5.12297 0.00320348 8.41609E-0 42 17.6293 [Bmk1001]
PLAN_ROW 0 8202Let me guess...
"My name is Ahmed Akbar, but you may call me George"
Right? LOL
"Brian Henry" <brianiupmsdn@.newsgroups.nospam> wrote in message
news:%23a04ebbQFHA.3816@.TK2MSFTNGP10.phx.gbl...
> I am lost... we are having problems with SQL server and the MS tech keeps
> telling me to execute this query, but that is all they tell me... that it
is
> to help tune the database... but then this query never works because the
> table is undeclared, and I am not sure how the exactly want it declared or
> what to pass into it or what not... and then targetGroupID is undeclared
too
> and I don't know what to do there.. the support has been very
confuseing...
> could someone please help me and tell me whats going on?
> INSERT INTO @.tbTmpEffectiveDeploymentAction (
> UpdateID,
> DeploymentID)
> SELECT
> UpdateID = U.UpdateID,
> DeploymentID = dbo.fnGetEffectiveDeployment(AC.DeploymentID,
> TG.DeploymentID, AC.ActionID, TG.ActionID)
> FROM
> dbo.tbUpdate AS U
> LEFT OUTER JOIN dbo.tbDeployment AS TG ON TG.RevisionID =
> dbo. fn_GetBestRevisionForEvaluateDeployment(
U.LocalUpdateID,
targetGroupID)
> AND TG.TargetGroupID = @.targetGroupID
> LEFT OUTER JOIN dbo.tbDeployment AS AC ON AC.RevisionID =
> dbo. fn_GetBestRevisionForEvaluateDeployment(
U.LocalUpdateID,
> 'A0A08746-4DBE-4a37-9ADF-9E7652C0B421') AND AC.TargetGroupID =
> 'A0A08746-4DBE-4a37-9ADF-9E7652C0B421'
> WHERE dbo.fnGetEffectiveDeployment(AC.DeploymentID, TG.DeploymentID,
> AC.ActionID, TG.ActionID) IS NOT NULL
>
> they say it should return something like this, but not sure how to get it
to
> the point it returns something like this, and every time i ask support how
> to, i get the same thing back "execute the insert query" which ive stated
> many times now it doesnt work independently of itself with out somehting
> declared... could someone please unconfuse me? thanks!
>
> Rows Executes StmtText
> StmtId NodeId Parent PhysicalOp LogicalOp
> Argument
> DefinedValues
> EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList
> Warnings Type Parallel EstimateExecutions
> -- -- --
>
> -- --
> -- -- -- --
>
> --
> --
--
> -- -- -- -- --
> --
-
> -- -- --
> 381 1 Table
Insert(OBJECT:(@.tbTmpEffectiveDeployment
Action),
> SET:(@.tbTmpEffectiveDeploymentAction.[DeploymentID]=[Expr1003],
> @.tbTmpEffectiveDeploymentAction.[UpdateID]=[U].[UpdateID]))
> 0 0 Table Insert Insert
> OBJECT:(@.tbTmpEffectiveDeploymentAction)
,
> SET:(@.tbTmpEffectiveDeploymentAction.[DeploymentID]=[Expr1003],
> @.tbTmpEffectiveDeploymentAction.[UpdateID]=[U].[UpdateID])
> 38070.5 0.0100355 0.0380705 15 50.3633
> PLAN_ROW 0 1
> 381 1 |--Table Spool
> 0 1 0 Table Spool Eager Spool
> 38070.5 0.968432 0.0137056 27 50.3152
> [U].[UpdateID], [Expr1003]
> PLAN_ROW 0 1
> 381 1 |--Top(ROWCOUNT est 0)
> 0 2 1 Top Top
> 38070.5 0 0.00380705 27 49.3331
> [U].[UpdateID], [Expr1003]
> PLAN_ROW 0 1
> 381 1 |--Compute
>
Scalar(DEFINE:([Expr1003]=[dbo].[fnGetEffectiveDeployment]([AC].[DeploymentI
D],
> [TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID])))
> 0 3 2 Compute Scalar Compute Scalar
> DEFINE:([Expr1003]=[dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
> [TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID]))
> [Expr1003]=[dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
> [TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID]) 38070.5 0
> 0.00380705 27 49.3293 [U].[UpdateID], [Expr1003]
> PLAN_ROW 0 1
> 381 1
> |--Filter(WHERE:([dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
> [TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID])<>NULL))
> 0 4 3 Filter Filter
> WHERE:([dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
> [TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID])<>NULL)
> 38070.5 0 0.0360352 43 49.3255
> [U].[UpdateID], [TG].[DeploymentID], [TG].[ActionID], [AC].[DeploymentID],
> [AC].[ActionID] PLAN_ROW 0 1
> 8210 1 |--Hash Match(Right Outer
Join,
> HASH:([AC].[RevisionID])=([Expr1006]),
> RESIDUAL:([AC].[RevisionID]=[Expr1006]))
> 0 5 4 Hash Match Right Outer Join
> HASH:([AC].[RevisionID])=([Expr1006]),
> RESIDUAL:([AC].[RevisionID]=[Expr1006])
> 40949.1 0 0.329403 43 49.2894
> [U].[UpdateID], [TG].[DeploymentID], [TG].[ActionID], [AC].[DeploymentID],
> [AC].[ActionID] PLAN_ROW 0 1
> 7557 1 |--Clustered Index
> Scan(OBJECT:([SUSDB].[dbo].[tbDeployment].[c0DeploymentRevision] AS [AC]),
> WHERE:([AC].[TargetGroupID]=A0A08746-4DBE-4A37-9ADF-9E7652C0B421))
> 0 6 5 Clustered Index Scan Clustered Index Scan
> OBJECT:([SUSDB].[dbo].[tbDeployment].[c0DeploymentRevision] AS [AC]),
> WHERE:([AC].[TargetGroupID]=A0A08746-4DBE-4A37-9ADF-9E7652C0B421)
> [AC].[TargetGroupID], [AC].[RevisionID], [AC].[DeploymentID],
> [AC].[ActionID] 7564.53
> 0.635086 0.0289242 371 1.32802 [AC].[TargetGroupID],
> [AC].[RevisionID], [AC].[DeploymentID], [AC].[ActionID]
> PLAN_ROW 0 1
> 8202 1 |--Compute
>
Scalar(DEFINE:([Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U
].[LocalUpdateID],
> A0A08746-4DBE-4A37-9ADF-9E7652C0B421)))
> 0 7 5 Compute Scalar Compute Scalar
>
DEFINE:([Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[Loca
lUpdateID],
> A0A08746-4DBE-4A37-9ADF-9E7652C0B421))
>
[Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[LocalUpdateI
D],
> A0A08746-4DBE-4A37-9ADF-9E7652C0B421) 17597 0 0.0017597
> 35 47.6068 [U].[UpdateID], [TG].[DeploymentID],
> [TG].[ActionID], [Expr1006] PLAN_ROW
> 0 1
> 8202 1 |--Nested Loops(Left
> Outer Join, OUTER REFERENCES:([U].[LocalUpdateID]) WITH PREFETCH)
> 0 8 7 Nested Loops Left Outer Join
> OUTER REFERENCES:([U].[LocalUpdateID]) WITH PREFETCH
> 17597 0 0.0735556 408 47.605
> [U].[LocalUpdateID], [U].[UpdateID], [TG].[DeploymentID], [TG].[ActionID]
> PLAN_ROW 0 1
> 8202 1 |--Index
> Scan(OBJECT:([SUSDB].[dbo].[tbUpdate].[UQ__tbUpdate__43D61337] AS [U]))
> 0 10 8 Index Scan Index Scan
> OBJECT:([SUSDB].[dbo].[tbUpdate].[UQ__tbUpdate__43D61337] AS [U])
> [U].[LocalUpdateID], [U].[UpdateID]
> 8202 0.0605415 0.0091007 45 0.0696422
> [U].[LocalUpdateID], [U].[UpdateID]
> PLAN_ROW 0 1
> 1812 8202
> |--Filter(WHERE:([TG].[TargetGroupID]=[@.targetGroupID]))
> 0 11 8 Filter Filter
> WHERE:([TG].[TargetGroupID]=[@.targetGroupID])
> 2.14546 0 2.45903E-0 371 47.4618
> [TG].[DeploymentID], [TG].[ActionID]
> PLAN_ROW 0 8202
> 50347 8202
|--Bookmark
> Lookup(BOOKMARK:([Bmk1001]), OBJECT:([SUSDB].[dbo].[tbDeployment] AS
[TG]))
> 0 12 11 Bookmark Lookup Bookmark Lookup
> BOOKMARK:([Bmk1001]), OBJECT:([SUSDB].[dbo].[tbDeployment] AS [TG])
> [TG].[TargetGroupID], [TG].[DeploymentID], [TG].[ActionID]
> 5.12297 0.0156249 5.63527E-0 371 47.4417
> [TG].[TargetGroupID], [TG].[DeploymentID], [TG].[ActionID]
> PLAN_ROW 0 8202
> 50347 8202
> |--Index S(OBJECT:([SUSDB].[dbo].[tbDeployment].[nc1DeploymentRevision]
> AS [TG]),
>
SEEK:([TG].[RevisionID]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].
[LocalUpdateID],
> [@.targetGroupID])) ORDERED FORWARD) 0 13 12 Index
> S Index S
> OBJECT:([SUSDB].[dbo].[tbDeployment].[nc1DeploymentRevision] AS [TG]),
>
SEEK:([TG].[RevisionID]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].
[LocalUpdateID],
> [@.targetGroupID])) ORDERED FORWARD [Bmk1001]
> 5.12297 0.00320348 8.41609E-0 42 17.6293 [Bmk1001]
> PLAN_ROW 0 8202
>|||something like that...
"Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
news:%237PCTjbQFHA.580@.TK2MSFTNGP15.phx.gbl...
> Let me guess...
> "My name is Ahmed Akbar, but you may call me George"
> Right? LOL
> "Brian Henry" <brianiupmsdn@.newsgroups.nospam> wrote in message
> news:%23a04ebbQFHA.3816@.TK2MSFTNGP10.phx.gbl...
> is
> too
> confuseing...
> targetGroupID)
> to
> --
> -
> Insert(OBJECT:(@.tbTmpEffectiveDeployment
Action),
> Scalar(DEFINE:([Expr1003]=[dbo].[fnGetEffectiveDeployment]([AC].[DeploymentI
> D],
> Join,
> Scalar(DEFINE:([Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U
> ].[LocalUpdateID],
> DEFINE:([Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[Loca
> lUpdateID],
> [Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[LocalUpdateI
> D],
> |--Bookmark
> [TG]))
> SEEK:([TG].[RevisionID]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].
> [LocalUpdateID],
> SEEK:([TG].[RevisionID]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].
> [LocalUpdateID],
>