execution plan for stored procedure?

Hi,
I'm brand new to SQL Server and have a question about execution plans
for stored procedures. It takes 10 minutes to process 10,000 rows
(seems excessive!) When I try to get an execution plan for the
procedure, it doesn't give me any information about the queries inside
the package, it just lists as one of the queries "query text: CREATE
PROCEDURE ....." and gives the cost relative to batch as zero.
For example, here's some of the script - I'll get a result for each of
the "set" statements, then the create, then the sets and grant at the
end. I've loaded the actual procedure from the DB and executed that
and then I get the same, except instead of the "create" statement as
one of the queries I get "object: Stored procedure xxx" but still
with zero cost.
Any help would be greatly appreciated! Sorry if my question doesn't
make sense, I hardly know enough to know what/how to ask! We're
taking the outline of our packaged software vendor's stored procedures
to base ours upon.
Thanks,
Pat
==============
USE AAD
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id =
object_id('dbo.usp_item_update_insert'))
BEGIN
PRINT 'Dropping old version of dbo.usp_item_update_insert'
DROP PROCEDURE dbo.usp_item_update_insert
END
GO
CREATE PROCEDURE usp_item_update_insert
@.v_vchMessage VARCHAR(500) OUTPUT
AS
(body of procedure)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS OFF
GO
GRANT EXECUTE ON usp_item_update_insert TO WA_USER, AAD_USER
GO
In Query Analyzer, select the "Query" menu and click "Show Execution Plan".
Then run the stored procedure and you will see the actual execution plan.
"Pat" <miapjp@.chartermi.net> wrote in message
news:f7a99267.0403310721.117e7f1b@.posting.google.c om...
> Hi,
> I'm brand new to SQL Server and have a question about execution plans
> for stored procedures. It takes 10 minutes to process 10,000 rows
> (seems excessive!) When I try to get an execution plan for the
> procedure, it doesn't give me any information about the queries inside
> the package, it just lists as one of the queries "query text: CREATE
> PROCEDURE ....." and gives the cost relative to batch as zero.
> For example, here's some of the script - I'll get a result for each of
> the "set" statements, then the create, then the sets and grant at the
> end. I've loaded the actual procedure from the DB and executed that
> and then I get the same, except instead of the "create" statement as
> one of the queries I get "object: Stored procedure xxx" but still
> with zero cost.
> Any help would be greatly appreciated! Sorry if my question doesn't
> make sense, I hardly know enough to know what/how to ask! We're
> taking the outline of our packaged software vendor's stored procedures
> to base ours upon.
> Thanks,
> Pat
> ==============
> USE AAD
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET ANSI_WARNINGS ON
> GO
> IF EXISTS (SELECT * FROM sysobjects WHERE id =
> object_id('dbo.usp_item_update_insert'))
> BEGIN
> PRINT 'Dropping old version of dbo.usp_item_update_insert'
> DROP PROCEDURE dbo.usp_item_update_insert
> END
> GO
> CREATE PROCEDURE usp_item_update_insert
> @.v_vchMessage VARCHAR(500) OUTPUT
> AS
> (body of procedure)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET ANSI_NULLS ON
> GO
> SET ANSI_WARNINGS OFF
> GO
> GRANT EXECUTE ON usp_item_update_insert TO WA_USER, AAD_USER
> GO
|||Pat,
You need to get the plan when you execute the proc, not when you create it. And that should be no problem. In
Query Analyzer, (Northwind), I have below:
EXEC dbo.CustOrderHist 123
When I press "Display Estimated Execution Plan", I do get a plan. Do you see something else?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Pat" <miapjp@.chartermi.net> wrote in message news:f7a99267.0403310721.117e7f1b@.posting.google.c om...
> Hi,
> I'm brand new to SQL Server and have a question about execution plans
> for stored procedures. It takes 10 minutes to process 10,000 rows
> (seems excessive!) When I try to get an execution plan for the
> procedure, it doesn't give me any information about the queries inside
> the package, it just lists as one of the queries "query text: CREATE
> PROCEDURE ....." and gives the cost relative to batch as zero.
> For example, here's some of the script - I'll get a result for each of
> the "set" statements, then the create, then the sets and grant at the
> end. I've loaded the actual procedure from the DB and executed that
> and then I get the same, except instead of the "create" statement as
> one of the queries I get "object: Stored procedure xxx" but still
> with zero cost.
> Any help would be greatly appreciated! Sorry if my question doesn't
> make sense, I hardly know enough to know what/how to ask! We're
> taking the outline of our packaged software vendor's stored procedures
> to base ours upon.
> Thanks,
> Pat
> ==============
> USE AAD
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET ANSI_WARNINGS ON
> GO
> IF EXISTS (SELECT * FROM sysobjects WHERE id =
> object_id('dbo.usp_item_update_insert'))
> BEGIN
> PRINT 'Dropping old version of dbo.usp_item_update_insert'
> DROP PROCEDURE dbo.usp_item_update_insert
> END
> GO
> CREATE PROCEDURE usp_item_update_insert
> @.v_vchMessage VARCHAR(500) OUTPUT
> AS
> (body of procedure)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET ANSI_NULLS ON
> GO
> SET ANSI_WARNINGS OFF
> GO
> GRANT EXECUTE ON usp_item_update_insert TO WA_USER, AAD_USER
> GO
|||OK, I was not executing it with the exec command, I have been
right-clicking on the sp, selecting open and then execute. Or
right-clicking and selecting edit and then the green execute arrow.
I've got it now.
Told you I was new!
Thanks much!
Pat
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<#H0KcTzFEHA.1128@.TK2MSFTNGP11.phx.gbl>...
> Pat,
> You need to get the plan when you execute the proc, not when you create it. And that should be no problem. In
> Query Analyzer, (Northwind), I have below:
> EXEC dbo.CustOrderHist 123
> When I press "Display Estimated Execution Plan", I do get a plan. Do you see something else?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Pat" <miapjp@.chartermi.net> wrote in message news:f7a99267.0403310721.117e7f1b@.posting.google.c om...