Need Help Figuring Out SHOWPLAN_TEXT Help
Jul 23, 2005Here's what's going on. I have a 2 computers (x & y) running SQL2000. I
backed up a copy a DB from x and restored it on y. I have a Stored proc
that runs in under 2 seconds on both x & y when running it through
Analyzer, but when I call this stored proc running it throuhg my C#
winforms app (running on computer z) it takes over 3 minutes on
computer x and under 10 seconds on y.
This stored proc does have a select clause as part of the where clause,
but again it works fine on y.
I've check the indexes and that looks good and I just did a restore of
the database so they should be identical. And I don't think it's a
performance issue because the rest of the app runs actaully a bit
faster on x.
The plans do have differences. Specifically with a mention of
Parallelism in the fast one.
Here are the plans:
X (slow):
|--Sort(DISTINCT ORDER BY:([r].[GuestId] ASC, [g].[GuestNote] ASC,
[Expr1005] ASC, [g].[Email] ASC, [g].[Phone1] ASC))
|--Compute Scalar(DEFINE:([Expr1005]=[g].[LastName]+',
'+[g].[FirstName]))
|--Filter(WHERE:(If ([Expr1003] IS NULL) then 0 else
[Expr1003]>=2))
|--Nested Loops(Left Outer Join, OUTER
REFERENCES:([r].[GuestId]))
|--Hash Match(Inner Join,
HASH:([g].[GuestId])=([r].[GuestId]),
RESIDUAL:([r].[GuestId]=[g].[GuestId]))
| |--Clustered Index
Scan(OBJECT:([Restaurant].[dbo].[Guest].[PK_Guest] AS [g]),
WHERE:(len(isnull([g].[Email], ''))>6 AND charindex('@',
isnull([g].[Email], ''), NULL)>1))
| |--Clustered Index
Seek(OBJECT:([Restaurant].[dbo].[Reservations].[PK_Reservations] AS
[r]), SEEK:([r].[RestId]=1), WHERE:([r].[Date]<='Jan 1 2005 12:00AM'
AND [r].[Date]>='Jan 1 2003 12:00AM') ORDERED FORWARD)
|--Hash Match(Cache, HASH:([r].[GuestId]),
RESIDUAL:([r].[GuestId]=[r].[GuestId]))
|--Compute
Scalar(DEFINE:([Expr1003]=Convert([Expr1011])))
|--Stream
Aggregate(DEFINE:([Expr1011]=Count(*)))
|--Index
Spool(SEEK:([r2].[GuestId]=[r].[GuestId]))
|--Clustered Index
Scan(OBJECT:([Restaurant].[dbo].[Reservations].[PK_Reservations] AS
[r2]))
Y (Fast):
|--Parallelism(Gather Streams)
|--Sort(DISTINCT ORDER BY:([r].[GuestId] ASC, [g].[GuestNote]
ASC, [Expr1005] ASC, [g].[Email] ASC, [g].[Phone1] ASC))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([r].[GuestId], [g].[GuestNote], [Expr1005], [g].[Email],
[g].[Phone1]))
|--Compute Scalar(DEFINE:([Expr1005]=[g].[LastName]+',
'+[g].[FirstName]))
|--Filter(WHERE:(If ([Expr1003] IS NULL) then 0
else [Expr1003]>=2))
|--Compute
Scalar(DEFINE:([Expr1003]=Convert([Expr1013])))
|--Hash Match Root(Right Outer Join,
HASH:([r2].[GuestId])=([r].[GuestId]),
RESIDUAL:([r2].[GuestId]=[r2].[GuestId]) AND
([r2].[GuestId]=[r].[GuestId]) DEFINE:([Expr1013]=COUNT(*)))
|--Parallelism(Repartition
Streams, PARTITION COLUMNS:([r2].[GuestId]))
| |--Clustered Index
Scan(OBJECT:([Restaurant].[dbo].[Reservations].[PK_Reservations] AS
[r2]))
|--Hash Match Team(Inner Join,
HASH:([g].[GuestId])=([r].[GuestId]),
RESIDUAL:([r].[GuestId]=[g].[GuestId]))
|--Bitmap(HASH:([g].[GuestId]), DEFINE:([Bitmap1014]))
|
|--Parallelism(Repartition Streams, PARTITION COLUMNS:([g].[GuestId]))
| |--Clustered Index
Scan(OBJECT:([Restaurant].[dbo].[Guest].[PK_Guest] AS [g]),
WHERE:(len(isnull([g].[Email], ''))>6 AND charindex('@',
isnull([g].[Email], ''), NULL)>1))
|--Parallelism(Repartition
Streams, PARTITION COLUMNS:([r].[GuestId]),
WHERE:(PROBE([Bitmap1014])=TRUE))
|--Clustered Index
Seek(OBJECT:([Restaurant].[dbo].[Reservations].[PK_Reservations] AS
[r]), SEEK:([r].[RestId]=1), WHERE:([r].[Date]<='Jan 1 2005 12:00AM'
AND [r].[Date]>='Jan 1 2003 12:00AM') ORDERED FORW
Any ideas of what I can check for?
Thanks for any help.