Re: Owner Qualified procedures and compiles

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Paul Cahill (xyzpaul.xyzcahill_at_dsl.pipex.com)
Date: 04/30/04 

Date: Fri, 30 Apr 2004 19:19:43 +0100

So it's worth doing but probably only when you are editing a proc for other
changes.
I might ask the VB guys just to put it in their calls if they go though a
common piece of code.

Thanks.

"Trey Walpole" <treyNOpole@SPcomcastAM.net> wrote in message
news:e45xZdtLEHA.620@TK2MSFTNGP10.phx.gbl...
> My understanding for this situation is that in SQL Server you should make
> sure that stored procs and all objects within a stored procedure are
> referenced by owner to reduce not necessarily recompiles but rather
compile
> locks, while it determines if all objects called within the stored proc
have
> the same owner(s) as those in the currently cached plan.
>
> As Quentin mentioned, the impact is usually negligible - although it could
> have an effect in ultra-high usage situations.
>
> for more info on reducing recompiles check out:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243586
>
>
> "Paul Cahill" <XYZpaul.cahillXYZ@blueyonder.co.uk> wrote in message
> news:O9GXovsLEHA.1032@tk2msftngp13.phx.gbl...
> > I read somewhere that to avoid recompiles and compile locks that one
> should
> > create all procedures with a qualified owner name.
> >
> > eg
> > create procedure dbo.myproc
> >
> > Would it also be true that for EXEC's from within a procedure that one
> > should qualify the owner on the exec call.
> >
> > eg
> > create procedure dbo.myproc
> > as
> > exec dbo.MyOtherProc
> > -- rather than
> > exec MyOtherProc
> > return
> > go
> >
> > Should I get our vb guys to prefix their ADO database calls with dbo. as
> > well?
> >
> > Thanks
> > Paul Cahill
> >
> >
>
>

Relevant Pages

  • Re: Tables created by user
    ... owner name, or you can take ownership of the object using ... CREATE PROC dbo.sprTest1 ... EXEC sp_changeobjectowner 'someowner.Test1', 'dbo' ...
    (microsoft.public.sqlserver.security)
  • Re: Owner Qualified procedures and compiles
    ... sure that stored procs and all objects within a stored procedure are ... referenced by owner to reduce not necessarily recompiles but rather compile ... > create all procedures with a qualified owner name. ... > should qualify the owner on the exec call. ...
    (microsoft.public.sqlserver.programming)
  • Re: SqlDataAdapter1.SelectCommand.CommandType= CommandType.StoredProcedure
    ... >> kann man beim EXEC PROC keine Parameter beifügen. ... > da das TDS Protokoll das intern unterscheiden kann. ... > nach der Ausführung ungültig werden. ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)
  • Re: using nexted procs
    ... So I have a cataloged proc 'FJS.PDSE.PROC' that looks like this: ... //IEFRDER DD DUMMY //SYSUOUT DD SYSOUT=* ... //FFND05 EXEC DLIBATCH,DLIPGM=FFND05,DLIPSB=FFUNDGO ... Our goal was that when testing we would execute the production proc and override the DD names to the names of test files. ...
    (bit.listserv.ibm-main)
  • typo error...
    ... First Proc should be p1 and not p3 ... > exec p1 ... >> Over night we take a copy of various live SQL databases onto another SQL ... >> I connect to the live databases using linked servers. ...
    (microsoft.public.sqlserver.programming)