By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,295 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Need help figuring out SHOWPLAN_TEXT help

P: n/a
Here'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.

Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Voss (vo********@hotmail.com) writes:
Here'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.


Could you post the @@version for both SQL Server boxes?

As I understand there are three plans here. One you get in QA for both
procedures, and then you get two different ones from your C# application?
Of these three, which two did you actually post?

What happens if you from QA first run the command SET ARITHABORT OFF
before you run the procedures?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.